Form/Subform combo box dependent combo box

[Piperlynne]

I have a form - frmCustomer
that I added a subform frmBrand to to pull up brand information if the
customer name field matches. There can be many brands per customer. I want to
be able to make a combo box of ONLY those brands that pull up.(instead of
using the record navigation 1 of 6 etc)
frmCustomer is based off tblCustomer that has CustomerName as a field
frmBrand (subform) is based off tblBrand that has CustomerName as a field.
The user should be able to select a customer then use the CustomerBrand
dropdown on the subformBrand to select those brands associated with the
customer.

Tell me there's a really easy solution that I'm completely overlooking.

 

[Klatuu]

I personally don't think what you are doing is a good idea and is a bit more
involved than you might think. What you did not say was what you want to do
once you have selected a brand?

The basic concept would be to put a combo box on the main form that would
use a query on the brand table that will present a list of brands for the
customer. You would filter the query based on the current customer.

Now, once you select a brand, what do you want to do?

 

[Piperlynne]

Ok, I'm not sure of the best simplest way to do this:
The USER wants to be able to select the customer, then get a list of brands
based on that customer. Then she wants to be able to update Project
information under and associate it to the brand and customer. She wants this
to be on "one form" for data entry. - i.e. she doesn't want to have to window
between forms.
I'm trying to find the simplest, most user friendly way to do this.
ANY suggestions you have are most welcome.
tblCustomer
tblBrand
tblProject

 

[Klatuu]

Okay, now your question makes more sense.
Use the main form to display the customer and brand information. Use a
combo like I described previously.
Use a subform for the project info.
Since your user wants it all on "one form", you can make your subform a
single form view or a continuous form, but you may run into enough room on
the form problems to do everything you want.

If the user will accept it, you can make a detail form open from your
subform when you select a project from the list.

 

[Piperlynne]

Thank you!
I have the combos for customer and brand working great now on the main form.
Now I'm running into getting the Project subform to stay referential to the
brand.
It keeps displaying everything and not the specific project.
I think it might be because I'm basing it off of the combo box instead of a
text field.
Should I populate a text field with the value of the brand combo and tie the
project subform to that or some other way.

 

[Klatuu]

you should be able to control that with Link Master and Link Child fields of
the subform control on the main form. This is how you typically control what
is included in the subform's recordset.

 

[Piperlynne]

Yep, got it after I posted this LOL.
I do that alot . . ask a question, then answer it myself. So here's another
try.
I linked it - master/child. And it works. . on the first change. But how do
I get it to requery. I have the customer/brand fields set up to do that, but
they're on the main form. (They work). I'm not sure how to set up the subform
to go back and check if the brand has changed "automagically" ;-)

 

[Klatuu]

You shouldn't have to do anything. When the linked values in the Link Master
fields change, the subform will requery automatically to match the subform
records with the main form.