Re: Cascading Combo boxes

[jonosborne]

hi, i hope someone can help, i have read a lot of tutorials relating to this matter but im afraid VBA isnt my strong point and i just need a touch of guidance.

I have one table called [tbl Site Info] which has three fields [Site], [Department] and [Manager]. On a form i have three combo boxes which show data from each field on the table along with an extra 'All Records'. The following code enabling this is in the 'Row Source' of each combo box:

[SELECT [tbl Site Info].[Site] AS Filter, [Site] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Site] FROM [tbl Site Info];]

In conjunction with this, i need/would like the option of limiting the data in the combo boxes based on the selection of the last, i.e. if i select 'UK' from the cboSite box then it will only give me the Departments from the UK in the cboDept box and similarly when i select a department then the cboMgr box will only show the managers associated with that department.

However i need to keep the 'All Records' option as i will need to show all departments in UK, etc...

I tried using the following code in the row source of the cboDept box:

[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info] WHERE [tbl Site Info].[Site]=Forms![frm Main copy]!cboSite;]

which i believed would only show the departments related to the site, but it did'nt.

Any help would be greatly appreciated,

[puppydogbuddy]

hi, i hope someone can help, i have read a lot of tutorials relating to this matter but im afraid VBA isnt my strong point and i just need a touch of guidance.

I have one table called [tbl Site Info] which has three fields [Site], [Department] and [Manager]. On a form i have three combo boxes which show data from each field on the table along with an extra 'All Records'. The following code enabling this is in the 'Row Source' of each combo box:

[SELECT [tbl Site Info].[Site] AS Filter, [Site] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Site] FROM [tbl Site Info];]

In conjunction with this, i need/would like the option of limiting the data in the combo boxes based on the selection of the last, i.e. if i select 'UK' from the cboSite box then it will only give me the Departments from the UK in the cboDept box and similarly when i select a department then the cboMgr box will only show the managers associated with that department.

However i need to keep the 'All Records' option as i will need to show all departments in UK, etc...

I tried using the following code in the row source of the cboDept box:

[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info] WHERE [tbl Site Info].[Site]=Forms![frm Main copy]!cboSite;]

which i believed would only show the departments related to the site, but it did'nt.

Any help would be greatly appreciated,

Hi Jon,
You are much closer to what you want then you realize.

All you needed to make the Department combobox work was an afterUpdate event for cboSite that will requery the Department list. In other words, if you want the Department combobox list to reflect only the Departments for the site(s) selected in cboSite, you need to requery the Department list. To do that, place the following code in the afterUpdate event of cboSite.

Private Sub cboSite_AfterUpdate()
 
Me!cboDept.Requery
 
End Sub
 

Similarly, if you make a selection of a department, you need to requery the managers list to update the managers list based on the department selected.

Private Sub cboDept_AfterUpdate()
 
Me!cboMgr.Requery
 
End Sub
 

[jonosborne]

Thanks Puppydogbuddy,

When i put the code into the 'after_update' of cboSite, there is no difference in the outcome of the cboDept box (i.e. all the results are shown, not just the ones related to that site) . I have tried removing the Row Source details from cboDept but that does not give me any entries.

Is there anything that i am missing?.. if i remove the row source then i wont have the option of 'All Records' which is necessary. Is it possible to have both??

If there is anything you (or anyone else) can suggest then i am open to all ideas, if not then i will keep the boxes as they are.

Again, i do appreciate the help.

[puppydogbuddy]

Jon,
Be patient. We will get there. In the meantime, can you ry the code below in the after update event of cboSite. Just tell me what happens that is different than before. Thanks.

Private Sub cboSite_AfterUpdate()
cboDept.RowSource = cboSite.RowSource
Me!cboDept.Requery
End Sub
 

[puppydogbuddy]

Hi Jon,
I see what the problem is now. For cboDept, you should have the following code as the row source:

[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info];]

and the following code in the after update of cboSite:

