Multiple combo boxes

[unknownop]

Hello, I have already searched the past threads that people have sent about using multiple combo boxes, but I cannot use the theory in the answers to these posts to sort out my situation.

For an A-Level project, I am building a database for stock management.In a customer order form, I have 2 drop downs (not sure whether to use combo or list boxes) amongst other items.

The first drop down lists all manufacturers of printer cartridges the company stores and is taken from one table (a field called M_Name) called tbl_make.

In another table (tbl_stock), there are a few fields, 2 of them are M_Name and the other is S_Name (this is the name of the printer cartridge), they are next to each other in the table.

The first drop down has all the values of M_Name listed. This is taken from tbl_make as there are no repeating makes. It can not be taken from tbl_stock because there will be repeating makes ie, there are lots of HP cartridge so this would be displayed in the drop down:

HP
HP
HP
Epson
Kyocera

etc...

The second drop down I have displays all the cartridges that are under the field S_Name in the tbl_stock table. My question is how can I have my drop downs so when I select HP for example (in the first box) all the HP printer cartridges are displayed.

I thought of one way of doing it would be that all the printer catridge manufacturers in the first drop down are queries that display the cartridges from tbl_stock in the next drop down box. Each query for example 'HP' would look at the two fields (M_Name and S_Name) in tbl_stock table and find all the M_Name values that were HP and so list all the cartridges that were next to the HP M_Name.

Sorry this was so long winded... I have no idea how to do this so any help appreciated really. thanks andi

 

[JerichoJ]

I am surprised that there is nothing in the archives that explains this. But, here it is, and you will need some coding:

Create an "AfterUpdate" event of Combo1 (Manufacturers)

Dim mssql as String

mssql = "SELECT * FROM tbl_stock "
mssql = mssql & "WHERE ((tbl_stock.M_Name) = """
mssql = mssql & me.Combo1 & """"
me.Combo2.RowSource = mssql


Voila! Everytime a manufacturer is selected in Combo1, the afterupdate event fires, changing the rowsource for Combo2.

Now, you will want to replace "SELECT * " with the appropriate columns from your tbl_stock that you need.

Also, you will notice the triple double-quotes. From your description, it appears that M_Name is a text field. By properly using the triple double-quotes, it causes the resulting sql string to become:

...M_Name) = "HP"

That way, if any of your manufacturer names happen to have a single quote in them, they will be properly handled.

 

[unknownop]

sorry to be ignorant but is this typed in the expression builder or as a query???

"
Dim mssql as String

mssql = "SELECT * FROM tbl_stock "
mssql = mssql & "WHERE ((tbl_stock.M_Name) = """
mssql = mssql & me.Combo1 & """"
me.Combo2.RowSource = mssql
"

thanks again, andi

 

[JerichoJ]

This is not entered into either the expression builder or as a query.

From the form design view, highlight the Combo1 combo box and bring up the properties window. Click on the "Events" tab. Click on the event "After Update". A button with elipses will appear on the right side. Click that button. If you are asked to choose a builder, choose Code builder.

This will give you the shell of the event procedure. Then the code I provided goes there. You will have to make substitutions in the code for the actual table names, field names and control names that you are using.

 

[cmgarOK]

Hi,
I've been working on a similar project in Access with multiple combo boxes and I can't seem to get the code you posted to work the code that I'm using seems to be in the correct format. It even prints out the right SQL when I click the first combo box. The problem is that it's storing the SQL statement as a regular string. I'm wondering if you might have to Dim the mssql variable as some kind of executable statement? The problem mine is giving me is after the first combo box updates the second box is filled in with the SQL statement. No values are being dropped in the boxes. The Statement prints out and will actually run and work when I copy and paste it into a separate query window. The code I've got on the AfterUpdate method for the first combo box is this:

Dim mssql As String

mssql = "SELECT Shifts.shift FROM Shifts "
mssql = mssql & "Where ((Shifts.stcNo) = """
mssql = mssql & Me.cboStcNames & """"

Me.cboLabShifts.RowSource = mssql

and what is being printed to the cboLabShifts box is:

SELECT Shifts.shift FROM Shifts Where ((Shifts.stcNo)= "26&quot

Which when used in a regular query I have to enter a parameter for Shifts.stcNo but normally it should be filled in by the combo box, right?

I've tried to define the variable as a few different things and none of them have worked. I'd appreciate any help if you see anything I'm doing wrong.

Thanks...

 

[JerichoJ]

cmgarOK,

Your problem appears to be related to the data type for your stcNo field. What data type is this field? From what you have shown, it appears to be a numerical type, probably Long Integer.

If so, you must remove the extra double-quotes in the SQL string that the code builds. The code I gave was for a text data type. And as you point it, it produces:

Where ((Shifts.stcNo)= "26&quot

If your stcNo is a numeric field, then you want:

Where ((Shifts.stcNo)= 26)

To do this, change your code to the following:

mssql = mssql & "Where ((Shifts.stcNo) = "
mssql = mssql & Me.cboStcNames & ""

 

[cmgarOK]

Thanks Jericho you really helped me out. I put in the code that worked for the integers and it still didn't work then I realized I'd set my RowSource Type to Value List. Once I changed that to Table/Query it worked like a charm. I really appreciate it.