Cascading combo box

[zippy483]

Hi all I have 2 combo boxes on a form associated with 2 tables the first table combo box pair chooses a piece of equipment form a list

the Equipment table is essentially


ID|Equipment|Manufacturer

the combo box allow choice of equipment.

once I've chosen said equipment I want to populate a second combo box with equipment protocols

the protocol table is essentially
ID|Protocol|Manufacturer

I beleive it can be done by setting the row source of the second combobox after the update of the first in a few lines of code that are essentially an SQL string.

I'm looking at something along the lines of

Select Protocol from the Protocol table
Where the manufacturer of the Equipment in the Equipment table matches the Manufacturer in the protocol table

I'm struggling with the SQL statement to achieve this

Any ideas ??

[JeffChr]

You can just grab the SQL from the query builder.

[thebigthing313]

Something like this in the rowsource could work:

SELECT ProtocolID, ProtocolName
FROM tblProtocols
WHERE Manufacturer = DLookup("Manufacturer", "tblEquipment", "EquipmentID = " & Forms!frmName!EquipmentComboBox)

[zippy483]

Might need to rewrite that dogs dinner of a SELECT query again

I'm actually looking to produce a list of protocols from the protocol table that match the manufacutrer in the Machine table where the Machine matches the selection in the Machine combo box on the form

so I'm probably looking at

Select Protocol from the Protocol table
Where the manufacturer in the Protocol table matches the Manufacturer in the Machine table For the machine selected in the Combo box on the form ??

I may just be confusing the issue here though

[zippy483]

Further update and following thebigthing313's advice i placed this in the after update event of the Macine combo box

Me.Protocol.RowSource = "SELECT Protocol, Protocol " & _
"FROM tblPhysicsQCPinnacleProtocol " & _
"WHERE Manufacturer = DLookup([Manufacturer], [tblScannerLookup], [Machine] = & Me.Machine)"

Unfortunately I get an error saying there is a missing operator in the DLookup Expression (I'm hoping Me.Machine refers to the combo box on the form)

I'm struggling with naming conventions as I have inherited this dB and no naming conventions were used in it's construction

[thebigthing313]

That's what the SQL code above does. Unless the protocols are linked to the equipment. I was under the assumption that protocols and equipment have no relationship. If they do (there is a field in the Protocol table for the Equipment), then you can take Manufacturer out of the picture.

SELECT ProtocolID, ProtocolName
FROM tblProtocol INNER JOIN tblEquipment ON tblProtocol.EquipmentID=tblEquipment.EquipmentID
WHERE EquipmentID = Forms!frmName!EquipmentComboBoxName

Keep in mind that when you select an Equipment in the combobox, you will need to Requery the Protocol Combobox to reflect the change. You would do this in the Equipment Combo Box's AfterUpdate event.

[thebigthing313]

If you choose to manually change Rowsource on AfterUpdate using VBA, then your code has wrong syntax:

If the Bound Column in Me.Machine is a number:

Me.Protocol.RowSource = _
"SELECT Protocol " & _
"FROM tblPhysicsQCPinnacleProtocol " & _
"WHERE Manufacturer = '" & DLookup("Manufacturer", "tblScannerLookup", "[Machine] = " & Me.Machine) & "'"

If it is a text field(string):

Me.Protocol.RowSource = _
"SELECT Protocol " & _
"FROM tblPhysicsQCPinnacleProtocol " & _
"WHERE Manufacturer = '" & DLookup("Manufacturer", "tblScannerLookup", "Machine = '" & Me.Machine & "'") & "'"

Your initial query had Protocol twice in the SELECT clause, not sure if that was typo or you wanted to select and ID number and the protocol name. If the latter, then those two fields can't have the same name.

And don't forget to put Me.Protocol.Requery at the end of the AfterUpdate code.

PS: I assumed Manufacturer is a text field (string), by the way.

[zippy483]

Many thanks theBigThing thats just the job

I owe you a pint

[thebigthing313]

Haha, I might take you up on that. Good luck with your project!