Combo Box to declared variable for second combo lookup query

[Haagimus]

I am trying to create an access form that will have multiple combo box drop downs. There are two that will need to be linked in the form. The selection of the first will need to become the query criteria for the second. Everything I try ends up popping up a box and asking for inputs instead of running the query.

***This also needs to be a one time variable as the form has a subform that creates new entries for missions as needed so the lookup will be unique to each entry.

eg. Combo box 1 lets user select a squadron from a list of 3, when that combo is selected it should allow the second combo box to run a query to lookup the value in the first combo box and return the results as the list in the second combo.

I have been trying to figure this out for 3 days now and it is starting to get on my nerves. Can anyone out there help?

[Seth Schrock]

You can reference your combobox in your query criteria as follows. This assumes a form name of frmForm1, a combobox name of cboCombo1 and a field name of Squadron. Also, since when you first open the form, your first combobox will be empty, you will need to handle the null/empty string value depending on if Squadron is a number or text field. This can be done using the Nz() function.

WHERE Squadron = Nz(Forms!frmForm1!cboCombo1, 0)

You would then need to requery your second combobox in your first combobox's AfterUpdate event. This will make it so that as soon as you make a selection in your first combobox, it will requery your second combobox to list the appropriate items.

[Haagimus]

SELECT Personnel.[Name-Last]
FROM Personnel
WHERE (((Personnel.[Name-Last])<>"") AND ((Personnel.[Home Station Organization])=[Forms]![Ops Sup_Mission Subform]![Squadron]) AND ((Personnel.[Duty Status])="Present"))
ORDER BY Personnel.[Name-Last];

This is the script that is running directly from the query itself. Now when I run this either from the query directly or through the form it pops up a text entry box with "Forms]![Ops Sup_Mission Subform]![Squadron]" above the text entry box itself.

[Seth Schrock]

To reference a control on a subform, you do it like this:

Forms!MainFormName![Ops Sup_Mission Subform].Form!Squadron

Click Here to see an article that explains how it works.

[Haagimus]

Fantastic that fixed that. Now when I update the second combo however it changes every second combo in the subform?

**Nevermind, just realized the table that the data writes to does not have a commander block in it, adding it should fix the problem. Thanks Seth you da man.

[zmbd]

The following may also give you some other ideas:

-filtering-
Microsoft Access / VBA Insights Sitemap
42.Cascaded Form Filtering
55.Multiple Combobox Filter with Clear Option
168.Example Filtering on a Form.