To populate the rest of the controls in the form, make the Before Update
event of the combo fire a macro with as many SetValue actions as the
controls you want updated, with arguments:
Item: [ControlNameToBeUpdated]
Expression: DLookup("[FieldName]","table1","[SSN]='" & [ComboName] & "'")
Change field, table and control names as required.
Note: the above assumes the SSN field is text; if it's numeric, the
expression should be:
Expression: DLookup("[FieldName]","table1","[SSN]=" & [ComboName])
Needless to say, the same can be accomplished through code like:
Me.ControlName = DLookup("[FieldName]","table1","[SSN]='" & Me.[ComboName]
& "'")
or
Me.ControlName = DLookup("[FieldName]","table1","[SSN]=" & Me.[ComboName])
Now, to get records transferred to another table: Make a query on the linked
table, change it to an Append query on the target table, and in the
criterion line under the SSN field type:
Forms![FormName]![ComboName]
(change to the right names)
When you run the query, it will append record(s) based on the current
selection in the combo. Use the command button wizard to add a button on
your form to open (run) the query, so you choose the SSN in the combo, click
on the button and it's done!
HTH,
Nikos
sunberries said:
I have a read only Excel (table1) linked as a data source for my combo box, I
wanted to be able to get the rest of the field data to another table (table2)
by selecting only 1 field value using a form. Example: select one Social
Security number from the combo box and the rest of the personal information
will populate the form. In the event, it kinda downloads the info from table
1 to table2 using the combo box. Is this possible?