Command button search code

[mraetrudeaujr]

I'm taking the advice of the many experts in this forum, and I'm weaning myself off of using MACROS. So, I searched the FAQ's on this forum for a way to 'code' the command buttons to search various fields. I found the following example:

Dim rst As Recordset
    Dim strSearchName As String

    Set rst = Me.RecordsetClone
    strSearchName = Me.txtSearch
    rst.FindFirst "PolInsured Like '*" & Nz(strSearchName, "") & "*'"
        If rst.NoMatch Then
            MsgBox "Record not found"
        Else
            Me.Bookmark = rst.Bookmark
        End If
    rst.Close

This code looks like I can modify it for my database, so how I would set this code up? On the 'On Click' event, and change the "PolInsured" to my particular field?

If I'm missing something, please show me what I need to accomplish this 'search/find' command button creation on my forms. Thank you.

Al

 

[Remou]

Yes, that sounds like it to me. You will also need a textbox on your form called txtSearch into which a user has filled a search string. It is referenced:
Me.txtSearch
In the code
You will also need some error handling and such things as checking that there is something to search for.

 

[mraetrudeaujr]

Oops. Maybe the above code ISN'T what I need...then.

How I have this set up is that every pertinent field (possible field that would be used to search for a record) has a command button on it. I used a little bit of code that calls a MACRO from a macro group. Since this database is becoming more sophisticated, I want to get rid of the dependancy on MACROS (no error-trapping) and have it work exclusively off of VBA. I'm sure that there is a sample of code that I could use, with only having to change the particular table and field names, right?

 

[Remou]

Have you tried saving your macro as code? Also, the above code must be quite close to what you want and a quick comparison with the saved macro may show this.

 

[mraetrudeaujr]

Remou,

I kept searching through this forum (archives) and I found the following code...THAT WORKS (for me)!

Private Sub cmdFindFirstNamePage4_Click()
On Error GoTo Err_cmdFindFirstNamePage4_Click


'    Screen.PreviousControl.SetFocus <-- Comment this line out
    Me.txtFirstNamePage4.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_cmdFindFirstNamePage4_Click:
    Exit Sub

Err_cmdFindFirstNamePage4_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindFirstNamePage4_Click
    
End Sub

Now, my only problem is that I need to be able to 'modify' HOW it's going to search; e.g. searching "Any part of field" in the Match field, and under the "Advanced" part, being able to have the "Search Fields As Formatted" box being checked. Any ideas? Thanks in advance.