Cascading Combo Boxes

[alan1203]

Hi. I hope somebody can point me in the right direction with help on how to use cascading combo boxes on MS Access forms.

I have 4 tables;

tblManufacturer;
ManufacturerID (PK)
Manufacturer

tbl Model;
ModelID (PK)
Model

tblMachineParts;
PartID (PK)
ManufacturerID (FK)
ModelID (FK)
PartNumber(FK)

tblParts;
PartDescription
PartNumber (PK)
Supplier

The same part can be used by multiple manufacturers and thats why I have tblMachineparts.

What i'm trying to do is have a form with cascading combo boxes so that the user can select a manufacturer and model and then a list of available parts is shown on a subform.

I have 2 unbound combo boxes
cboManufacturer and cboModel

For the rowsource for cboManufacturer I have:
SELECT tblManufacturer.ManufacturerID, tblManufacturer.EquipmentManufacturer FROM tblManufacturer

The rowsource for cboModel is based on the afterupdate event on cbomanufacturer:
Private Sub
cboManufacturer_AfterUpdate()
Me.cboModel.RowSource = "Select[ModelID]FROM tblMachineParts WHERE [ManufacturerID]=""&cboManufacturer&"""
Me.cboModel.Requery
End Sub

cboManufacturer works no problem and brings up all the manufacturers but when I click on cboModel I get "Data type mismatch in criteria expression" error.

I'm grateful for any advice given.
Alan

 

[PHV]

If ManufacturerID is defined as numeric in tblMachineParts:

Me!cboModel.RowSource = "Select ModelID FROM tblMachineParts WHERE ManufacturerID=" & Me!cboManufacturer

If ManufacturerID is defined as text:

Me!cboModel.RowSource = "Select ModelID FROM tblMachineParts WHERE ManufacturerID='" & Me!cboManufacturer & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Darrylles]

Hi,

Alan seemed to have different quotes to this:
[tt]
Me.cboModel.RowSource = "Select[ModelID]FROM tblMachineParts WHERE [ManufacturerID]= '" & cboManufacturer & " ';"
[/tt]

Which you are attempting to cover dependant on type also PHV. However, is the answer: NOT using double-quotes to encapsulate a numeric value as Alan has done, rather than getting mixed up with encapsulating a string unnecessarily?

All well and good giving the solution PHV, but the complete answer might be better (saves 'em coming back to give you unnecessary points yet again PHV).

ATB

Darrylle









Never argue with an idiot, he'll bring you down to his level - then beat you with experience.