Combo Box with Query Results Issue

[dascoli]

I have two combo boxes in a subform named "sfrmReferDetail". The first is based on a table named "Agency". The table has one field "AgencyName". This field is a text field that is indexed without duplicates. The second is a combo box that is based on a query that is based on a table called "Agency Programs". The "Agency Programs" table has multiple fields with the primary key being an auto-number, and a foreign key being "AgencyName".

I need the second combo box to populate the Agency Programs that correspond with the Agency selected in the first combo box.



I've tried using the criteria of the query to restrict the contents of the second combo box to only the selected Agency, but when I click the second combo box I get a window prompting manual entry of the Agency's Name.

This is what was in the criteria:
Forms![sfrmReferDetail]![cboAgency]

I'm not sure if this isn't working because the Agency Name is a string. Any help that you guys can give me is greatly appreciated!

[Ruegen]

Have you tried requiring the row source using VBA of the second combo box after selecting results from the first combo box - using an after update event.

You have to requery the list in the second with the new selection from the first and it won't do it by itself.

i.e.

sub combo1_afterupdate()

me.combo2.requery

end sub

[Ruegen]

I'm not sure if this isn't working because the Agency Name is a string. Any help that you guys can give me is greatly appreciated!

both tables have to have an ID number of the same to make that possible.

However you can make a query search a string that matches another string as criteria -

Like "*" & "string" & "*"

this is much ​slower and not reccommended

also when making the row source of the combo boxes make sure they key id values are the first column. Open the row source query using the little icon to the right of the property field in the property panel. add the table you want, then pull only the field you need (ID/foreignkey and then name/description field)

this way both row source queries will have two columns and the first being the number field for each

make sure you have two columns with a column width for each (property of combo box)

I tend to make the first field 0; width so that I don't see it then the second that displays the name/description 6; so you see that in the combo drop down action

[June7]

Ruegen, I disagree that both tables need ID primary/foreign key. Tables can link on text fields,

Here is a tutorial on dependent comboboxes. http://www.datapigtechnologies.com/f...combobox2.html

If both comboboxes are on the same form, the second combobox sql WHERE criteria can simply be: AgencyName = [cboAgency]

Be aware that dependent comboboxes that use lookup alias (as Ruegen describes) will not work nice with form in Continuous or Datasheet view. However, since you are not using an ID primary/foreign key for Agency you should not have an issue.

[Ruegen]

Ruegen, I disagree that both tables need ID primary/foreign key. Tables can link on text fields,

Here is a tutorial on dependent comboboxes. http://www.datapigtechnologies.com/f...combobox2.html

If both comboboxes are on the same form, the second combobox sql WHERE criteria can simply be: AgencyName = [cboAgency]

Be aware that dependent comboboxes that use lookup alias (as Ruegen describes) will not work nice with form in Continuous or Datasheet view. However, since you are not using an ID primary/foreign key for Agency you should not have an issue.

That's what I meant - both combo boxes. What did I type? *looks*

Go with what June7 says regardless - he's right.

[dascoli]

Thank you very much guys! When I get to work this morning I'll make these changes and report back with the results. This forum is the best around!

[dascoli]

Adding the Where = [cboAgency] to the Criteria in the query builder does work. But June7, you are right, this does not play nice in datasheet view. It works great for the first row. Updates as it should and re-runs the query. The problem is when I go to the second row in the subforfm's datasheet. When I change the first combo box (cboAgency) it forces the second combo box (cboPrograms) to update in all rows of the datasheet with the program information from the first Agency.

So now I'm wondering if there is a way to prevent this from happening.

[June7]

There is not. Lookups with alias and Continuous/Datasheet just won't work.

[dascoli]

I figured there wouldn't be after Googling cascading combo boxes in datasheet/continuous form view. The solutions were extremely convoluted and mostly beyond my scope of knowledge. I ended up just having a field in the query builder that concatenated the Agency Name and Program Name and displays all the records. It's going to be a much longer list for the user to scroll through, but that's the way it's going to be. They can suck it up! Lol.

Thanks again June7! You always seem to be the one that answers my questions, so I don't know what I would do without you! Have a great day!

[June7]

Set combobox AutoExpand property to yes and that might make users less aggravated.