Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9124 > unrolled thread

Managing changes to combo box contents when values are contained in a table

Back to article view | Back to comp.databases.ms-access

Contents

  Managing changes to combo box contents when values are contained in a table BobAlston <bobalston9@yahoo.com> - 2014-03-12 11:39 -0500
    Re: Managing changes to combo box contents when values are contained in a table "Ron Paii" <None@none.com> - 2014-03-12 12:44 -0500
      Re: Managing changes to combo box contents when values are contained in a table BobAlston <bobalston9@yahoo.com> - 2014-03-12 16:58 -0500
        Re: Managing changes to combo box contents when values are contained in a table Bob Quintal <rquintal@sPAmpatico.ca> - 2014-03-13 10:24 +0000
    Re: Managing changes to combo box contents when values are contained in a table Neil <xxx@gmail.com> - 2014-03-12 16:16 -0500
    Re: Managing changes to combo box contents when values are contained in a table Hagen Weidlich <durragon@web.de> - 2014-03-14 22:14 +0100
      Re: Managing changes to combo box contents when values are contained in a table BobAlston <bobalston9@yahoo.com> - 2014-03-14 21:05 -0500
        Re: Managing changes to combo box contents when values are contained in a table "Phil" <phil@stantonfamily.co.uk> - 2014-03-15 09:55 +0000
        Re: Managing changes to combo box contents when values are contained in a table Hagen Weidlich <durragon@web.de> - 2014-03-15 17:14 +0100

#9124 — Managing changes to combo box contents when values are contained in a table

I am interested in hearing from some of you experienced developers on 
how you design your table driven combo boxes to accommodate user changes 
to the table over time.  Access defaults to storing the key but 
displaying the text value.  That is cool.

If a user subsequently decides to change the combo box list contents, 
they could delete records (in the table feeding the combo box) that they 
no longer need.  Of course this would affect the historical data using 
that combo box selection.  The "proper" way around this is to set the 
relation so that the entry could not be deleted if there are related 
records with its key.  Relational theory!!

You can handle this by setting an "unused" flag in the table, to allow 
the old, no longer needed value, to remain, but not show up in the combo 
box.  Unfortunately, this would prevent the decode action from 
displaying the value.  How do you handle that?

One way of handling that I have used is to have the unused indicator and 
sort the combo records so the unused are at the bottom of the list with 
"NOT CURRENTLY USED" in the display as column 2.  That makes the decode 
work.  And I add logic to produce an error if a user tries to select a 
Not Currently used value for an empty combo box field.

Of course, smart users can get around all this by simply typing the new 
10 values of the combo box, over the existing 10 values, thereby 
destroying the historical data.

I am interested in hearing from experienced developers about how they 
handle this?

bob

[toc] | [next] | [standalone]

#9127

Use a query instead of a table as the combo box's source.

"BobAlston" <bobalston9@yahoo.com> wrote in message 
news:lfq2ka$mjk$1@dont-email.me...
> I am interested in hearing from some of you experienced developers on how 
> you design your table driven combo boxes to accommodate user changes to 
> the table over time.  Access defaults to storing the key but displaying 
> the text value.  That is cool.
>
> If a user subsequently decides to change the combo box list contents, they 
> could delete records (in the table feeding the combo box) that they no 
> longer need.  Of course this would affect the historical data using that 
> combo box selection.  The "proper" way around this is to set the relation 
> so that the entry could not be deleted if there are related records with 
> its key.  Relational theory!!
>
> You can handle this by setting an "unused" flag in the table, to allow the 
> old, no longer needed value, to remain, but not show up in the combo box. 
> Unfortunately, this would prevent the decode action from displaying the 
> value.  How do you handle that?
>
> One way of handling that I have used is to have the unused indicator and 
> sort the combo records so the unused are at the bottom of the list with 
> "NOT CURRENTLY USED" in the display as column 2.  That makes the decode 
> work.  And I add logic to produce an error if a user tries to select a Not 
> Currently used value for an empty combo box field.
>
> Of course, smart users can get around all this by simply typing the new 10 
> values of the combo box, over the existing 10 values, thereby destroying 
> the historical data.
>
> I am interested in hearing from experienced developers about how they 
> handle this?
>
> bob 

[toc] | [prev] | [next] | [standalone]

#9138

