adding search to my form in access

[lillyehrhart]

i have more that one button that i am trying to search different things. here is my first button which is my search button for the sku

Private Sub cmdSearchSku_Click()

    Dim bkmk As Variant
    Dim strField As String
    
    Me.RecordsetClone.MoveFirst
    
       Me.RecordsetClone.FindFirst "[SKU] Like " & Chr(34) & Me.txtSearchSku & "*" & Chr(34)

        
    
    If Me.RecordsetClone.NoMatch Then
        msgBox "No Match"
    Else
        bkmk = Me.RecordsetClone.Bookmark
        Me.Recordset.Bookmark = bkmk
    End If

i want to add another search button for the description and another one for code. so three in total. when i add the same code to the search by description button, it just come back no records found, when i type in exactly what i am looking for, and the same for nmfc code. i tried to make the variable a bkmk1 and bkmk2, but then it gives me an error! please help!

[Seth Schrock]

Please use [CODE/] tags when posting code, per site rules.

You appear to be trying to do what is on the MSDN website: Form.RecordsetC lone Property

Their example is like this:

Sub SupplierID_AfterUpdate() 
    Dim rst As Recordset 
    Dim strSearchName As String 
 
    Set rst = Me.RecordsetClone 
    strSearchName = Str(Me!SupplierID) 
    rst.FindFirst "SupplierID = " & strSearchName 
        If rst.NoMatch Then 
            MsgBox "Record not found" 
        Else 
            Me.Bookmark = rst.Bookmark 
        End If 
    rst.Close 
End Sub

You should only have to change lines 6 and 7 to fit your criteria and then it would work on your form.

[lillyehrhart]

why is this saying after update?

Private Sub buttonSearchDesc_Click()
Dim search As Recordset
Dim txtSearchDesc As String

Set search = Me.RecordsetClone
txtSearchDesc = Str(Me!description)
search.FindFirst "Description" = " & txtSearchDesc"

If search.NoMatch Then
    msgBox "record not found"
    Else
    Me.Bookmark = search.Bookmark
    End If
    
    search.Close

the txtsearchdesc is my textbox and description is what i am trying to search for. it is not working. the error is 'type mismatch'.

[Seth Schrock]

For the code tags to work, remove the / in the first one. You can also use the [CODE/] button to add the tags for you.

Line 7 should be

search.FindFirst "Description = '" & Me.txtSearchDesc & "'"

[lillyehrhart]

it is saying type mismatch and then highlighting this line

txtSearchDesc = Str(Me!description)

[Seth Schrock]

Try just removing that line.

[lillyehrhart]

okay!! thanks so much!! i got that part to working!!! life savor!!

[Seth Schrock]

Great! Glad I could help. Good luck on your project.

[lillyehrhart]

oh an i do have another question for you. when you pull up the search, it just pulls up the first one it finds with that match, that is when i used the find first field. is there anyway to make it show me all of the records with that code? and separate those out.

[NeoPa]

Hi Lilly.

It sounds like you're looking for a filtering solution rather than a search/find one. Very similar in concept but coded quite differently.

The good news for you is that filtering is mostly handled automatically by Access so much less complicated to code. Have a look at Cascaded Form Filtering or Example Filtering on a Form for help on that.

NB. Pay special attention to how differently numeric, date and string literal values are used in the filter string. For strings, such as you're likely to find in a [Description] field, you want single-quotes (') around the value.