Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Combo Box Source (2000 - SR2)

    I have a tabbed form whereby I have the primary Customer information listed on the first tab, and the use subforms on the subsequent other 4 tabs. The common denominator on all forms is a filed identified as [CustomerID]. On the second tab, is where I can enter multiple 'Contact' persons information (i.e. name, phone, etc.). So for each Customer Form, there is 1 or more Contact Persons information as well. Most contain between 2-5 Contacts Persons Information. On tab 3 & 4, I use a combo box to select the appropriate [ContactName] for the relevant information. It is being fed from an underlying query that uses both the "CustomerInfo" and "CustomerContact" tables, with a relationship link between the common [CustomerID] fields. I need to the Combo Box to only provide the related Contact Info for the apprpriate Customer Info. So when I move to the next form record (Next Customer), the combo box will re-load with the Contact Persons Name related to that Customer, and so on. I have tried to set a "Requery Command" on the combo Box, as well as the related "On Open" and "On Activate" properties of the sub-form, but cannot get it to show just the related Contact Names. The combo box continues to show all 500+ names.

    Needless to say I'm kind of stumped on this one. As always, thanks in advance for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Source (2000 - SR2)

    The Row Source of the combo box must be a query or SQL statement with a restriction on CustomerID: [Forms]![Name_of_Form]![CustomerID] in the criteria line, or

    ... WHERE CustomerInfo.CustomerID = [Forms]![Name_of_Form]![CustomerID]

    in the SQL statement. Substitute the actual name of the form.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box Source (2000 - SR2)

    I placed [Forms]![nfrmCustomerInfo]![CustomerID] in the criteria line, under the [CustomerID] field, and then set up a new combo box based on the [CustomerID] and [ContactName] fields. The Combo box works, but only shows the Contact Names for the first CustomerInfo record. When I scroll to the next CustomerInfo record, the Contact Name combo box still only shows the Contact Names for the first Customer Information. Do I need to do a requery on the combo box some how?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Combo Box Source (2000 - SR2)

    Yes, you must requery the combo box in the On Current event of the form:

    Private Sub Form_Current()
    ComboBoxName.Requery
    End Sub

    Replace ComboBoxName with the actual name.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Combo Box Source (2000 - SR2)

    I think this is the second time in the last month that you've pointed me in the right direction Hans. Thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •