Solved

MS Access VBA Syntax for Combo Box After Update Event

Posted on 2014-09-11
9
1,096 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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