?
Solved

MS Access VBA Syntax for Combo Box After Update Event

Posted on 2014-09-11
9
Medium Priority
?
1,339 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

578 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