Forum Discussion
Error in cascading Combo Boxes
Hi Access community,
In a form, I'd like to filter choices in combo box cobStatus based on the value I select in combo box cobCountry. The code I copied from an online post is:
Private Sub cobCountry_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [Tables]![Project Database].[Project Stage]" & _
"FROM [Tables]![Project Database]" & _
"WHERE [Tables]![Project Database].Country = " & Me.cobCountry & _
"ORDER BY [Tables]![Project Database].[Project Stage];"
Me.cobStatus.RowSource = strSQL
Me.cobStatus.Requery
End Sub
So, I select a country in cobCountry but, when selecting cobStatus an alert pops us:
'Syntax error in FROM clause'
As I said, I copied the code from a demo database a user shared online and in his demo database the code works. The only difference btw my DB and his is that he's pulling values from two separate tables (tblCountry and tblStatus), whereas I am pulling data from a single table. May that be the issue?
Thanks for any help you can provide!
Hi XPS35, thank you for your help, I think I'm on the right path!
cobStatus now presents only the choices in the 'Status' field of the data source that are available for the selected country! However, this happens when, after selecting the country I go and try to select the Status:
The system asks me to re-enter the country manually.
1. How do I eliminate that error? and
2. How can I filter the two other combo boxes based on choices made in the first two? What would the After Update event look like for Concept and Project Type?
Thank you so much for your help!
10 Replies
Hi arnel_gp,
Tried your script and got a bunch of errors, probably due to syntax issues. Unfortunately, with my very limited VBA skills, I can't figure out what's wrong with it and make corrections. The script you provided me with is as follows:
Private Sub cobCountry_AfterUpdate() Call subUpdateRowSource() End Sub Private sub cobStatus_AfterUpdate() call subUpdateRowSource() End Sub Private Sub subUpdateRowSource() Dim strSQL As String Dim strWhere As String strSQL = "SELECT [Project Database].[Project Stage] " & _ "FROM [Project Database]" If Me.cobCountry.ListIndex > -1 Then strWhere = strWhere & "[Country] = '" & Me.[cobCountry] & "' And " End If If Me.cobStatus.ListIndex > -1 Then strWhere = strWhere & "[Status] = '" & Me.[cobStatus] & "' And " End If If Len(strWhere)<>0 Then strWhere=Left$(strWhere, Len(strWhere)- 5) strSQL=strSQL & " WHERE " & strWhere End If Me.cobStatus.RowSource = strSQL End Sub
Which you said I should add to the form... What do you mean by that? Assign it to a button or to each combo box?
I realize this is way above my skill level and may require a lot of back and forth. I understand if you decline to respond. Thank you for your help so far!
Thank you arnel_gp,
I just started using some VBA in Access recently, so I'm not really sure of how to use certain terms.
I used [Table]! because there are also a report and a query called 'Project Database' in the file.
Both Status and Country come from the same table. Here's what I'm trying to achieve:
Thank you for your help!
As Country is a text field you need
"WHERE [Project Database].Country = '" & Me.cobCountry & "'" & _
Also put a Debug.Print strSQL after the strSQL =........... (and press Ctrl-g after the error occurred).You will notice some spaces are missing.
