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