ghjeffeii
asked on
MS Access Recordset Search
I'm attempting to search a recordset for an integer using the attached code. Included in the code snippet is an if...then...else construct that is puzzling me. I can't get the else part to come true. This results in the user always being taken to pallet 1 (first item in recordset) if the record isn't found which is deceiving. Any help would be much appreciated.
Please let me know what else you might need from me.
Thanks
Please let me know what else you might need from me.
Thanks
Private Sub cmbPalletSearch_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[PalletNumber] = " & Me![cmbPalletSearch]
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Else
rs.FindFirst "PalletNumber = " & iLocate
MsgBox "Pallet Not Found"
End If
End Sub
ASKER
Sorry, iLocate is declared as Long in the general declarations section of the form and initialized w/ the below code snippet. My thinking was that if the record isn't found in the record then the form would return to the record (pallet) that the form was on before the user performed the search. I know through debugs that the BeforeUpdate event returns the current record properly and the AfterUpdate event also returns the correct record, but alas i always end up on record 1 if the pallet being searched isn't found.
Private Sub cmbPalletSearch_BeforeUpda te(Cancel As Integer)
iLocate = Nz(Me.txtPalletNumber, 0)
End Sub
Private Sub cmbPalletSearch_BeforeUpda
iLocate = Nz(Me.txtPalletNumber, 0)
End Sub
ASKER
Any comments, suggestions, solutions...?
I suppose the obvious Q is why you are not basing the rowsource of the combo box on the pallet numbers that do exist and restricting the user's choice to the list.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
' tries to find the pallet number matching the value in the form
If Not rs.EOF Then
' assumes that if you don't reach eof then something is found
Me.Bookmark = rs.Bookmark
' sets the form record pointer to the found record
Else
' there is no match
rs.FindFirst "PalletNumber = " & iLocate
' find the pallet corresponding to iLocate
' there is no clue in the code as to what iLocate is - the value must be set elsewhere
' but nothing happens to the found record in this code
MsgBox "Pallet Not Found"
End If