Pulling data from a table to a form

[Sirkevin]

I wonder if anyone can enlighten me.

If I have a table say with two fields Member and staff code:

Member..... Staff Code
Jon.................1
Adam.............10
Sarah.............7
Bob................8

If I also have a form with various details including a dropdown box for the member name (taken from the table above), is there any way I can have a seperate box on the form, which automatically fills the correct staff code when the member is chosen.

In a nutshell, I want the user to select the member and for the staff code to be filled automatically in the form.

I would also like it so that when the user updates the table with extra members and codes in the future, these still work in the same way.

Thanks in advance for any help.

 

[missinglinq]

Have you included the Staff Code in the combobox, i.e. does it have two columns, Member and Staff Code? That would be the standard way to do this, then simply use the AFterUpdate event of the combobox, assuming that the columns, running left-to-right, are Member - StaffCode:

Private Sub MemberComboBox_AfterUpdate()
  Me.StaffCode = Me.MemberComboBox.Column(1)
End Sub

StaffCode would be Column 1 in this case, because the index is Zero-based.

 

[Sirkevin]

This is exactly what I was looking for thanks.

 

[missinglinq]

Glad to help!

 

[Ilexa]

This helped me too !

I was doing the same thing with DLookUp function in the ControlSource of the textbox. With AfterUpdate event it works better, and obviously quicker.

I beg for some help with a similar thing: the value of a textbox in the form to be extracted from another table (not bound with the form) which is in relationship with the table from which the combobox is getting the data. Sorry for my not-uderstandable English - I even confused myself.
So, the combobox's control source is based on the table (tblA). This table consist a field that is in many-to-one relationship with the primary key of another table (tblB). And I want to get the corresponding data from tblB into the textbox of my form. Now, I'm doing this with DLookUp function:
Control Source = DLookUp("[result_field_from_tblB]";"[tblB]";"[field_from_tblA]=" & [Forms]![My_Form]![field_from_My_Form_equal_to_field_from_tblA])
and it works.

But could it be done with AfterUpdate event???

10x in advance.