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

Groups > comp.databases.ms-access > #9156

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

Show all headers | View raw

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

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web