"enter" after combo box entry

[Lauren H]

I am using a combo box to lookup employee information stored in a LARGE
table. This combo box autofills numerous items on my form and gives me
information about the selected employee. This is the code I am using on the
combo box:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[person_id] = '" & Me![Search_Employee] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This works great! The problem is that I have created a subform (accessed
through a command button on this form) used to UPDATE information. One of
the options to update is to change the employee. When I change the employee
on the subform, I want the combo box to look up the new employee and display
the information on the main form.

I CAN GET THE NAME TO APPEAR IN THE COMBO BOX, BUT I CAN'T MAKE THE SELECTED
EMPLOYEE'S INFORMATION APPEAR ON THE MAIN FORM. URGH!

Here is the code I am using to send the name to the combo box:

Private Sub Command49_Click()
Dim sqlupdaterecord As String

sqlupdaterecord = "UPDATE Tbl_Cases SET Case_Name = '" & Me.newcasename &
"', "
sqlupdaterecord = sqlupdaterecord & "PersonID = '" & Me.newemployee & "', "

sqlupdaterecord = sqlupdaterecord & "WHERE CaseID = " & Me.CaseID

Dim db As DAO.Database
Set db = CurrentDb
db.Execute (sqlupdaterecord)

MsgBox "Case details updated successfully.", vbInformation, "Case Management"

[Forms]![WS_Case_Management]![Search_Advisor].Value = Me.newemployee

[Forms]![WS_Case_Management].SetFocus

DoCmd.Close acForm, "Case_Management_Edit_Case_Details", acSaveNo

[Forms]![WS_Case_Management]![Case_Management_Cases_Subform].Requery

End Sub

Any help that anyone can offer would be GREATLY appreciated!! Thanks!!

 

[Robert Morley]

I think if you just add a [Forms]![WS_Case_Management].Requery to your code,
you should be good to go. You might also have to do an rs.Requery, though
that's a little trickier from a subform. But try the first one, and then if
you need to do an rs.Requery, we'll play around from there.


Rob

 

[Lauren H]

Well, I tried that and it didn't work. Thanks for the try though! Any other
ideas? Just to clarify, the choice that a user makes it the combo box fills
text fields all over the form. The code we are writing is being used on a
command button on a separate form that is used to edit details of a
particular case associated with an employee. That button updates the
information, closes the form, and sets the focus back to the main form. If
the user changes the employee that is associated with that case, I want to
insert the new employee's name into the combo box and fill the text fields
(by doing the equivalent of either pressing enter or clicking on a drop-down
option within the combo box). Hopefully this makes sense and we're on the
same page.

:
I think if you just add a [Forms]![WS_Case_Management].Requery to your code,
you should be good to go. You might also have to do an rs.Requery, though
that's a little trickier from a subform. But try the first one, and then if
you need to do an rs.Requery, we'll play around from there.

Rob