DLookup

[madurham]

I'm trying to set up a form with two synchronised combo boxes. I keep running into issues with the code as I'm learning as I'm going.

The first combo box is used to select manufacturer and the second should update to only show models from the manufacturer chosen from the first combo box.

The 'Assets' table contains the 'model' column (the intended result) and a 'manufacturer_id'. This is linked to the 'manufacturer_id' on the 'Manufacturers' table that has a 'name' column (the one selected from the 1st combo box).

This is what I have:

Private Sub cboManufacturer_AfterUpdate()
    
    Dim varManID As Variant
    varManID = DLookup("[manufacturer_id]", "Manufacturers", "[name] ='" & Me.cboManufacturer.Value & "'")
    
    Me.cboModel.RowSource = "SELECT model FROM Assets WHERE Manufacturers.manufacturer_id = " & varManID & " ORDER by model"
                            
    Me.cboModel = Me.cboModel.ItemData(0)
End Sub

When selecting a value from the first combo box I receive a popup asking for a parameter value for Manufacturers.manufacturer_id. I assume the Dlookup is the problem?

 

[arnelgp]

try adding a Breakpoint (f9) on the code, and see where the error is.

what is the fieldtype of manufacturer_id?

 

[madurham]

It steps through the code fine until it reaches the 'End Sub' line, then the popup appears.

Manufacturer_id on Assets is Number
Manufacturer_id on Manufacturers is Autonumber

 

[mdnuts]

It seems like you're asking for a join without the join. In other words you're trying to find a fieldname from the manufacturers table inside the assets table.

Me.cboModel.RowSource = "SELECT model FROM Assets WHERE Manufacturers.manufacturer_id = " & varManID & " ORDER by model"

Should be something like (if manufacturer_id in table assets is the same name as it is in table Model).

Me.cboModel.RowSource = "SELECT model FROM Assets WHERE manufacturer_id = " & varManID & " ORDER by  model"

 

[arnelgp]

change this portion:

Me.cboModel.RowSource = "SELECT model FROM Assets WHERE Manufacturers.manufacturer_id = " & varManID & " ORDER by model"

to:

Me.cboModel.RowSource = "SELECT model FROM Assets WHERE manufacturer_id = " & varManID & " ORDER by model"

 

[madurham]

I see where I went wrong that solved it. Thanks guys.