Yesterday I mentioned a technique I use to hide repeated report fields: https://www.accessjumpstart.com/hiding-duplicate-records-using-running-totals-over-a-group/
Today, I will discuss another technique I use to update fields on a form based on columns in a combo box to avoid another query. In this technique, I add columns to a combo box recordsource with related information that is needed for the form.
The case I’m using it for is to select the details of an estimated line item for a job with an Item Description, Quantity, Unit Price, etc. and I am adding it to a line items table for an order.
On a blank line, the user can simply select the corresponding line in the combo box which is already populated with the needed fields.
The Item Description row source looks something like this:
SELECT Item_Description, Qty, Unit_Price FROM estimated_items WHERE job_id=12
You need to make sure that the Number of Columns property in the combo box properties matches the number of items you are going to extract. In this case it would be 3.
Then you can either show or hide the columns using the Column Widths property. Each number indicates the inches wide that column will display as when you drop down the combo box. To hide a column you set the width to 0″. So these values:
2″;0″;0.5″
will set the Item Description column pretty wide at 2″, the Qty column hidden at 0″, and the Unit_Price column shown at 0.5″.
In the AfterUpdate event of the combo box control (if the form is bound and this control is bound), you can then use this code to access each of the values in the selected row:
Msgbox Item_Description.Columns(0) ‘ Item Description… column 1
Msgbox Item_Description.Columns(1) ‘ Qty… column 2
Msgbox Item_Description.Columns(2) ‘ Unit_Price… column 3
So there you have it. Then you can set the values of other controls on the form to the selected values without having to do any more querying.