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
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

Open in new window

ghjeffeiiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
rs.FindFirst "[PalletNumber] = " & Me![cmbPalletSearch]
' 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
0
ghjeffeiiAuthor Commented:
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_BeforeUpdate(Cancel As Integer)
    iLocate = Nz(Me.txtPalletNumber, 0)
End Sub
0
ghjeffeiiAuthor Commented:
Any comments, suggestions, solutions...?
0
peter57rCommented:
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.

0
ghjeffeiiAuthor Commented:
I ended up just using the NotInList event instead, but I don't understand why the original If...Then...Else construct didn't work correctly here.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.