Combo box query

[Guest]

I have a series of command buttons number 1 to 12 that open a new form where
I can select different attributes which then updates information on the
original form (reason for this is the editing is password protected) one of
them is a combo box which has a list from a table called “Cables†the list is
1 – 12 which when selected passes data back into the cables text box on the
original form. if I select number 2 I want it to disappear from the list for
that record ie 1, 3, 4, 5 etc so that I don’t have users select cable 2 more
than once for that record. When I go to the next record I want to be able to
have all 12 available to select again and so on
Do I use a query on the combo box or vba code someone suggested using a
query and in criteria using “is not nullâ€.

Hope this makes sense

 

[Michel Walsh]

How do you 'undo' an error? If I select 2, while I wanted to select 3, what
will I do?


Anyhow, ***using combo box***, with their RowSource set to:

SELECT id FROM oneToTwelve WHERE id NOT IN(FORMS!formNameHere!Control1,
FORMS!formNameHere!Control2, FORMS!formNameHere!Control3,
FORMS!formNameHere!Control3, ....FORMS!formNameHere!Control12)


assuming that table oneToTwelve has one field, id, and 12 records, with
values from 1 to 12.


will allow you to select, in each 12 combo box, 12 different values. NOTE
that a NULL (no entry) is also allowed, for each combo box, even if you
LIMIT TO LIST.


Maybe important to note that Jet evaluates

5 NOT IN(1, 2, null)


as true, so NULL values (combo box without a selected value, at this point)
work fine for this actual case. MS SQL Server evaluates the same expression
as UNKNOWN, so, using MS SQL Server would be different.


When you create a new record, all combo box, each of them bound to a field,
would have their value set to null, unless you defined a default value, in
the table design, for the fields tied to the combo box.



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Forgot to mention that to dynamically got your 'list' updated under each
combo box, you should write a line of code like:


Me.ComboboxName.RowSource = Me.ComboboxName.RowSource


in the procedure handling the GotFocus event, of each combo box.



Vanderghast, Access MVP