Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2009
    Posts
    95
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Go to record based on search in Access 2007 form

    Hello
    I would like to be able to go to a record in an Access 2007 form by searching on partial criteria. For example if I am looking for a business name and it has the word Training anywhere in it's name field I want the form to navigate to that record and then the next record that contains Training. What is the best way to achieve this?

    Thanks for any help you can provide!

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Mostate,

    Here's some code that I use in this situation.

    Here's the form:
    OwnerInput.JPG
    The following code is attached to the respective button above the name field.
    Code:
    '                            +---------------------+              +----------+
    '----------------------------|cmdFindRecord_Click()|--------------| 07/21/12 |
    '                            +---------------------+              +----------+
    'Called by: Form Button Click
    'Calls    : N/A()
    'Notes    :
    
    Private Sub cmdFindRec_Click()
    
      Dim zWhatToFind As Variant
      Dim lCurOwnerID As Long
      
    On Error GoTo cmdFindRec_Click_Err
    
        lCurOwnerID = Me.OwnerID
        LastName.SetFocus
        
        zWhatToFind = Trim(InputBox("Enter the first part of the last name:", _
                               "Find Owner Record"))
        If zWhatToFind = "" Then GoTo cmdFindRec_Click_Exit  '*** Handles Cancel button ***
        
        DoCmd.FindRecord zWhatToFind, acStart, False, acSearchAll, True, acCurrent, True
    
        If Forms![frmOwnerInput].OwnerID = lCurOwnerID Then
          MsgBox "No records found with a Last Name starting with: " & zWhatToFind, _
                 vbOKOnly + vbInformation, "No Matches"
        End If
        
    cmdFindRec_Click_Exit:
        Exit Sub
    
    cmdFindRec_Click_Err:
        MsgBox Error$
        Resume cmdFindRec_Click_Exit
    
    End Sub                      'cmdFindRec_Click
    
    '                         +------------------------+              +----------+
    '-------------------------|cmdNextOwnerName_Click()|--------------| 07/21/12 |
    '                         +------------------------+              +----------+
    'Called by: Exit Buton on frmOwnerInput
    'Calls    :N/A
    
    Private Sub cmdNextOwnerName_Click()
     
      Dim ctlPrevious As Control
      Dim zOwnerFName As String
      
      zOwnerFName = Me.FirstName  '*** Used to see if Record Changed!
      
       Set ctlPrevious = Screen.PreviousControl
       If ctlPrevious.Name <> "LastName" Then
         MsgBox "You must find a Name before" & vbCrLf & _
                "you can use the Find Next Button", _
                vbOKOnly + vbCritical, "Button Unavailable"
         GoTo Exit_cmdNextOwnerName_Click
       End If
       
    On Error GoTo Err_cmdNextOwnerName_Click
        
        Screen.PreviousControl.SetFocus
        DoCmd.FindNext
         
      If Me.FirstName = zOwnerFName Then
        MsgBox "No more matches!", vbOKOnly + vbInformation, "Warning: No Match"
      End If
      
    Exit_cmdNextOwnerName_Click:
        Exit Sub
    
    Err_cmdNextOwnerName_Click:
        MsgBox Err.Description
        Resume Exit_cmdNextOwnerName_Click
        
    End Sub                   'cmdNextOwnerName_Click
    The user presses the "Find Owner" button to enter the partial search term to find the first matching record. To find subsequent records that match the criteria they press the down arrow icon. The code provides the appropriate messages if no match exists or the last match has been reached.

    Of course you'll have to substitute your table/field information.
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Star Lounger
    Join Date
    Apr 2009
    Posts
    95
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks so much for the reply. I am not a coder so I am attaching the code with my changes the form name is Pay and Benefits and the field name I want to be able to search on is FirstName. Would you be able to look at my code and tell me what is wrong as it's not working?

    Thanks!
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,193
    Thanks
    201
    Thanked 784 Times in 718 Posts
    Mostate,

    The only obvious error I could find is that in your DB your initial search is for a string value (First Name) and my code was searching for a number so the variable definition (Dim) for lCurFirstName as Long needs to be changed to zCurFirstName as String. Note that all occurances of lCurFirstName need to be changed to zCurFirstName.

    The only other thing is to make sure your buttons are named cmdFindRec and cmdNextFirstName respectively or the events will not fire. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    MOSTATE (2014-03-17)

  6. #5
    Star Lounger
    Join Date
    Apr 2009
    Posts
    95
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Works like a charm...thanks so much

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    You could also use the filter technique of the form:
    Me.Filter = "BusinessName Like '*training*'"
    Me.FilterOn = True
    To find subsequent records use the navigation buttons at the base of the form

  8. #7
    Star Lounger
    Join Date
    Apr 2009
    Posts
    95
    Thanks
    10
    Thanked 0 Times in 0 Posts
    Thanks for the reply, Patt. Where do I place the Me.Filter code?

  9. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,537
    Thanks
    0
    Thanked 23 Times in 23 Posts
    In the button that does the find:
    Code:
    Private Sub cmdFindRec_Click()
         Me.Filter = "BusinessName Like '*training*'"
         Me.FilterOn = True
    End Sub
    Or if you type the text into a text box (unbound) on the form it could be:
    Code:
    Private Sub cmdFindRec_Click()
         Me.Filter = "BusinessName Like '*" & yourtextbox & "*'"
         Me.FilterOn = True
    End Sub
    where yourtextbox is the name of your text box.
    cmdFindRec is the name of the find button you created for this.
    Last edited by patt; 2014-03-20 at 18:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •