Combo Box Lookup that populates other fields besides the combo box

On rare occasions -- Lets say you want to populate a table of 'reviewers' using the employee table as a lookup. Your first instinct is to only capture the key of the employee table and use the employee table to display employee data. But the review table is static, that is, the tracking of when a employee reviewed something is done at one point in time. When the employee gets a promotion and a new title or moves to a different department, you don't want that information in the review table -- you want ot populate the same fields in another table in a different table.

You want to select the employee for example using a combobox to lookup the key field and save it in the combobox fields source. But also return various other values and populate other fields.

Set the source of the combo box to include the key column plus the other columns.

Use the after_update event of the combobox to populate the other fields:

Private Sub cb_Employee_FK_AfterUpdate()
Me.Department = cb_Employee_FK.Column(2)
Me.Title = cb_Employee_FK.Column(3)
end sub

Important to remember however is if the property Column Count isn't set to the number returned you wont have access to them using the column(#) property above.

But this displays too many columns in the when using the combobox for a lookup right!?

Hide the ones you don't want by setting the width property 0;1.5;0;0 for example.