VBA code to show record on form (1 Viewer)

[brichardson1991]

So I have a form which is showing the current record and you can scroll through these and make modifications to them from the form as opposed to using a table.

The form has the following fields populated from the main table
Customer name (Can appear more than once)
Status
Date
Servers
Positions

In addition it has several buttons for next record, previous record, first & last record, new record, delete record and update record.

On this form i have a text box that i want to be able to use to search for a record using the customer name when you press the search button associated with it.

I've got some code and it is mostly working but it seems to be moving the text entry cursor to the date box of the record for some reason.

If you could help me fix this I would really appreciate it.


What I want it to do is take the text from the search box and find it and move the form to that record. Ideally allowing me to then scroll through that customer.


Here is my code so far:

Private Sub btn_cstmr_srch_DblClick(Cancel As Integer)
On Error GoTo HandleError
    Dim strFindWhat As String
    strFindWhat = Me.txt_cust_search.Value
If IsNull(Me.txt_cust_search) Or Me.txt_cust_search = "" Then
            MsgBox "You must enter a customer name to use this function", vbCritical, "Error!"
            'if the textbox for searching is empty throw a message up!
            Me.txt_cust_search.SetFocus 'focus on the textbox in question
Else: DoCmd.FindRecord (findwhat = strFindWhat), (Match = acEntire), (matchcase = False), (search = acSearchAll), (searchasformatted = False), (onlycurrentfield = acCurrent), (FindFirst = False)
                    'the above line is for finding the customer via the text box
        Exit Sub
    End If
HandleExit:
    Exit Sub
HandleError:
    MsgBox Err.Description
    Resume HandleExit
End Sub

 

[vbaInet]

Welcome to the forum!

Here's a link to a search form that a lot of people on here use:
http://allenbrowne.com/ser-62.html

 

[brichardson1991]

Thanks for the reply.

unfortunately that hasn't really helped me

i've tried another bit of code

DoCmd.SearchForRecord acActiveDataObject, , , "[Customer] = " & strFindWhat

but this hasn't worked either.

 

[vbaInet]

I don't understand what you mean by "that hasn't really helped me"?

 

[JHB]

If Customer is a string value then:

"[Customer] = '" & strFindWhat & "'"

 

[brichardson1991]

I don't understand what you mean by "that hasn't really helped me"?

I tried to use that but I can't get what I want out of it.

If Customer is a string value then:

"[Customer] = '" & strFindWhat & "'"

This worked for me.

Now how would I get it to search for the next value based on that customer.
So if a customer appeared more than once it wouldn't go to just the first record.

Dim strFindWhat As String
    strFindWhat = Me.txt_cust_search.Value
    DoCmd.SearchForRecord , , acNext, "[Customer] = '" & strFindWhat & "'"
    'This code will search for the customer as in the box but the next value

Edit: figured it out code above

Solution solved

 

[vbaInet]

Good to see that you sourced it out!