Syntax of SQL statement in Visual Basic.

[Carlos]

I'm using an onUpdate event to determine the rowsource of
a combo box based on a selection in another combo box.
e.g. I choose Honda in one combo box and Honda models
become the rowsource in the next combo box. My table
contains three fields: system, application, and detail.
This particular form is associated to a particular system,
while the first combo box lists the applications, and the
the second combo box should list the details associated
with the application selected in the first box.
Below is the event code that I've tried. Note the use of
the variable strCategory in the SQL statement.

Private Sub Combo0_AfterUpdate()
Dim strCategory As String

strCategory = [Combo0]

Me!Combo2.RowSource = SELECT Category.detail FROM Category
WHERE (((Category.system)="ABC") AND ((Category.category)
="strCategory"));

End Sub

This code does not work. The trick I can't manage, I
believe, is in the use of quotation marks to fit the SQL
statement into the module.
Could someone please guide me in the exact syntax.

Thank you,

Carlos

 

[Marshall Barton]

I'm using an onUpdate event to determine the rowsource of
a combo box based on a selection in another combo box.
e.g. I choose Honda in one combo box and Honda models
become the rowsource in the next combo box. My table
contains three fields: system, application, and detail.
This particular form is associated to a particular system,
while the first combo box lists the applications, and the
the second combo box should list the details associated
with the application selected in the first box.
Below is the event code that I've tried. Note the use of
the variable strCategory in the SQL statement.

Private Sub Combo0_AfterUpdate()
Dim strCategory As String

strCategory = [Combo0]

Me!Combo2.RowSource = SELECT Category.detail FROM Category
WHERE (((Category.system)="ABC") AND ((Category.category)
="strCategory"));

You need to plave the value of the variable in the SQL, not
the name of the variable. The entire SQL statement must be
enclosed in quotes and quotes within quotes need to be
doubleed up. The end result of all that would be:

Me!Combo2.RowSource = "SELECT Category.detail " _
& "FROM Category " _
& "WHERE Category.system=""ABC"" " _
& "AND Category.category=""" & strCategory & """"

 

[Kevin]

I think the following should work for you:

Me!Combo2.RowSource = SELECT Category.detail FROM Category
WHERE (((Category.system)='" & ABC & "') AND
((Category.category)
='" & strCategory & "'));

I am not sure I can explain to you why this syntax works,
but one of the true gurus can.

Hope that helps!

Kevin