Bernard Thouin
asked on
MS Access continuous form with dropdown that should show for each row different data depending on value of another field in same row
This is an Access 2013 VBA question.
I have a subform of type continuous form that shows selected rows of data from a table. in the first field I have a kind of record type, say A or B. In the third field, which is shown as a a combobox, I have a value that has to come from two different data sets depending on the record type. I.e.
- if record type is A, then the dropdown in the third field should show a set of values (selected from a separate table, in a column that holds the possible values of the 3rd field for record type A)
- if record type is B, then the dropdown in the third field should show a different set of values (selected from the same separate table, in a column that holds the values of the 3rd field for record type B)
I am really stuck with that user requirement, and don't know if I should have
- 1 or 2 comboboxes for the 2 record types and ony show one or the other
- 2 separate select statements depending on the type
- and even how to find out, when the row is displayed, if record type is or B.
There doesn't seem to be an event that I can catch when each row is being displayed, so that I could populate the values for the combobox(es) properly depending on the record type
Any idea how I can do what the users want ?
Thanks for help.
Bernard
I have a subform of type continuous form that shows selected rows of data from a table. in the first field I have a kind of record type, say A or B. In the third field, which is shown as a a combobox, I have a value that has to come from two different data sets depending on the record type. I.e.
- if record type is A, then the dropdown in the third field should show a set of values (selected from a separate table, in a column that holds the possible values of the 3rd field for record type A)
- if record type is B, then the dropdown in the third field should show a different set of values (selected from the same separate table, in a column that holds the values of the 3rd field for record type B)
I am really stuck with that user requirement, and don't know if I should have
- 1 or 2 comboboxes for the 2 record types and ony show one or the other
- 2 separate select statements depending on the type
- and even how to find out, when the row is displayed, if record type is or B.
There doesn't seem to be an event that I can catch when each row is being displayed, so that I could populate the values for the combobox(es) properly depending on the record type
Any idea how I can do what the users want ?
Thanks for help.
Bernard
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi guys
To be honest, I only looked at John's solution, which was just perfect for me, made a nice generic litte sub out of it, as I had more tan just one control with the same problem, and it works a treat :)
So thanks to both of you for answering so quickly :)
Regards
Bernard
To be honest, I only looked at John's solution, which was just perfect for me, made a nice generic litte sub out of it, as I had more tan just one control with the same problem, and it works a treat :)
So thanks to both of you for answering so quickly :)
Regards
Bernard
and this example database.
In the sample database, the form "MyContinuous_Working" shows the textbox associated with the second value below the actual combo. In the "MyContinuous" form, it displays this with the textbox placed over top of the combo.
20180807-Cascading-Combos-in-Conti.accdb