Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating combo box options based on (Access 2003)

    Hi,

    I have a combo box (drop down list) on a form for users to select the company name that an individual works for when adding that individual's details. I would then like the user to select which office of that company the individual is based in again by picking from a combo box. How would i get this combo box to just show the office names of the company which has been selected rather than all office names (thousands!)?

    Thanks.

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

    Re: Creating combo box options based on (Access 2003)

    See How to synchronize two combo boxes on a form in Access 2002 or in Access 2003. This MSKB article describes a method to do what you want, and also contains a link to download a (free) sample database that illustrates this method, and much more.

  3. #3
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating combo box options based on (Access 2003)

    Hi,

    Have followed the instructions on the link. The code i have produced doesn't come up with any errors but when i select an item in the first combo box (CTBL business name), the second combo box ( CTBL Office Name) is just empty - not even showing all the records as when nothing is entered in the first combo.

    Here is the code i'm using:
    Private Sub CTBL_Business_Name_AfterUpdate()
    Me.CTBL_Office_Name.RowSource = "SELECT [BLTBL office name] FROM" & "[CTBL_Office_Name]where [bltbl business name]" & Me.[CTBL_Business_Name]
    Me.CTBL_Office_Name = Me.CTBL_Office_Name.ItemData(0)

    End Sub

    Any ideas?

    Thanks.

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

    Re: Creating combo box options based on (Access 2003)

    There are some things missing, such as an =. And if CTBL_Business_Name is a text field, you need quotes around the value:

    Me.CTBL_Office_Name.RowSource = "SELECT [BLTBL office name] FROM [CTBL_Office_Name] " & _
    "WHERE [bltbl business name] = " & Chr(34) & Me.[CTBL_Business_Name] & Chr(34)

    Chr(34) is the double quote character ".

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating combo box options based on (Access 2003)

    The first ampersand is not needed and you have forgoten the equal sign ath the end.
    This is in case [BLTBL office name] is a number.

    Me.CTBL_Office_Name.RowSource = "SELECT [BLTBL office name] FROM [CTBL_Office_Name] where [bltbl business name] =" & Me.[CTBL_Business_Name]

    If [BLTBL office name] is text then use :

    Me.CTBL_Office_Name.RowSource = "SELECT [BLTBL office name] FROM [CTBL_Office_Name] where [bltbl business name] =" & Chr(34) & Me.[CTBL_Business_Name] & Chr(34)
    Francois

  6. #6
    Lounger
    Join Date
    Jan 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating combo box options based on (Access 2003)

    Thanks.

    I have changed as suggested but it's now saying that the record source is not available. Have i put the right names in the right places? Which names of tables or fields go where i.e. SELECT ?, FROM ?, WHERE ? etc.

    Sorry - copied this from the link you gave me but just guessed what went where.

    Thanks again.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating combo box options based on (Access 2003)

    SELECT Field FROM Table WHERE Field ...
    Francois

Posting Permissions

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