Creating 3+ combo boxes that depend on eachother

[abic131]

Hi, I am very new to Access and I could use a couple pointers. I'm creating a database for lab inventory. The tables I've created are Suppliers, Equipment and Parts (for each piece of equipment). I've tried to create relevant relationships between the tables and now I'm trying to create a form. I'd like the first combo box to allow me to select the supplier. Based on which supplier I've created I'd then be directed to choose between the pieces of equipment they supply in the 2nd combo box or list. Finally, once I've selected that I'd be able to choose/add to a list of parts specific to that piece of equipment in a third list. WHen I create a form using the Wizard it gives me a separate page for each supplier instead listing them all in one combo box. When I try to design a form I don't know how to make the lists/combo boxes depend on eachother so they don't change based on what I've selected. Any help is much appreciated.

[abic131]

PS When I use form Wizard the subforms DO depend on eachother. IE which I choose different pieces of equipment the parts change but I have to toggle between pages to get to the different suppliers.

[ItsMe]

Combo Boxes and List Boxes depend on SQL in their Row Source property. Perhaps this sample DB will benefit you. It shows how to Dynamically update a combo's Row Source via VBA.

https://www.accessforums.net/sample-...tml#post200624

There are other approaches too.

[abic131]

Okay so I see what your saying. In that sample DB there was stuff written in the row source and I have nothing written in mine. However I have no idea what to put in there. I tried to attach my db but apparently it's too big for some reason.

[ItsMe]

You can use the query builder to create SQL for your Row Source. Click the ellipses next to the Row Source property field to generate SQL for your Combo Box.

.

[abic131]

Okay awesome I'm starting to get it. Thank you so much!

[ItsMe]

It is not easy. After you start to get the bigger picture you will have more questions.

[abic131]

Okay so I've gotten to the point where I've designed the first combo box to show both company names using a query SQL and a second list to display equipment. But the list of equipment doesn't change based on the company name I've selected. It just shows all of the equipment. It's like the two combo boxes aren't linked... I've based my row source on the sample database which is super similar to mine. What could I possibly be missing?

[ItsMe]

The name of the First combo needs to be within the SQL of the second combo. The WHERE criteria needs to include the name of the combo.

Here is one method
http://www.datapigtechnologies.com/f...combobox2.html

Another approach would be to use VBA. There is an example of this in the DB you downloaded.

[abic131]

Okay so I followed the method on the link that you sent me and it prompted me for a parameter for the first combo. If I type in 1 I get the pieces of equipment associated with the 1st company both times and if I type in 2 it's the equipment from the second company both times. What should I put in for a parameter?

[ItsMe]

Something is not correct. Can you post the SQL from each of the combos here?

[abic131]

Okay, do you mean what I typed into Row source? Here it is. I actually tried to change it to the same form as what was in the sample db you sent me and the same thing happened.

SELECT [Suppliers Query].[Company Name] FROM [Suppliers Query] ORDER BY [Company Name];

SELECT [Equipment Query].[Equipment ID], [Equipment Query].Description FROM [Equipment Query] WHERE ((([Equipment Query].[Supplier ID])=Forms![frmForm1]![Suppliers Query])) ORDER BY [Equipment Query].[Equipment ID], [Equipment Query].Description;

[abic131]

I'm way off aren't I haha

[ItsMe]

So the first thing you should do is include the Primary Key field from Suppliers Query in your combo.

You have one column in your SELECT statement. Maybe the following would work, I am just guessing.
SELECT [Suppliers Query].[Supplier ID], [Suppliers Query].[Company Name] FROM [Suppliers Query] ORDER BY [Company Name];

Then you need to adjust the properties of your first combo to accompany two columns, bind the first column, and set the Column Widths.

So maybe
Bound Column = 1
Column Widths = 1", 1"
Column Count = 2

Look in the Data tab and Format tab of the property sheet for these properties.


Then, in the second combo, you need to adjust the WHERE clause to include the name of the control that is the first Combo Box.
WHERE ((([Equipment Query].[Supplier ID])=Forms![frmForm1]![Suppliers Query]))

Is Suppliers Query the name of your combo?

[abic131]

OK so I took your advice and I also changed the names on my combos and everything to make more sense. Here's what I have.

SELECT qrySuppliers.[Company Name] FROM qrySuppliers ORDER BY qrySuppliers.[Company Name]; This worked fine

SELECT qryEquip.[Equipment ID], qryEquip.Description FROM qryEquip WHERE (((qryEquip.[Supplier ID])=[Forms]![frmInventory]![cmbSuppliers])) ORDER BY qryEquip.Description;

This is starting to make some sense but it's still not working, none of the equipment shows up.