Data type mismatch in criteria expression error in cascading combobox

Question

I'm getting a "data type mismatch in criteria expression" error every time on a form I have set up in my database with cascading comboboxes from two different tables: tblUnits and tblProjects. The first combobox (cboUnit) selects the appropriate UnitID from tblUnits, while the second (cboProject) limits options to ProjectID's to the selected UnitID.

RowSource for cboUnit:

SELECT [tblUnits].ID, [tblUnits].Unit FROM tblUnits ORDER BY [Unit];

And here's the code for the AfterUpdate:

Private Sub cboUnit_AfterUpdate()
     On Error Resume Next
     cboProject.RowSource = "Select tblProjects.ID " & _
                          "FROM tblProjects " & _ 
                          "WHERE tblProjects.UnitID = '" & cboUnit.Value & _ 
                          "ORDER BY tblProjects.ID;"

The ProjectID and UnitID fields in the destination table are numeric but are NOT lookup fields, and the bound columns are correct. I don't understand why I keep getting the criteria mismatch even when dealing strictly with the foreign keys.

the quote in the WHERE clause has to be deleted and a blank is needed in front of ORDER ! Check with debug.print cboUnit.Value if cboUnitis bound to UnitID. — BitAccesser

HansUp HansUp · Accepted Answer · 2016-06-08T18:55:58

Christopher explained how to cure the error from the SELECT statement the code builds.

However, I don't think you should need to modify cboProject.RowSource every time the user selects a value in cboUnit.

It should be simpler to use this for cboProject.RowSource, and don't change it afterward:

SELECT p.ID FROM tblProjects AS p
WHERE p.UnitID = [Forms]![YourFormNameHere]![cboUnit]
ORDER BY p.ID;

Then all you need to do for cboUnit After Update is requery cboProject:

Private Sub cboUnit_AfterUpdate()
    Me!cboProject.Requery

Data type mismatch in criteria expression error in cascading combobox

2 Answers