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]

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!cboDepartmen t.Requery to Me!cboSite.Requ ery 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!cboDepartmen t.Requery to Me!cboSite.Requ ery 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