So when i choose a selection from one combo box, my 2nd combo box will only show a list based on the entry I selected in the first combo box. It works great.
But when I embed this form into another form (essentially making the form in question a subform), it doesn't work! As soon as I make a choice in the first combo box, a pop up box called "Enter Parameter Value" shows up with "Forms!MySubformName!MyFirstComboBoxName" in the details and a field where I have to fill in something. At this point, my 2nd combo box doesn't show any list at all.
I followed the instructions provided in the microsoft office link above (I added the criteria to the 2nd combo box query, and I added the code to the first combo box's After Update event), and it worked within a form, but when that form is embedded as a subform within another form, it ceases to work!
why? can someone help me here?
If you can base your answer on the example provided in the microsoft access link, then I will duplicate it on my form.
I have a combo box called "cbxFunction" and a 2nd combo box called "cbxProcedure".
cbxFunction has 2 fields in its rowsource query: FunctionID, and Function
cbxProcedure has 3 fields in its rowsource query: ProcedureID, Procedure, and Function.
I added criteria to the cbxProcedure's Function field which says:
[Forms]![Subform New Transaction]![cbxFunction]
where Subform New Transaction is the name of the form.
Then, in cbxFunction's After Update event, I added the following code:
Private Sub cbxFunction_AfterUpdate()
Me.cbxProcedure = Null
Me.cbxProcedure = Me.cbxProcedure.ItemData(0)
When I open Subform New Transaction as it's own form, everything works great. The problem is that this form is embedded as a subform in another form called Stats Log. And when I open the Stats Log, then select a choice from the first combo box within the Subform New Transaction subform - the following pop up box appears: Enter Parameter Value
Forms!Subform New Transaction!cbxFunction
- and there is a space where I am supposed to fill in something.
Anyways, at this point I close the pop up box, and the 2nd combo box doesn't show any list in the drop down.
I don't have enough time to analyse this fully, so I am gonna take a guess....
The reference from one combo to the other will change when it is a subform vs when it is not.
Forms!Subform New Transaction!cbxFunction is no longer valid. That is why you are getting the "Enter Parameter Value" dialog. Instead you will need something like this:
Forms!SubformObjectName.Form!ComboBoxControlName or something like that.... while the subform is in place, edit the other subform and use the build button for the criteria of the dependant combo box... doing it that way you'll get the syntax right.
Hope I am on the right track here
It will be something simple like that as cascading combo boxes do work!