On 3/12/2014 12:44 PM, Ron Paii wrote:
> Use a query instead of a table as the combo box's source.
>
> "BobAlston" <bobalston9@yahoo.com> wrote in message
> news:lfq2ka$mjk$1@dont-email.me...
>> I am interested in hearing from some of you experienced developers on
>> how you design your table driven combo boxes to accommodate user
>> changes to the table over time.  Access defaults to storing the key
>> but displaying the text value.  That is cool.
>>
>> If a user subsequently decides to change the combo box list contents,
>> they could delete records (in the table feeding the combo box) that
>> they no longer need.  Of course this would affect the historical data
>> using that combo box selection.  The "proper" way around this is to
>> set the relation so that the entry could not be deleted if there are
>> related records with its key.  Relational theory!!
>>
>> You can handle this by setting an "unused" flag in the table, to allow
>> the old, no longer needed value, to remain, but not show up in the
>> combo box. Unfortunately, this would prevent the decode action from
>> displaying the value.  How do you handle that?
>>
>> One way of handling that I have used is to have the unused indicator
>> and sort the combo records so the unused are at the bottom of the list
>> with "NOT CURRENTLY USED" in the display as column 2.  That makes the
>> decode work.  And I add logic to produce an error if a user tries to
>> select a Not Currently used value for an empty combo box field.
>>
>> Of course, smart users can get around all this by simply typing the
>> new 10 values of the combo box, over the existing 10 values, thereby
>> destroying the historical data.
>>
>> I am interested in hearing from experienced developers about how they
>> handle this?
>>
>> bob
>
If the query excludes records that are no longer used, then the text 
will no longer be displayed for old records having the no longer used 
value from that combo box.

Bob

[toc] | [prev] | [next] | [standalone]

#9143

BobAlston <bobalston9@yahoo.com> wrote in
news:5320D8A3.90903@yahoo.com: 

> On 3/12/2014 12:44 PM, Ron Paii wrote:
>> Use a query instead of a table as the combo box's source.
>>
>> "BobAlston" <bobalston9@yahoo.com> wrote in message
>> news:lfq2ka$mjk$1@dont-email.me...
>>> I am interested in hearing from some of you experienced
>>> developers on how you design your table driven combo boxes to
>>> accommodate user changes to the table over time.  Access
>>> defaults to storing the key but displaying the text value.  That
>>> is cool. 
>>>
>>> If a user subsequently decides to change the combo box list
>>> contents, they could delete records (in the table feeding the
>>> combo box) that they no longer need.  Of course this would
>>> affect the historical data using that combo box selection.  The
>>> "proper" way around this is to set the relation so that the
>>> entry could not be deleted if there are related records with its
>>> key.  Relational theory!! 
>>>
>>> You can handle this by setting an "unused" flag in the table, to
>>> allow the old, no longer needed value, to remain, but not show
>>> up in the combo box. Unfortunately, this would prevent the
>>> decode action from displaying the value.  How do you handle
>>> that? 
>>>
>>> One way of handling that I have used is to have the unused
>>> indicator and sort the combo records so the unused are at the
>>> bottom of the list with "NOT CURRENTLY USED" in the display as
>>> column 2.  That makes the decode work.  And I add logic to
>>> produce an error if a user tries to select a Not Currently used
>>> value for an empty combo box field. 
>>>
>>> Of course, smart users can get around all this by simply typing
>>> the new 10 values of the combo box, over the existing 10 values,
>>> thereby destroying the historical data.
>>>
>>> I am interested in hearing from experienced developers about how
>>> they handle this?
>>>
>>> bob
>>
> If the query excludes records that are no longer used, then the
> text will no longer be displayed for old records having the no
> longer used value from that combo box.
> 
> Bob
> 
The query needs a Where Chause.If you are adding a new record, then 
filter only hte current values. If displaying existing records use 
the full list.

-- 
Bob Q.
PA is y I've altered my address.

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#9135

On 3/12/2014 11:39 AM, BobAlston wrote:
> The "proper" way around this is to set the
> relation so that the entry could not be deleted if there are related
> records with its key.  Relational theory!!
>

Yes, that is the proper way, and that's what should be done! Users 
shouldn't be deleting the table and repopulating it!

If, for some reason, there is a need to do that, the system admin can do 
it, and restore the old autonumber values (writing to an autonumber 
field using an append query will force the autonumber field to have the 
value from the query). Thus, you can retain the old values.

If, for some reason, this is a very fluid table that needs to be deleted 
and repopulated often, then use a user-defined primary key instead of an 
autonumber one. The user-defined key can be a number, or it can be a 
code, such as a three-character sequence. That way, the values can be 
retained when repopulated.

