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

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

ok thanks I will try this

Reply | Quote

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

Reply | Quote

See Synchronizing Combo Boxes on Forms in Access 2007.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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]

Reply | Quote

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?

Reply | Quote

That’s correct.

Reply | Quote

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?

Reply | Quote

Perhaps you should explain in detail what you’re trying to do…

Reply | Quote

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

Reply | Quote

Since a company can have multiple reps (I assume ), 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.

Reply | Quote

Ok so what I gather from this is I need another Table called Company that lists the Company Names individually.
I can then have a relationship with tblContact for getting the Companys information
Correct?

Reply | Quote

Yes, the way I described in my previous reply.

Reply | Quote

Ok thank-you. So I have setup my Tables this way. as for the SQL statment behind combobox1:
Option Compare Database

Private Sub cboCompany_AfterUpdate()
‘ Update the row source of the cboProducts combo box
‘ when the user makes a selection in the cboCategories
‘ combo box.
Me.cboCompanyRep.RowSource = “SELECT [Company Rep] FROM” & _
” tblContact WHERE CompanyID = ” & _
Me.cboCompany & _
” ORDER BY [Company Rep]”

Me.cboCompanyRep = Me.cboCompanyRep.ItemData(0)
End Sub

This doesnt seem to work anysuggestions?

Reply | Quote

Could you post a stripped down copy of your database? See post 401925 for instructions.

Note: the database should be an .mdb, not an .accdb file, most Loungers (including me) don’t have Access 2007 yet, so they can’t open an .accdb.

Reply | Quote

Thanks

Reply | Quote

You should pay attention to the details, you had fields of the wrong type and superfluous fields. Please study the attached version of your database carefully.

Reply | Quote