Solved

MS Access Recordset Search

Posted on 2009-07-09
5
681 Views
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.

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

0
Comment
Question by:ghjeffeii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
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

    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
 

Author Comment

by:ghjeffeii
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
0
 

Author Comment

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

Expert Comment

by:peter57r
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.

0
 

Accepted Solution

by:
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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