But, really, I don't see why users would need to do that. Always enforce 
referential integrity and don't let the users delete the records! If the 
table needs to be repopulated, then let the sys admin do it.

But, either way, using a hidden key is the right way to go. That way the 
users can change the value in the list without changing the actual 
stored data.

[toc] | [prev] | [next] | [standalone]

#9154

Am 12.03.2014 17:39, schrieb BobAlston:> ...
> If a user subsequently decides to change the combo box list contents,
> they could delete records (in the table feeding the combo box) that they
> no longer need.  Of course this would affect the historical data using
> that combo box selection.  The "proper" way around this is to set the
> relation so that the entry could not be deleted if there are related
> records with its key.  Relational theory!!
>
> You can handle this by setting an "unused" flag in the table, to allow
> the old, no longer needed value, to remain, but not show up in the combo
> box.  Unfortunately, this would prevent the decode action from
> displaying the value.  How do you handle that?
Those two consecutive paragraphs do puzzle me. Obviously you give the
solution in the first paragraph - access can enforce referential
integrity. So why would you need hidden flags or mask unused values?
>
> One way of handling that I have used is to have the unused indicator and
> sort the combo records so the unused are at the bottom of the list with
> "NOT CURRENTLY USED" in the display as column 2.  That makes the decode
> work.  And I add logic to produce an error if a user tries to select a
> Not Currently used value for an empty combo box field.
Why is the entry in the table in the first place? If every attempt to
use a previously unused value raises an error that value is at best
superfluous. And the description of "not _CURRENTLY_ used" is misleading
since there seems no way to ever use it.

>
> Of course, smart users can get around all this by simply typing the new
> 10 values of the combo box, over the existing 10 values, thereby
> destroying the historical data.
That doesn't make sense if your data model is correct. Your combo box
shows just the description but is bound to the foreign key. Or at least
it should. Then every time somebody wants to change an existing entry
access should fire a "not_in_list" event. You can't just replace the
description through a simple combo box.

To make the design a bit more clear just consider the following example.
Please forgive me for that silly example. It's just to show my point.

We have two tables. Table 1 holds information for persons. Table 2 just
lists the titles. Table 1 has the following fields:
ID - autonumber, Primary Key
txtName - text
fktitle - long integer

table 2 just holds
ID - autonumber, Primary Key
txtTitle - text

Between table 1:fkTitle and table 2:ID is a 1:n relation and access is
told to enforce referential integrity. Cascade Delete must not be checked!

Table 2 holds these entries:
1, Ms
2, Mrs
3, Miss
4, Master
5, Madam
6, M'lady

The combo box on the persons form is based on table 2 and bound to
fktitle. Since Access is enforcing referential integrity a user can not
enter another title(he can however leave it blank). When he tries to
change one of the existing titles access treats it as a new one and
fires the NotInList event. There you can change the existing one or add
another title to table 2.

However key to that functionality is that access has an established 1:n
relation and referential integrity checked. With that in place no user
can delete a used entry in table 2 as long as there are corresponding
entries in table 1. Access prevents it and there is no way around! If it
is unused the deletion will of course succeed.

So it seems you are missing the part where you establish relations and
set their properties.

Hagen

[toc] | [prev] | [next] | [standalone]

#9156

