Syntax of SQL in VB

[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

 

[fredg]

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

Carlos,

No need for the strCatagory variable.

1) If the Bound column of Combo0 is a Number datatype:

Me!Combo2.RowSource = "SELECT Category.detail FROM Category
WHERE Category.system= 'ABC' AND Category.category = " & Me!Combo0 & "
Order By Category.Detail;"

2) If the Bound column of Combo0 is a Text datatype:

Me!Combo2.RowSource = "SELECT Category.detail FROM Category
WHERE Category.system= 'ABC' AND Category.category = '" & Me!Combo0 &
"' Order By Category.Detail;"

Note.. the entire SQL is a string and must be within quotation marks.
Therefore text within the quoted string (ABC and the value of
Me!Combo0) must be delimited using single quotes (').
I added an Order By clause to sort the combo box. If you do not wish
it sorted, don't add it, but keep the semicolon.