Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combo Box that looks at 2 or more tables

Access Howto:

Combo Box that looks at 2 or more tables

by  DougP  Posted    (Edited  )
This FAQ shows the real power of a Combo box
Add a Combo box using the Wizard and choose one of the tables that it will get its data from.
After its finished then click its properties and click in the "Row Source" to open the QBE grid. This is a regular query and can have multiple tables.

Add the second table and create a Link between the foreign keys (the black line) Add more tables if needed
Note: The more records and tables the more time it takes when you first click the combo box for it to drop down.

Next add the appropriate fields belows that you want in you Combo box.

Then click the ôXö in the upper right to close it.
It will prompt to save so click ôYesö

Now in the properties of the Combo box itself you can add more columns in the column widths and column count.
The following 7 properties including and below the ôRow sourceö are what you are interested in to make it function correctly.

Property name What it does
--------------------------------------------
Row Source SQL statement to get data
Column Count # of columns showing when clicking the combo to open it
Column Heads ôYesö or ôNoö Show field headings (good if you have a lots of columns)
Column Widths The width of each field separated by semi colons 1ö; 2ö; 1.5ö
Bound Column The column that returns the data once the item is picked . 1 is the first column
List Rows number of rows to drop down 8 is the default
List width Width of the whole combo box when it drops down in inches

Note you can hide a column by making its width 0ö and when you hide it, it does not have to count in the ôColumn Countö Property.

The Combo box is extremely powerful and can do
The value that shows in the Combo box always has to be the first column
So you can move the columns around in the QBE grid.

It may take some time to get the widths correct and everythign set right. Bu the time spent here will multiply in the data your end user has and the ease of which they can enter data in a form.

Uses:
I have a combo box that lets users see Invoices from previous days. Only the dates where actual invoice were made show because the Query in the Combo box is a "Group By" on the Date. Of the Invoice table The Dates are sorted Descending so the most recent is at the top in the Combo window. When they click that date the After_Update event of the Combo sets the Record source of a subform to just those Invoice which are based on the Combo boxes date.
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top