How to Lookup in ComboBox & Fill multiple Fields in Record

[Will]

I have a Small Table with about 6 fields per record.

I have a Large Table with about 20 fields per record... 6 of those fields
will have data from the Small Table.

In this case I can't just store the Small Table ID of the related record in
the Large Table

I have to actually put the data in each of the 6 fields in the Large Table.

I have a Form that shows all the fields of the Large Table.

I want to put a Combo Box on it that will let the user lookup the correct
record in the Small Table and then fill the corrosponding 6 fields in the
Large Record.

I'm not sure how to do it.

Thanks for any help on this one.

Will

 

[Arvin Meyer [MVP]]

Rest assured that you CAN use the Small Table ID if your database is
designed properly, and that is certainly the preferred way to go. If you
insist of duplicating the data, you can do it be referencing the columns in
your combo box in it's AfterUpdate event. As you can see the column index is
zero(0) based:

Sub cboMyCombo_AfterUpdate()
Me.txtControl1 = Me.cboMyCombo.Column(0)
Me.txtControl2 = Me.cboMyCombo.Column(1)
Me.txtControl3 = Me.cboMyCombo.Column(2)
Me.txtControl4 = Me.cboMyCombo.Column(3)
Me.txtControl5 = Me.cboMyCombo.Column(4)
Me.txtControl6 = Me.cboMyCombo.Column(5)
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access