On 3/14/2014 4:14 PM, Hagen Weidlich wrote:
> Am 12.03.2014 17:39, schrieb BobAlston:> ...
>> If a user subsequently decides to change the combo box list contents,
>> they could delete records (in the table feeding the combo box) that they
>> no longer need.  Of course this would affect the historical data using
>> that combo box selection.  The "proper" way around this is to set the
>> relation so that the entry could not be deleted if there are related
>> records with its key.  Relational theory!!
>>
>> You can handle this by setting an "unused" flag in the table, to allow
>> the old, no longer needed value, to remain, but not show up in the combo
>> box.  Unfortunately, this would prevent the decode action from
>> displaying the value.  How do you handle that?
> Those two consecutive paragraphs do puzzle me. Obviously you give the
> solution in the first paragraph - access can enforce referential
> integrity. So why would you need hidden flags or mask unused values?
>>
>> One way of handling that I have used is to have the unused indicator and
>> sort the combo records so the unused are at the bottom of the list with
>> "NOT CURRENTLY USED" in the display as column 2.  That makes the decode
>> work.  And I add logic to produce an error if a user tries to select a
>> Not Currently used value for an empty combo box field.
> Why is the entry in the table in the first place? If every attempt to
> use a previously unused value raises an error that value is at best
> superfluous. And the description of "not _CURRENTLY_ used" is misleading
> since there seems no way to ever use it.
>
>>
>> Of course, smart users can get around all this by simply typing the new
>> 10 values of the combo box, over the existing 10 values, thereby
>> destroying the historical data.
> That doesn't make sense if your data model is correct. Your combo box
> shows just the description but is bound to the foreign key. Or at least
> it should. Then every time somebody wants to change an existing entry
> access should fire a "not_in_list" event. You can't just replace the
> description through a simple combo box.
>
> To make the design a bit more clear just consider the following example.
> Please forgive me for that silly example. It's just to show my point.
>
> We have two tables. Table 1 holds information for persons. Table 2 just
> lists the titles. Table 1 has the following fields:
> ID - autonumber, Primary Key
> txtName - text
> fktitle - long integer
>
> table 2 just holds
> ID - autonumber, Primary Key
> txtTitle - text
>
> Between table 1:fkTitle and table 2:ID is a 1:n relation and access is
> told to enforce referential integrity. Cascade Delete must not be checked!
>
> Table 2 holds these entries:
> 1, Ms
> 2, Mrs
> 3, Miss
> 4, Master
> 5, Madam
> 6, M'lady
>
> The combo box on the persons form is based on table 2 and bound to
> fktitle. Since Access is enforcing referential integrity a user can not
> enter another title(he can however leave it blank). When he tries to
> change one of the existing titles access treats it as a new one and
> fires the NotInList event. There you can change the existing one or add
> another title to table 2.
>
> However key to that functionality is that access has an established 1:n
> relation and referential integrity checked. With that in place no user
> can delete a used entry in table 2 as long as there are corresponding
> entries in table 1. Access prevents it and there is no way around! If it
> is unused the deletion will of course succeed.
>
> So it seems you are missing the part where you establish relations and
> set their properties.
>
> Hagen
>


Using your example, after using the table 2 entries for some time, a 
user cannot delete entry 1 "Ms" but they can make it something else 
completely, such as "Missy".  No if you go to a form using a combo box 
for the field, where previously a user selected entry 1 "Ms", they will 
now see "Missy".  There will be no entries for "Ms" in the database.

Bob

[toc] | [prev] | [next] | [standalone]

#9158

