Lookup Query to limit selections on a pulldown

[Guest]

I have three tables, one is the order table, one lists the insurance
companies, and the third lists the insurance contacts. The contacts table
has a pulldown list bound to the insurance companies. And on the order
table, there is a pulldown list to select the insurance company and one to
select the contact.

I created a query based on the instructions at
http://www.access.qbuilt.com/html/auto-complete_form.html and made the lookup
columns refer to it instead of the original tables...

When filling out the orders table, or the form that is bound to it, I want
to be able to select the insurance company, and then when using the pulldown
list for insurance contacts, I want it to be limited to the agents in the
company I just selected.

I've tried using the form field, the table field, and even the query field
as the criteria, but I either get nothing in the pulldown list or all of them
(in every company). HELP!

 

[Jeff Boyce]

Take a look for "Cascading Combo Boxes" in Access HELP, at mvps.org/access,
or via Google. That is the moniker for what you're describing...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff--Those are cool and I can use them elsewhere (I didn't even know
those existed). But's that not what I meant.

I have two separate fields. I want the data for the combo box in the second
field (Contacts) to be limited by what it entered into the first field
(Companies).

 

[Guest]

Nevermind, I'm a moron! Thanks!

Take a look for "Cascading Combo Boxes" in Access HELP, at mvps.org/access,
or via Google. That is the moniker for what you're describing...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

No. I am wrong. This still didn't help.

Take a look for "Cascading Combo Boxes" in Access HELP, at mvps.org/access,
or via Google. That is the moniker for what you're describing...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Okay, I found my own answer here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209576

I followed the example, but I don't understand all of it. I got the example
to work, but I don't know how to apply it to my own database...

 

[Jeff Boyce]

The generic approach is:
* create a query to fill the second combo box
* make that query a parameter query, using the field represented by the
first combo box
* make the parameter something like:
Forms!YourFormName!YourFirstComboBoxName
* add code to the AfterUpdate event of the FIRST combobox in your form,
something like:
Me.YourSecondComboBoxName.Requery

NOTE: If your underlying tables have some of these fields set to "lookup"
data types, you'll end up with a headache trying to untangle the way this
functions.

Regards

Jeff Boyce
Microsoft Office/Access MVP