Simulating a "click" action

[JPritch]

I have a series of drill-down combo boxes on a form, to keep from having to list hundreds of employees at once:

1) Dept_combo, (shows available department names)
2) TL_combo, (looks up team leaders in the selected Dept)
3) EmpName_combo, (looks up employees for the selected Dept/TL)
4) EmpID_listbox, (shows the employee ID(key field) for the EmpName selected in step 3)
.....EmpID value then sent to the subform to pull up the employees data.

My problem however, is that after selecting Employee Name, the Employee ID list box is populated, but in order to send that value to the subform, you must manually click in the Emp ID listbox.

Is there any way to code sending the Emp ID to the subform right after somebody selects the employee name from the combo box, and not have to manually click the listbox any more?

Here's the code for the Employee Name Combo Box:

Private Sub ComboName_AfterUpdate()
    Me.ListSSO.Requery
    Me.ListSSO.SetFocus
End Sub

Here's the code for the Employee ID List Box:

Private Sub ListSSO_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SSO_NBR] = '" & Me![ListSSO] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

I appreciate all the help!

 

[MarkK]

Private Sub ComboName_AfterUpdate()
    Me.ListSSO.Requery
    Me.ListSSO.SetFocus
    ListSSO_AfterUpdate
End Sub

 

[JPritch]

Thanks Lagbolt, I tried the code and some combinations close to it, but I'm still required to click the listbox in order for the subform to change. Am I missing something?

 

[MarkK]

- So I'm taking a re-look at this and I don't see why you need the EmployeeID listbox. what you call ListSSO. The bound column of the employee name combo--ComboName--should be the employee ID, so when you select employee name, you're done: You know the ID.
- And that ComboName_AfterUpdate handler can do the search--and you don't need all that recordsetclone/bookmark stuff. Access forms didn't used to have a Recordset property which you can now use like this...

private sub ComboName_AfterUpdate()
  me.recordset.findfirst "[SSO_NBR] = '" & Me.ComboName & "'"
  'not sure why that is a string
end sub

Are we getting closer?