Solved

MS Access VBA Syntax for Combo Box After Update Event

Posted on 2014-09-11
9
1,066 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 119

Expert Comment

by:Rey Obrero
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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now