Queries on Forms via LookUp


Active Member
Sep 2, 2008
Trying to find if it is possible without a macro to select a query value to run that would configure a lookup dropdown box.

For instance if I am tracking automation equipment I have a drop down for equipment type (monitor, desktop, notebook, etc.)

I also have a dropdown for Manufacturers and one for model.

Say I choose monitor then it queries all the monitors I have and changes the Manufacturers lookup to the manufacturers that we have monitors for (HP, Dell, Samsung).

Once I choose a Manufacturer the dropdown for models change to lets Say models of HP monitors in my inventory.

Any examples or links for explanations on how or why you cant do this would be appreciated.

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

This is explained fairly well here I think:

See if that helps and post back if you still have more questions.
(If anyone has another good suggestion please chime in).


I got this to sort of work. I created a query to pull in the information into one table. The problem is when I choose say Monitor I get the results in another combobox that says HP, Dell, Dell, HP, Samsung. It didnt filter out unique records only.
Upvote 0
Now I do have a legitimate question. Got this to work in my form. Once I choose the Monitor Type the Manufacturer chooses the distinct options that match the type. Here is what didnt work and I am glad I made a backup before continuing. Not only does this change the current record only but every record in the database in my table my form links to.
Upvote 0
Well that's curious. That kind of thing can happen if you have the records in a subform. Or if you have Continous Form instead of Single Form (all records vs. one record). Queries shouldn't be changing anything anyway, strictly speaking. What are you trying to do? Is this an update form? An entry form?
Upvote 0
Its an entry/update form for doing inventories.

My form is a split form. I have the combobox called cboEquipType and cboManufacturer.

I did what it said for row source on cboEquiptype where I placed the SQL code:
SELECT DISTINCT qryEquipment.EquipmentType FROM qryEquipment ORDER BY qryEquipment.EquipmentType;
I also have in code builder the following code:

Private Sub cboEquipType_AfterUpdate()
 End Sub
     On Error Resume Next
    cboManufacturer.RowSource = "SELECT DISTINCT qryEquipment.Manufacturer " & _
       "FROM qryEquipment " & _
       "WHERE qryEquipment.EquipmentType = '" & cboEquipType.Value & "'" & _
       "ORDER BY qryEquipment.Manufacturer;"
 End Sub

Say I go to record 1 and realize I made a mistake and it is not a desktop but a notebook. If I change it to desktop all 93 records change to desktop.
Upvote 0
Update In the split form the upper portion is showing the one record at a time but the lower form shows the entire table with all records. Could that be the problem?
Last edited:
Upvote 0
I have things like this happen to me but not for some while. I think it's because nowadays I just use Access' built in filtering far more often than trying to create filters with comboboxes (I would use that for data entry only).

I guess this begs the question of what you mean by "doing inventories" and why your form is split. If you just need to see filtered data, you first filter by equipment type, then by manufacturer. Usually right clicking in a field is enough for that. But this sounds like more than that.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back