Rowsource with multiple WHERE criteria throwing Syntax error

Nadine67

Board Regular
Joined
May 27, 2015
Messages
225
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.

Code:
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!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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.
 
Upvote 0
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.

Code:
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
 
Last edited:
Upvote 0
I don't think you are supposed to have quotes inside the brackets:
Code:
& " AND Make=" & ['Forms']!['frmUnitNew']!['cboMake'] & ")"

I think you mean to write:
Code:
& " AND Make=" & "'" & [Forms]![frmUnitNew]![cboMake] & "'" & ")"
 
Upvote 0
****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!
 
Upvote 0
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

Code:
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
Code:
whatever = DLookup(field, table,strCriteria)

HTH
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top