Dynamic Combo's in datasheet (HELP!)

[Guest]

Hi there,

I'm having a problem with a certain data access methodology that I'm trying
to incorporate into my Access project.

To describe my problem, I'll give an example:

Say you have 3 comboboxes within a datasheet that are bound to fields in a
table. These comboboxes have underlying rowsource queries that rely on each
other to act as filters. ie. combo B requires a value from combo A, and combo
C requires a value from combo B. Therefore, by selecting a value from combo
A, you can populate different values into B, and similarly from B into C.

Once you've selected a value in A, you have to requery B in order to re-run
the underlying rowsource query to poplate B with new values. The same is done
for C when you select a value in B. This works wonderfully when there is only
1 row in the table.

The problem I'm having is that when I move to a new record (record 2) and
start the same process, selecting a value in combo A requeries combo B and,
for some reason, requeries combo B in the FIRST RECORD! Therefore the first
record's combo B changes!!!! I guess this is because of the fact that
logically it's the same combo box, but I need to change the combo's value for
the selected record ONLY!

PLEASE HELP!!! What is wrong with my methodology? These dynamic combo's are
brilliant and I'd really like to use them but only if I can requery the
combo's from the selected record.

Thanks very much!!!

Miguel Bento
IT Consultant
(e-mail address removed)

 

[Marshall Barton]

I'm having a problem with a certain data access methodology that I'm trying
to incorporate into my Access project.

To describe my problem, I'll give an example:

Say you have 3 comboboxes within a datasheet that are bound to fields in a
table. These comboboxes have underlying rowsource queries that rely on each
other to act as filters. ie. combo B requires a value from combo A, and combo
C requires a value from combo B. Therefore, by selecting a value from combo
A, you can populate different values into B, and similarly from B into C.

Once you've selected a value in A, you have to requery B in order to re-run
the underlying rowsource query to poplate B with new values. The same is done
for C when you select a value in B. This works wonderfully when there is only
1 row in the table.

The problem I'm having is that when I move to a new record (record 2) and
start the same process, selecting a value in combo A requeries combo B and,
for some reason, requeries combo B in the FIRST RECORD! Therefore the first
record's combo B changes!!!! I guess this is because of the fact that
logically it's the same combo box, but I need to change the combo's value for
the selected record ONLY!

PLEASE HELP!!! What is wrong with my methodology? These dynamic combo's are
brilliant and I'd really like to use them but only if I can requery the
combo's from the selected record.

There is nothing wrong with your method. The problem is
that there is only one combo box value to use in the row
source query for all the other rows/records that are
displayed in sheet (or continuous) view.

Note that you will need to have the same Requery statements
in the form's Current event to make sure that the current
record combo boxes are displaying the appropriate list.