Rowsource with multiple WHERE criteria throwing Syntax error

[Nadine67]

Hello and thank you for any attention my post may receive.

My goal is to have multiple cascading combo boxes, and I have succeeded with the first two. I receive a syntax error with the third.

In my first code below, cboMake populates as it should after cboUnit is updated. However the second piece of code throws a syntax error on the WHERE criteria when I click into cboModel.

cboUnit is unbound with a ValueList rowsource (1;2;3;4;5;6;7).
cboMake is unbound with the below code (cboUnit_AfterUpdate) as rowsource, an rowsource type is Table/Query.
cboModel is unbound with the below code (cboMake_AfterUpdate) as rowsource, an rowsource type is Table/Query.

Private Sub cboUnit_AfterUpdate()
On Error Resume Next
    Me.cboMake = Null
    Me.cboModel = Null
    cboMake.RowSource = "Select Make from tblUnit where (ID_Unit=" & [Forms]![frmUnitNew]![cboUnit] & ")"
    cboMake.Requery
End Sub



Private Sub cboMake_AfterUpdate()
On Error Resume Next
    Me.cboModel = Null
    cboModel.RowSource = "Select Model from tblUnit where (ID_Unit=" & [Forms]![frmUnitNew]![cboUnit] & " AND Make=" & [Forms]![frmUnitNew]![cboMake] & ")"
    cboModel.Requery
End Sub

Thank you again!

 

[stumac]

What is the error?

You aren't dealing with possible Null values in either cboMake or cboUnit. Also is cboMake returning a number, if not then it needs text qualifiers.

Something else to watch out for using the AfterUpdate event to re-load cascading combo box values is that if you edit an existing value it can show the wrong options for the combo box (if someone edits Model without first editing Make - the rowsource will be from the last record updated.) - I use the gotFocus event for the updating of the source.

 

[welshgasman]

If any values are text, then enclose in single quotes.

 

[Nadine67]

Thank you stumac and welshgasman!

You are both on the money because both cboMake and cbo Model contain alphanumeric values.


I have updated my code and no longer receive the error which is great. But....cboModel does not populate with a selection list. I know it should populate with at least three options to select from.

Private Sub cboUnit_AfterUpdate()
On Error Resume Next
    Me.cboMake = Null
    Me.cboModel = Null
    Me.cboMake.SetFocus
    cboMake.RowSource = "SELECT DISTINCT Make FROM tblUnit WHERE (ID_Unit=" & [Forms]![frmUnitNew]![cboUnit] & ")"
    cboMake.Requery
End Sub


Private Sub cboMake_AfterUpdate()
On Error Resume Next
    Me.cboModel = Null
    Me.cboModel.SetFocus
    cboModel.RowSource = "SELECT DISTINCT Model FROM tblUnit WHERE (ID_Unit=" & [Forms]![frmUnitNew]![cboUnit] & " AND Make=" & ['Forms']!['frmUnitNew']!['cboMake'] & ")"
    cboModel.Requery
End Sub

 

[xenou]

I don't think you are supposed to have quotes inside the brackets:

& " AND Make=" & ['Forms']!['frmUnitNew']!['cboMake'] & ")"

I think you mean to write:

& " AND Make=" & "'" & [Forms]![frmUnitNew]![cboMake] & "'" & ")"

 

[Nadine67]

****SOLVED****

Thank you so much xenou.

That is it......xenou, stumac and welshgasman have solved my problem, and I am happy.

I hope you all have a great day!

 

[welshgasman]

I'll give you a tip that has saved me time and a having to ask a lot of questions.
Build your criteria to a string

strCriteria = .....

and assign you dlookups, sql whatever to that string.

THEN you can debug.print it and see if it contains what you *think* it contains.

then use it in your code

whatever = DLookup(field, table,strCriteria)

HTH

 

[Nadine67]

Thank you welshgasman.

Gratefully I will take your tip, and use it.