Combo Box Lookup Question

[alpine82]

I have a subform, and on that subform there is a combo box and many other fields from a table. Is there a way for me to click on a record from a combo box, and have the existing record in the table fill in the fields on a form so I can edit it the existing record, instead of having to enter a new one?

 

[batman26]

Fill from combo

You can accomplish this by setting the RowSource for the combo box to include the data for fields that you want to populate. If you don’t want the users to see all the data in the combo box dropdown, set the width to ‘0’ for those fields. Then, on the ‘Click’ event of the comb box, set the value for the fields in your sub form to
Me.SomeField = Combo Box.Column(3).
Remembering that the column number (in VBA Code) starts with ‘0’ not ‘1’. in the Properties it starts with '1'

So, your RowSource might look like this:
SELECT Aaaa, Bbbb, Ccccc, Dddd, Eeee, Ffff, Gggg FROM SomeTable
The Column Count would be set to 7
The Column Widths would be 1.25”; 0”; 0”; 0”; 0”; 0”; 0”
You would then set the fields to something like this:

Private Sub ComboBox_Click()
With Me
.Field 1 = ComboBox.Column(1) ‘this would put Bbbb in field 1
.Field 2 = ComboBox.Column(2) ‘this would put Ccccc in field 2
.Field 3 = ComboBox.Column(3) ‘this would put Dddd in field 3
etc. ‘Finish assigning values to the fileds.
End With
End Sub