Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Setting up a combox using a table for list (Access 07)

    I am developing a user form on a access data base and would like to to have a combo box with a list of things to be picked. I can set the list up with in the properties, but I was wondering how I could use a table and the combo box would just reference the table. Any tips on how this can be done?

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

    Re: Setting up a combox using a table for list (Access 07)

    You can set the Row Source property of the combo box to the name of the table. I don't think it gets simpler than that. What exactly do you want?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    ok thanks I will try this

  4. #4
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    What I am exactly after is setting the row source to a table, called customer and I query all in the field called Company. The next combo box is where I am having trouble. I would like to query only Company Reps names from Customer table where the company name is = to the company selected in the first combo box.

    Hopefully this makes sense.

    Thanks Ahead

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

    Re: Setting up a combox using a table for list (Ac


  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    Thank you for the link.
    I added the SELECT [tblContact].ID, [tblContact].Company FROM tblContact ORDER BY [Company]; statment to combo box cboCompany and when the form is ran it shows the primary key values. is there a way to offset one more column?

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

    Re: Setting up a combox using a table for list (Ac

    Set the following properties:
    Column Count: 2
    Column Widths: 0";1"

    Setting the width of the first column effectively hides it, so that the second column is shown.

  8. #8
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    Another issue I seem to be having is with my code stopping on Me.cboCompanyRep.RowSource = "SELECT Company Rep FROM" & _
    " tblContact WHERE Company = " & _
    Me.cboCompany & _ < error grabing the value from the combobox
    saying Compile error:
    Method or data member not found

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

    Re: Setting up a combox using a table for list (Ac

    You should replace cboCompany with the exact name of the combo box on your form. If the name contains spaces or punctuation, place square brackets around the name: Me.[Combo Name]

  10. #10
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    Thank you for the tip. I am almost of fto the races having the event procedure not stop on anything.

    although nothing is showing up in the 2nd combobox I goto the vb scipt editor an stop the code to view the varibles and it seems to be grabbing the primay key value from the first combobox pick?

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

    Re: Setting up a combox using a table for list (Ac

    That's correct.

  12. #12
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    I think my problem is when following the instructions on the MSDN link you have attached to the post. I do not know how to do the step:

    An Access 2007 database exists which contains two tables, tblCategories and tblProducts. The Category field in tblProducts is a lookup column that is linked to the Category field in the tblCategories table.

    Does this matter?

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

    Re: Setting up a combox using a table for list (Ac

    Perhaps you should explain in detail what you're trying to do...

  14. #14
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting up a combox using a table for list (Ac

    Sure,
    What I have setup is Two Tables,
    tblContact
    tblRequest_Contact

    tblContact Is a table that Stores customer details ex. Company, Company Rep, Street Address ect. for lists

    TblRequest_Contact is a table that will store jobs.

    I have created a Job fill out form that so far has 2 comboboxes and a text box.
    Combobox 1 is for picking a Company, which I want connect to the tblContact Company field to get a list and only List the unique companys so no multiples. The use will pick a company from the list and populate the Company field in tblRequest_Contact (Currently I have this working)
    Combobox2 is a Company Rep, Which I want to syncronize with the company combobox, so which ever company is picked then the list will show only customer reps belonging to the Company pick in the combobox list. I believe a query is needed that looks at the Combobox1 value and select only company reps that belong to that company.
    The Textbox is for An Address which again is a query, that reports multiple fields from the tblContact
    Street Address, Postal Code, Province, Country

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

    Re: Setting up a combox using a table for list (Ac

    Since a company can have multiple reps (I assume <img src=/S/grin.gif border=0 alt=grin width=15 height=15>), you should have a table tblCompany that lists the companies. Each company will have a unique CompanyID (autonumber), plus a CompanyName and other information relevant to the company.
    The table tblContact contains a unique ContactID (autonumber), plus CompanyID (number, long integer) to link to tblCompany, plus ContactName and other information relevant to the contact.
    The first combo box will have a query based on tblCompany as Row Source, and the second combo box will have a query based on tblContact as Row Source.
    Both combo boxes have two columns, of which the first (which contains the unique key) will be hidden because its column width is set to 0. So the user selects a name, but the value of the combo box will be the corresponding ID.
    When the user selects a company in the first combo box, the After Update event procedure changes the SQL of the Row Source of the second combo box to return only contacts whose CompanyID equals the value of the first combo box.

Page 1 of 2 12 LastLast

Posting Permissions

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