Solved

MS Access VBA Syntax for Combo Box After Update Event

Posted on 2014-09-11
9
1,118 Views
Last Modified: 2014-09-11
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
0
Comment
Question by:Dovberman
  • 6
  • 3
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40317943
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.
0
 

Author Comment

by:Dovberman
ID: 40317968
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?
0
 

Author Comment

by:Dovberman
ID: 40317979
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Dovberman
ID: 40317996
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.
0
 

Author Comment

by:Dovberman
ID: 40318014
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,
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40318160
try this

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40318162
or

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

Author Comment

by:Dovberman
ID: 40318273
You are right. I also forgot about the VBA line return character.

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 40318274
Thank you.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question