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

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

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

Show all headers | View raw

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

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