combo boxes and links 1

[pmkieffe]

Hi,
I hope this is just an easy quick question. Can a combo box in a form have a table control source different than the table source of the form itself?

I want the form to be linked to table tblEmployeeInfo, but then select data from other tables via comboboxes. A recordset on a command button then populates a different table (tblTimeCard) with the selected data in all of the combo boxes. The reason I want the form tied to tblEmployeeInfo is because I want a subform with a master-child link to the form based on EmployeeID which is in tblEmployeeInfo.

Thanks for any help,
pmkieffe

 

[Eupher]

Absolutely. This is one of the most common uses of the combobox.

Ken S.

 

[pmkieffe]

Could you please guide me on how to accomplish this? When I click on the properties of the combobox it only gives me the fields of the form control source table as possible control sources for the combobox. I would like to use fields from other tables.

Thanks,
pmkieffe

 

[Eupher]

Sure.

1) Set "Row Source Type" to "Table/Query"
2) Put the cursor in the "Control Source" field, then click the button with 3 ellipsis points next to it to open the query design grid. Then build a query that retrieves the data you want for your combo.
3) Edit the "Column Count," "Column Widths," and "Bound Column" properties as appropriate. Column Count determines how many columns are included in the combo - this should match the number of columns returned by your query. If you want any of the columns to not be viewed, set their column widths to 0. This is commonly done to hide an autonumber or other key field. So, for example, if your combo query includes 3 columns, EmpID, LastName, Firstname, and EmpID is an autonumber that you don't want to display, set your column widths to 0;1";1 (or whatever width you prefer, experiment to see what works for you)
4) The bound column property is critical as this is the value that's actually held in the combo... BUT ...you can refer to the values of any of the columns, even hidden ones, using the .Column property in VBA code.

HTH,

Ken S.