Private Sub cboSite_AfterUpdate()
cboDepartment.RowSource = “[SELECT [tbl Site Info].[Department] AS Filter, [Department] FROM [tbl Site Info] UNION SELECT "*" AS Filter, "All Records" AS [Department] FROM [tbl Site Info] WHERE [tbl Site Info].[Site]=Forms![frm Main copy]!cboSite;]”
 
Me!cboDept.Requery
 
End Sub
 

[jonosborne]

Hi,

I used the code from your second post and after selecting from cboSite, cboDept then mirrored the information from cboSite.

Now, using the code from your last reply (i amended cboDepartment to cboDept), there seems to be a syntax error in line 2.

[puppydogbuddy]

Hi,

I used the code from your second post and after selecting from cboSite, cboDept then mirrored the information from cboSite.

Now, using the code from your last reply (i amended cboDepartment to cboDept), there seems to be a syntax error in line 2.

Ok, jon. It may take a couple of tries to get the syntax correct, but we are making progress. I assuming that Site is a text data type. If it isn't, I will need to correct the syntax accordingly. Also, I am trying single quotes around * . If that does not work, I will have to replace the single quotes with the ascii equivalent for double quotes.

Private Sub cboSite_AfterUpdate()
Dim strSQL As String
 
 
strSQL = “SELECT [tbl Site Info].[Department] AS Filter, [Department]“
strSQL = strSQL & “ FROM [tbl Site Info]”
strSQL = strSQL & “ UNION SELECT ‘*’ AS Filter, ‘All Records’ AS [Department]”
strSQL = strSQL & “ FROM [tbl Site Info]”
strSQL = strSQL & “ WHERE [tbl Site Info].[Site] = ‘” & Forms![frm Main copy]!cboSite & “’” & “;”
 
cboDept.RowSource = strSQL
 
Me!cboDept.Requery
 
End Sub

[jonosborne]

Site is a text data type, however when pasting the code lines 5-9 are highlighted in red and when selecting the first combo box, once i click on one of the items the same 'Syntax Error' msg box is shown and line 5 is highlighted.

[puppydogbuddy]

Site is a text data type, however when pasting the code lines 5-9 are highlighted in red and when selecting the first combo box, once i click on one of the items the same 'Syntax Error' msg box is shown and line 5 is highlighted.

Ok, Jon.

Please change line 7 from this:

strSQL = strSQL & “ UNION SELECT ‘*’ AS Filter, ‘All Records’ AS [Department]”
 
' to:
strSQL = strSQL & “ UNION SELECT “ & Chr(34) & * & Chr(34) & “ AS Filter, ‘All Records’ AS [Department]”
 

If you still get a syntax error, I need you to hit CTRL + G which will take you to the debug window. The debug window will appear at the bottom of the code window with a carot prompt. At the prompt I need you to type as shown below:
>? strSQL

Access will respond by displaying its interpretation of the SQL syntax that was used. Take a look at it and see if you spot where the syntax was not interpreted correctly. If you can't see anything, copy the string that was displayed and post it here so that I can see it..

[jonosborne]

Ok, Jon.

Please change line 7 from this:

strSQL = strSQL & “ UNION SELECT ‘*’ AS Filter, ‘All Records’ AS [Department]”
 
' to:
strSQL = strSQL & “ UNION SELECT “ & Chr(34) & * & Chr(34) & “ AS Filter, ‘All Records’ AS [Department]”
 

If you still get a syntax error, I need you to hit CTRL + G which will take you to the debug window. The debug window will appear at the bottom of the code window with a carot prompt. At the prompt I need you to type as shown below:
>? strSQL

Access will respond by displaying its interpretation of the SQL syntax that was used. Take a look at it and see if you spot where the syntax was not interpreted correctly. If you can't see anything, copy the string that was displayed and post it here so that I can see it..

Right, still coming up with a Syntax error. When i goto the debug window and type >?strSQL a message box appears with the words

Compile Error:
Expected: line number or label or statement or end of statement

but nothing else is displayed.

Where am i going wrong?
This is the code:

