Populate EMP_ID control with selection from EMP_NAME control

[KMEscherich]

Platform = Windows
Program = Access 2003


Hi there, I am stumpped!!

I am attempting to populate a form control as follows:

I have a form that has 2 drop-down boxes. One retrieves the EMP_ID and the 2nd one retrieves the EMP_NAME and EMP_ID.

When I make a selection from the EMP_ID control, the data in the database appears accordingly. When I make a selection from the EMP_NAME control, the data in the database appears accordingly, as well.

I am attempting to have it so when I make a selection from the EMP_NAME control, the EMP_ID control becomes populated with the EMP_ID that corresponds to the selection that I make using the EMP_NAME control.



RowSource for EMP_NAME
SELECT distinct T_EMPLOYEE_2.EMP_NAME , T_EMPLOYEE_2.EMP_ID FROM T_EMPLOYEE_2 ;

Private Sub Cmd_EmpName_Click()
  Dim rs As Recordset
 
  Set rs = Me.Recordset.Clone
  rs.FindFirst "[EMP_NAME] = '" & Combo_EmpName.Value & "' "
  If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 

=================================================

Rowsource for EMP_ID
SELECT DISTINCT T_EMPLOYEE_2.EMP_ID FROM T_EMPLOYEE_2;

Private Sub Cmd_EmpID_Click()
 
   Dim rs As Recordset
 
   Set rs = Me.Recordset.Clone
   rs.FindFirst "[EMP_ID] = '" & Combo_EmpID.Value & "'"
   If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 

When I run them independently of each other, they work just fine, but I need to have the EMP_ID control be populated with the EMP_ID when a selection is made from the EMP_NAME form control and also have the EMP_NAME control populate accordingly when a selection is made from the EMP_ID form control.

I hope I have supplied enough information and have explained it clearly enough for someone to help me.

Can someone please help me with this???

Thank you VERY MUCH for your assistance.

[NeoPa]

This all sounds like an overcomplication. A single ComboBox with both items in it is fully referenceable from code. There is no "valid" reason I can see why you would want to set them up as you have.

Having said that, the way to do it would be to set the value of the "other" ComboBox whenever one has an item selected.

Travel this path and expect problems though.