On 15/03/2014 02:05:42, BobAlston wrote:
> On 3/14/2014 4:14 PM, Hagen Weidlich wrote:
>> Am 12.03.2014 17:39, schrieb BobAlston:> ...
>>> If a user subsequently decides to change the combo box list contents,
>>> they could delete records (in the table feeding the combo box) that they
>>> no longer need.  Of course this would affect the historical data using
>>> that combo box selection.  The "proper" way around this is to set the
>>> relation so that the entry could not be deleted if there are related
>>> records with its key.  Relational theory!!
>>>
>>> You can handle this by setting an "unused" flag in the table, to allow
>>> the old, no longer needed value, to remain, but not show up in the combo
>>> box.  Unfortunately, this would prevent the decode action from
>>> displaying the value.  How do you handle that?
>> Those two consecutive paragraphs do puzzle me. Obviously you give the
>> solution in the first paragraph - access can enforce referential
>> integrity. So why would you need hidden flags or mask unused values?
>>>
>>> One way of handling that I have used is to have the unused indicator and
>>> sort the combo records so the unused are at the bottom of the list with
>>> "NOT CURRENTLY USED" in the display as column 2.  That makes the decode
>>> work.  And I add logic to produce an error if a user tries to select a
>>> Not Currently used value for an empty combo box field.
>> Why is the entry in the table in the first place? If every attempt to
>> use a previously unused value raises an error that value is at best
>> superfluous. And the description of "not _CURRENTLY_ used" is misleading
>> since there seems no way to ever use it.
>>
>>>
>>> Of course, smart users can get around all this by simply typing the new
>>> 10 values of the combo box, over the existing 10 values, thereby
>>> destroying the historical data.
>> That doesn't make sense if your data model is correct. Your combo box
>> shows just the description but is bound to the foreign key. Or at least
>> it should. Then every time somebody wants to change an existing entry
>> access should fire a "not_in_list" event. You can't just replace the
>> description through a simple combo box.
>>
>> To make the design a bit more clear just consider the following example.
>> Please forgive me for that silly example. It's just to show my point.
>>
>> We have two tables. Table 1 holds information for persons. Table 2 just
>> lists the titles. Table 1 has the following fields:
>> ID - autonumber, Primary Key
>> txtName - text
>> fktitle - long integer
>>
>> table 2 just holds
>> ID - autonumber, Primary Key
>> txtTitle - text
>>
>> Between table 1:fkTitle and table 2:ID is a 1:n relation and access is
>> told to enforce referential integrity. Cascade Delete must not be checked!
>>
>> Table 2 holds these entries:
>> 1, Ms
>> 2, Mrs
>> 3, Miss
>> 4, Master
>> 5, Madam
>> 6, M'lady
>>
>> The combo box on the persons form is based on table 2 and bound to
>> fktitle. Since Access is enforcing referential integrity a user can not
>> enter another title(he can however leave it blank). When he tries to
>> change one of the existing titles access treats it as a new one and
>> fires the NotInList event. There you can change the existing one or add
>> another title to table 2.
>>
>> However key to that functionality is that access has an established 1:n
>> relation and referential integrity checked. With that in place no user
>> can delete a used entry in table 2 as long as there are corresponding
>> entries in table 1. Access prevents it and there is no way around! If it
>> is unused the deletion will of course succeed.
>>
>> So it seems you are missing the part where you establish relations and
>> set their properties.
>>
>> Hagen
>>
> 
> 
> Using your example, after using the table 2 entries for some time, a
> user cannot delete entry 1 "Ms" but they can make it something else
> completely, such as "Missy".  No if you go to a form using a combo box
> for the field, where previously a user selected entry 1 "Ms", they will
> now see "Missy".  There will be no entries for "Ms" in the database.
> 
> Bob
> 
I never allow edits nor use Limit to list as yes. Often a Combo box shows
only a subsection of a table, so continuing with the above example, in Table
2 above there may be a third column (sex) and forth column (NotCurrentlyUsed)
that needs setting up. So on nearly all my Combo Boxes, I double click to
open a form which is based on Table2 so that it can be set up correctly with
all the necessary checks.

Phil
 

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[toc] | [prev] | [next] | [standalone]

#9160

Am 15.03.2014 03:05, schrieb BobAlston:> On 3/14/2014 4:14 PM, Hagen
Weidlich wrote:
>> Am 12.03.2014 17:39, schrieb BobAlston:> ...
>> ...
>> We have two tables. Table 1 holds information for persons. Table 2 just
>> lists the titles. Table 1 has the following fields:
>> ID - autonumber, Primary Key
>> txtName - text
>> fktitle - long integer
>>
>> table 2 just holds
>> ID - autonumber, Primary Key
>> txtTitle - text
>>
>> Between table 1:fkTitle and table 2:ID is a 1:n relation and access is
>> told to enforce referential integrity. Cascade Delete must not be
>> checked!
>>
>> Table 2 holds these entries:
>> 1, Ms
>> 2, Mrs
>> 3, Miss
>> 4, Master
>> 5, Madam
>> 6, M'lady
>>
>> The combo box on the persons form is based on table 2 and bound to
>> fktitle. Since Access is enforcing referential integrity a user can not
>> enter another title(he can however leave it blank). When he tries to
>> change one of the existing titles access treats it as a new one and
>> fires the NotInList event. There you can change the existing one or add
>> another title to table 2.
>>
>> However key to that functionality is that access has an established 1:n
>> relation and referential integrity checked. With that in place no user
>> can delete a used entry in table 2 as long as there are corresponding
>> entries in table 1. Access prevents it and there is no way around! If it
>> is unused the deletion will of course succeed.
>>
>> So it seems you are missing the part where you establish relations and
>> set their properties.
>>
>> Hagen
>>
>
>
> Using your example, after using the table 2 entries for some time, a
> user cannot delete entry 1 "Ms" but they can make it something else
> completely, such as "Missy".  No if you go to a form using a combo box
> for the field, where previously a user selected entry 1 "Ms", they will
> now see "Missy".  There will be no entries for "Ms" in the database.
>
> Bob
I'm sorry. I do have trouble replicating that behaviour. I just created
those two tables, established a relation with referential integrity and
built a form with a text box and a combo box. The combo box however has
the property "LimitToList" set(I hope it's called so in the english
version). With that property set Access raises an error when I try to
change the text. It will fire the NotInList event so I can choose to
change the entry or add a new entry. :/

Hagen

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web