Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    We have a database which has a 'Find record' button which basically brings up the same prompt as Ctrl+F. The code for this button is shown below:

    Private Sub FindRecordButton_Click()
    On Error GoTo Err_FindRecordButton_Click
    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_FindRecordButton_Click:
    Exit Sub

    Err_FindRecordButton_Click:
    MsgBox Err.Description
    Resume Exit_FindRecordButton_Click

    End Sub


    There are a few issues with this - one is that it will only search 'forward' in the database (ie, if you've already gone past the record it won't find it), two is that it defaults to matching the "Whole field" rather then "Part of field" and three is that you have to be clicked in the field you want to search.

    Is there a better way to create a 'Find Record' button which will allow the user to:
    • Search both forwards and backwards in the database, essentially looping through the records until they find the one they want.
    • Default to searching any part of the field instead of the whole field
    • Being able to select the field you want to search on


    Cheers,
    Jason

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    1) The Find dialog does cycle. It stops when all records have been searched once.

    2) To make Access search any part of the field, replace the line

    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    with

    SendKeys "%ha%n"
    RunCommand acCmdFind

    3) You could place a combo box next to the command button from which the user can select a field. The code would then set focus to the corresponding control instead of to Screen.PreviousControl.

  3. #3
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    1) The Find dialog does cycle. It stops when all records have been searched once.

    2) To make Access search any part of the field, replace the line

    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    with

    SendKeys "%ha%n"
    RunCommand acCmdFind

    3) You could place a combo box next to the command button from which the user can select a field. The code would then set focus to the corresponding control instead of to Screen.PreviousControl.
    Thanks Hans, that has resolved my issue.

    Cheers,
    Jason

Posting Permissions

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