Triple cascading combo boxes

[leedub]

Working in MS Access 2007.

Hello, So Im creating a small database for a budget and Im trying to create 3 cascading combo boxes on a form (areabox2 , devbox2, entitybox2) . I have it right now so that that

devbox2 is based on areabox2
and
entitybox2 is based on devbox2
BUT!!!!!!!
I want entitybox2 to be based on both areabox2 and devbox2

Parameters are

Table = Budget Info
areabox2 data = [Budget Info].[Project Area]
devbox2 data = [Budget Info].Development
entitybox2 data = [Budget Info].Entity


This this the code im currently using

Private Sub areabox2_AfterUpdate()

devbox2.RowSource = "Select DISTINCT [Budget Info].Development " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Project Area] = '" & areabox2.Value & "' " & _
"ORDER BY [Budget Info].[Development];"
Me.devbox2.Requery

End Sub


Private Sub devbox2_AfterUpdate()

entitybox2.RowSource = "Select DISTINCT [Budget Info].Entity " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Development] = '" & devbox2.Value & "' " & _
"ORDER BY [Budget Info].[Entity];"
Me.entitybox2.Requery

End Sub



Im thinking that i have to add a second WHERE clause to the devbox2_AfterUpdate() but i dont know how???

Thanks in advance for any help.

 

[pbaldy]

You don't add a second WHERE clause, you add to the WHERE clause. The finished string would look like:

WHERE Field1 = 'ABC' AND Field2 = 'DEF'

presuming both fields are text. Your code would be built to achieve that end result.

 

[leedub]

Both text are fields. So i have change the WHERE line slightly but now nothing is populating the entitybox2

is my punctuation correct?

Private Sub areabox2_AfterUpdate()

devbox2.RowSource = "Select DISTINCT [Budget Info].Development " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Project Area] = '" & areabox2.Value & "' " & _
"ORDER BY [Budget Info].[Development];"
Me.devbox2.Requery

End Sub


Private Sub devbox2_AfterUpdate()

entitybox2.RowSource = "Select DISTINCT [Budget Info].Entity " & _
"FROM [Budget Info] " & _
"WHERE [Budget Info].[Development] = '" & devbox2.Value & "' AND [Budget Info].[Project Area] = 'areabox2.value' " & _
"ORDER BY [Budget Info].[Entity];"
Me.entitybox2.Requery

End Sub

 

[pbaldy]

Close; you didn't concatenate the value of areabox2 into the string like you did for devbox2.

 

[leedub]

I dont know what that looks like or what concatenate means??
Could write that line to show me what that looks like?

 

[pbaldy]

It's fine to copy/paste code, but you need to learn what it's doing. It should look like you did for the other form reference:

"WHERE [Budget Info].[Development] = '" & devbox2.Value & "' AND [Budget Info].[Project Area] = '" & areabox2.value & "' " & _

 

[leedub]

thanks that works perfect

 

[pbaldy]

No problem.