Populating fields using combo box

[JRickman]

In a form, I have a combo box for staff ID which displays staff ID, staff first name, and staff last name, and fills in the last name and first name text boxes as well as the staff ID box based on the selection. I have the following code to do this:
Private Sub cboStaffID_Change()
Me.txtStaffFirstName.Value = Me.cboStaffID.Column(2)
Me.txtStaffLastName.Value = Me.cboStaffID.Column(3)
End Sub
In both the form and the table, the staff ID, first name, and last name all show up correctly in the proper fields.
In the same form, I have a combo box for student ID and two text boxes for student first name and student last name. Though I followed the same steps as I did when doing the above, the results are different. Instead of showing three fields in the selection window (student ID, student first name, student last name as what is similarly output for staff), it only displays two (student first name and student last name). Again, I have similar visual basic code:
Private Sub cboStudentID_Change()
Me.txtStudentFirstName.Value = Me.cboStudentID.Column(6)
Me.txtStudentLastName.Value = Me.cboStudentID.Column(7)
End Sub
When I make a selection, the student first name shows up in the student ID field in the form, though in the table, the student ID is correctly written. In the table, neither the student last name or student first name are written.
Anyone have a clue as to what could be wrong?
Many thanks, Jim

 

[John Big Booty]

Sounds like the Row Source for your Second Combo is different to that of your first. Compare the row sources and see what is different between them.

Given that you are storing the Student/StaffID, you really should also be storing their given and family names as well, this is in breach of the rules of Data Normalisation. Rather than have the code you are using in the combo box's After Update event, simply use the following schema as the Control Source for and Unbound Text box;

= cboStaffID.Column([B][COLOR="Red"]x[/COLOR][/B])

Where x represents the column number you wish to display in that text box.

 

[JRickman]

Thank you for your response. I seem to be still having trouble, however. I agree with your view that redundancy of data is not necessary.
What I really want to see is the following.
Here's the table:
ID AutoNumber
StaffID Number
DateOfService Date/Time
StudentID Number
Duration Number
TypeOfService Text
Notes Text
In the form, what I’d like to see when choosing the StaffID is for the following three columns to appear: StaffID, StaffFirstName, StaffLastName (these come from the Staff_Info_T table. When selected, just the StaffID will go into the StaffID cell. The same goes for StudentID (info coming from Student_Info_T table).

How best to make this happen?
I know this is a beginner's question, but any help will be greatly appreciated.
Thank you, Jim