HELP: cascading drop down - need coding for 3rd dropdown

[shanice]

Hello all!

I'm working on creating 3 combo drop downs (cascading). I can get combo3 to work based on what's selected in combo1 but I want it to be based on whats selected in combo1 AND combo2. I'm currently using this coding to make the combo3 work:

Private Sub Program_Component_AfterUpdate()
Activity.RowSource = "Select Activity.Activity " & _
"FROM Activity " & _
"WHERE Activity.Domain = '" & Domain.Value & "' " & _
"ORDER BY Activity.Activity;"

End Sub

My question is how do I add an "AND" to the WHERE line so that it looks at what's selected in combo2 as well???

 

[vbaInet]

I'm hoping Activity isn't the name of your second combo box?

Activity.RowSource = "Select Activity " & _
            "FROM Activity " & _
            "WHERE Domain = '" & Domain.Value & "' [COLOR=Red]AND Domain2 = '" & Domain2.value &[/COLOR] "' "& _
            "ORDER BY Activity;"

Notice also I took off the "Activity." text from your select statement. If all you're doing is querying one table you don't need to prefix your field names by the table's name, so no need to write Activity.Domain for example.

 

[shanice]

OMG (been working on this for hours)! That worked!!! Thank you so much! One last question, what can i put in that will only pull the fields that apply to combo2? Right now I just have the following select statement in the row source field:

SELECT DISTINCT Activity.ProgramComponents FROM Activity ORDER BY Activity.ProgramComponents;

This is pulling the values but I want it to pull it by what's selected in combo1. So someone will come in and select a domain from combo1 and then combo 2 pulls up the components that apply and then combo3 pulls up the activities that fall under what was selected under combo1 and combo2. So everything is working except for combo2.

 

[shanice]

Ohh, and unlike combo3 they're a lot of duplicates in combo2 so I only want it to list each item once in the drop down.

 

[vbaInet]

Is there a field that links both combo boxes? Maybe an ID or something?

Here's a link from one of the site's VIPs
http://baldyweb.com/CascadingCombo.htm

 

[shanice]

Okay, I've finally got the database working the way I want except I have one (hopefully small) issue.

You know how you helped me get combo3 to generate a list based on combo1 and combo2? Well now there is a item from combo1 that has a subfield (partners) that none of the other items have. I want to write a code that generates another list in combo3 (activity) based on what is selected for combo1 (domain) and partners. I thought I could do this by changing up the code you gave me:

Private Sub Program_Component_AfterUpdate()
Activity.RowSource = "Select Activity " & _
"FROM Activity " & _
"WHERE Domain = '" & Domain.Value & "' AND ProgramComponents = '" & ProgramComponents.Value & "' " & _
"ORDER BY Activity;"

End Sub

By changing it to:

Private Sub Partners_AfterUpdate()

Activity.RowSource = "Select Activity " & _
"FROM Activity " & _
"WHERE Domain = '" & Domain.Value & "' AND Partner = '" & Partner.Value & "' " & _
"ORDER BY Activity;"
End Sub

But I get a "Run-time error '424' Object required" error message. Do you know what I'm doing wrong?

 

[vbaInet]

What line is it highlighting when the error comes up and you click DEBUG?

 

[shanice]

This whole area comes up highlighted:

Activity.RowSource = "Select Activity " & _
"FROM Activity " & _
"WHERE Domain = '" & Domain.Value & "' AND Partner = '" & Partner.Value & "' " & _
"ORDER BY Activity;"

When I pan over activity it says Activity=Null. Could it be because I'm using two similar codes?

Private Sub Partners_AfterUpdate()
Activity.RowSource = "Select Activity " & _
"FROM Activity " & _
"WHERE Domain = '" & Domain.Value & "' AND Partner = '" & Partner.Value & "' " & _
"ORDER BY Activity;"
End Sub

Private Sub Program_Component_AfterUpdate()
Activity.RowSource = "Select Activity " & _
"FROM Activity " & _
"WHERE Domain = '" & Domain.Value & "' AND ProgramComponents = '" & ProgramComponents.Value & "' " & _
"ORDER BY Activity;"

End Sub

 

[vbaInet]

Activity is not in the scope of where you're calling it from. Maybe you're using a subform now?

 

[shanice]

No it's the same form. I can't figure it out. I have the row source set as: SELECT DISTINCT Activity.Partner FROM Activity ORDER BY Activity.Partner;

 

[vbaInet]

Then check to make sure ProgramComponents is the correct name of your control.

 

[vbaInet]

Here's your culprit:

"WHERE Domain = '" & Domain.Value & "' AND Partner = '" & Partne[COLOR=Red]r.[/COLOR]Value & "' " & _

Highlighted in red.

It should be Partners.

 

[shanice]

You are the best!!! Thanks!

 

[vbaInet]

So now you know what that "Object Required" errors informs you about. Intellisense in access would tell you most of the methods and properties related to what you typed. So if you type "Partner." and you didn't see anything pop-up then you know that isn't an object. That whole block was highlighted because the error was part of the SELECT statement due to it being concatenated.

Have fun developing the rest of your db