Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Lookup Query to limit selections on a pulldown

Discussion in 'Information Technology' started by Christian >, Jul 28, 2006.

  1. Christian >

    Christian >
    Expand Collapse
    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!
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "Christian ><>" <Christian@discussions.microsoft.com> wrote in message
    news:BE8ADDCD-7053-4F47-8C09-8C949546E2B5@microsoft.com...
    >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!
     
  4. Christian >

    Christian >
    Expand Collapse
    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).

    "Jeff Boyce" wrote:

    > 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
    >
    >
    > "Christian ><>" <Christian@discussions.microsoft.com> wrote in message
    > news:BE8ADDCD-7053-4F47-8C09-8C949546E2B5@microsoft.com...
    > >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!

    >
    >
    >
     
  5. Christian >

    Christian >
    Expand Collapse
    Guest

    Nevermind, I'm a moron! Thanks!

    "Jeff Boyce" wrote:

    > 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
    >
    >
    > "Christian ><>" <Christian@discussions.microsoft.com> wrote in message
    > news:BE8ADDCD-7053-4F47-8C09-8C949546E2B5@microsoft.com...
    > >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!

    >
    >
    >
     
  6. Christian >

    Christian >
    Expand Collapse
    Guest

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

    "Jeff Boyce" wrote:

    > 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
    >
    >
    > "Christian ><>" <Christian@discussions.microsoft.com> wrote in message
    > news:BE8ADDCD-7053-4F47-8C09-8C949546E2B5@microsoft.com...
    > >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!

    >
    >
    >
     
  7. Christian >

    Christian >
    Expand Collapse
    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...

    "Christian ><>" wrote:

    > 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!
     
  8. Jeff Boyce

    Jeff Boyce
    Expand Collapse
    Guest

    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


    "Christian ><>" <Christian@discussions.microsoft.com> wrote in message
    news:7A46D7A3-943A-46AF-B21D-BB4DFE97EBFF@microsoft.com...
    > 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...
    >
    > "Christian ><>" wrote:
    >
    >> 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!
     

Share This Page