Auto fill form from linked Excel

[Bkndbrown]

The pertinent part of the database has 2 tables and a linked excel. There are more but this is the part I am stuck on.
Table 1 has Individual Names units and key field
Table 2 has Administrative Actions that was taken against the names listed in Table 1 linked by that key field
Linked excel is a roster that we pull out of another system. It has all personal information on everyone. (gender, Rank, Bday, the key field linked to both Table 1 and 2) I was using this as a linked excel to make it easier for others to update the roster by just swapping it out when a new roster is released.



I have to load the personal information from the linked excel into Table 2 with the Administrative Actions. Typically I would just have all of this info in Table 1 with the name but that will not work in this case. This is because for instance I don’t need to know how old you are now. I need to know how old you were when you received that action.

On my data entry form I would like to use a combo box to select a person. Then I would like it to automatically populate the fields on the form that will be stored in Table 2 when it is saved, with data that is pulled from the linked excel.

Again I am extremely novice, I am sure I am missing something easy that I am missing. but trying to learn.

[Gicu]

The easiest is to make sure the combo's row source includes all the fields you want to populate (you don't need to show them, just set the column width to 0 for the ones you want to hide but make sure you accurately set the number of columns property).
Then in the after update event of the combo you should prompt if the user want to update\populate then use something like this pseudo-code:

Me.txtRank=Me.cboName.Column(n) 'would populate txtRank textbox bound to Rank field with the value from column n of the combo's row source -keep in mind that the column collection is 0 based so if n=2 it will actually be the third column
Me.txtGender=Me.cboName.Column(m) '....