MS Access Recordset Search

Posted on 2009-07-09
Last Modified: 2013-11-28
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.

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
        rs.FindFirst "PalletNumber = " & iLocate
        MsgBox "Pallet Not Found"
    End If
End Sub

Open in new window

Question by:ghjeffeii
  • 3
  • 2
LVL 77

Expert Comment

ID: 24820798
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

' 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

Author Comment

ID: 24824744
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

Author Comment

ID: 24831869
Any comments, suggestions, solutions...?
LVL 77

Expert Comment

ID: 24834322
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.


Accepted Solution

ghjeffeii earned 0 total points
ID: 24835020
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.

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Date/Time to SQL Datetime2(7) 9 22
Ms Access VBA Variables 6 26
Run SQL Server Proc from Access 11 27
Access VBA, adding Progress Bar in code to allow execution. 7 27
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now