Private Sub cboSite_AfterUpdate()
Dim strSQL As String
 
 
strSQL = “SELECT [tbl Site Info].[Department] AS Filter, [Department]“
strSQL = strSQL & “ FROM [tbl Site Info]”
strSQL = strSQL & “ UNION SELECT “ & Chr(34) & * & Chr(34) & “ AS Filter, ‘All Records’ AS [Department]”
strSQL = strSQL & “ FROM [tbl Site Info]”
strSQL = strSQL & “ WHERE [tbl Site Info].[Site] = ‘” & Forms![frm Main copy]!cboSite & “’” & “;”
 
cboDept.RowSource = strSQL
 
Me!cboDept.Requery
 
End Sub
 

[puppydogbuddy]

Jon,
I think the problem is with the syntax for the wildcard and the ending semi-colon. Try the syntax below for lines #7 and #9.

strSQL = strSQL & “ UNION SELECT “ & Chr(34) & “*” & Chr(34) & “ AS Filter, ‘All Records’ AS [Department]”

strSQL = strSQL & “ WHERE [tbl Site Info].[Site] = ‘” & Forms![frm Main copy]!cboSite & “’;”


If that doesn't get it, can you send me a copy of your mdb, with any sensitive info stripped out? My email is with my profile.

[jonosborne]

Jon,
I think the problem is with the syntax for the wildcard and the ending semi-colon. Try the syntax below for lines #7 and #9.

strSQL = strSQL & “ UNION SELECT “ & Chr(34) & “*” & Chr(34) & “ AS Filter, ‘All Records’ AS [Department]”

strSQL = strSQL & “ WHERE [tbl Site Info].[Site] = ‘” & Forms![frm Main copy]!cboSite & “’;”


If that doesn't get it, can you send me a copy of your mdb, with any sensitive info stripped out? My email is with my profile.

Ok, its almost started to work, at least the Syntax error has stopped but cboDept doesnt seem to be taking the value of cboSite. Im unable to get your e-mail address from your profile, can you enable access to it and ill forward you the mdb.

[puppydogbuddy]

Ok, its almost started to work, at least the Syntax error has stopped but cboDept doesnt seem to be taking the value of cboSite. Im unable to get your e-mail address from your profile, can you enable access to it and ill forward you the mdb.

Jon.
I enabled my email, but it is still inaccessable and I will have to find out why. I was able to download my vCard, which also has my email address on it, so you should be able to get it from my vCard.

Before you send me your file, try changing the line that says Me!cboDepartment.Requery to Me!cboSite.Requery and see what happens, just for kicks.

[jonosborne]

Ive sent you an email, thanks.

[MMcCarthy]

Jon.
I enabled my email, but it is still inaccessable and I will have to find out why. I was able to download my vCard, which also has my email address on it, so you should be able to get it from my vCard.

Before you send me your file, try changing the line that says Me!cboDepartment.Requery to Me!cboSite.Requery and see what happens, just for kicks.

Hi pdog

The email link doesn't work but the vcard should work fine.

Mary

[puppydogbuddy]

Hi pdog

The email link doesn't work but the vcard should work fine.

Mary

Thanks, Mary. The vcard link works fine. After I received the application in the email from Jon, I was able to diagnose and fix the problem with these comboboxes. The AfterUpdate event used a union query with a where clause...and the Where clause was placed after the Union Select statement. The Where clause in a Union Query has to be placed with the first select statement or it will not work properly. The correct syntax is shown below.

Private Sub cboSite_AfterUpdate()
 
strSQL = ""    'to initialize string variable
strSQL = strSQL & "SELECT Distinct Department AS Filter, Department"
strSQL = strSQL & " FROM [tbl Site Info]"
 strSQL = strSQL & " WHERE [tbl Site Info].[Site] = '" & Forms![frm Main copy]!cboSite.Value & "'" 
strSQL = strSQL & " UNION SELECT Distinct " & Chr(34) & "*" & Chr(34) & " AS Filter, 'All Records' AS Department"
strSQL = strSQL & " FROM [tbl Site Info]"
strSQL = strSQL & " Order By Department;"
 
cboDept.RowSource = strSQL
 
Me.Requery
 
End Sub