combo box and navigation buttons

[Tony]

I have created a simple database based on the Time and
Billing template in Access XP. On the Time Cards form I
have added a following code to the EmployeeID combo box,
after update event:

Private Sub EmployeeID_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me!
[EmployeeID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

I want to have to methods for selecting records or moving
through the methods - using navigation buttons or through
selection using combo box. For some reason it is not
always working. Sometimes moving through the records is
ok, sometimes is giving wrong results. Sometimes after
first selecting record using combo box switching to the
navigation buttons is not refreshing the combo box values.
Any suggestions on what I am missing, what is wrong ?

Thanks for advice.

Tony

 

[Dale Fye]

Tony,

I'm not familiar with the form that you are using, but if the EmployeeID
text box is a bound, then you don't want to change the value in that field,
you want to add another text box that you use for your search string.
Editing the value in the EmployeeID field will do just that, change the
EmployeeID associated with the current record you are on. When that
happens, the find first, might find the record you are on, or it might find
another record that contains that value.

You need to use the NoMatch property of the recordset, not the EOF. Also,
you might want to add a message box that indicates no matching record was
found.

Private Sub txtSearchFor_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

If LEN(me.txtSearchFor & "") = 0 then
msgbox "Cannot search for an empty string"
Exit sub
endif

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Nz(Me.txtSearchFor.value, 0))
If rs.NoMatch then
msgbox "Record not found!"
Else
Me.Bookmark = rs.Bookmark
End if

End Sub

HTH
Dale