asked on

MS Access VBA Syntax for Combo Box After Update Event

I need to populate a second combo box based on the item selected from the 1st combo box.

cboCategoryList is combo box 1.
cboFeatureList is combo box 2.
The combo box value is numeric.
Combo Box After Update

Private Sub cboCategoryList_AfterUpdate()
Me.cboFeatureList.RowSource = "SELECT Feature.Fea_No, Feature.Fea_Name, Feature.Cat_No FROM Feature WHERE Feature.Cat_No = "
Me.cboCategoryList.SelText " ORDER BY Feature.Fea_Name ; "

Me.cboCategoryList.SelText Is this the value selected from cboCategoryList ?

How do I append the strings?

Me.cboFeatureList.RowSource = "bla sle9 " & " geoel dakf" is this correct?

Thanks,

End Sub

Rey Obrero (Capricorn1)

if Cat_No is NUMBER datatype the syntax is

Me.cboFeatureList.RowSource = "SELECT Feature.Fea_No, Feature.Fea_Name, Feature.Cat_No FROM Feature WHERE Feature.Cat_No = " & Me.cboCategoryList & " ORDER BY Feature.Fea_Name"

if Cat_No is TEXT data type the syntax is

Me.cboFeatureList.RowSource = "SELECT Feature.Fea_No, Feature.Fea_Name, Feature.Cat_No FROM Feature WHERE Feature.Cat_No = '" & Me.cboCategoryList & "' ORDER BY Feature.Fea_Name"

the value of the cboCategoryList is set to the Bound Column in the Rwosource of the combo box.

Dovberman

ASKER

Thanks,

I get a syntax error.

Me.cboFeatureList.RowSource = "SELECT Feature.Fea_No, Feature.Fea_Name, "
Feature.Cat_No FROM Feature WHERE Feature.Cat_No = " & Me.cboCategoryList & "
ORDER BY Feature.Fea_Name"

What am I missing?

Dovberman

ASKER

This SQL runs.

SELECT Feature.Fea_No, Feature.Fea_Name,
Feature.Cat_No FROM Feature WHERE Feature.Cat_No = 1
ORDER BY Feature.Fea_Name ;

-- I hard coded Me.cboCategoryList as 1.

Dovberman

ASKER

the combo boxes behave as expected if I do not append strings.

Entered as a single line of code.
Me.cboFeatureList.RowSource = "SELECT Fea_No, Fea_Name, Cat_No FROM Feature " & "WHERE Cat_No = " & Me.cboCategoryList & " ORDER BY Feature.Fea_Name"

What is happening?

I would like to make the statement more readable by using multiple lines.

Dovberman

ASKER

Private Sub cmdGetSyntax_Click()
Me.txtSyntax.Text = "abc" & " def"
& "kyg" & " "
End Sub

Error: Expected line number, or end of statement

Private Sub cmdGetSyntax_Click()
Me.txtSyntax.Text = "abc" & " def" & "kyg"

End Sub

Compiled without error.

Any ideas?

I will try this on my MS access installation at home.

Thanks,

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

or

Me.cboFeatureList.RowSource = "SELECT Fea_No, Fea_Name, Cat_No FROM Feature" _
& " WHERE Cat_No = " & Me.cboCategoryList & " _
& " ORDER BY Feature.Fea_Name"

Dovberman

ASKER

You are right. I also forgot about the VBA line return character.

Thanks,

Dovberman

ASKER

Thank you.