Create search form in Access

[NeoPa]

Try this version :

Option Compare Database
Option Explicit

Private Sub Command19_Click()
    With Me
        If .Text17 > "" Then
            Call .Recordset.FindFirst("([Vehicle Model]='" & Text17 & "')")
            If .Recordset.NoMatch Then
                Call MsgBox("No record found" _
                          , vbOKOnly + vbInformation _
                          , "Sorry")
                .Text17 = Null
            End If
        End If
    End With
End Sub

[ashjones86]

Hi NeoPa,

So i tried this and got 2 problems with it, in the vb editor the line

Call .Recordset.FindFirst("([Vehicle Model]='" & Text17 & "')"

is highlighted as red, if i save and ignore it, it brings up a syntax error on this line

Private Sub Command19_Click()

which is highlighted in yellow.

[ashjones86]

Ok, so found a solution to this, thank you to Slaxer13 who had the patience of a saint with me, back and forth over 150 emails.
Here is the final code for anyone else it may help .

Option Compare Database

Private Sub cmdAll_Click()

    Dim LSQL  As String
    
    'Display all vehicles
    LSQL = "select * from Vehicles"
    
    Form_Vehicles.RecordSource = LSQL
    
    'lblTitle.Caption = "Vehicle Details:  All records"'
    
    MsgBox "All Vehicles are now displayed."
    
End Sub

Private Sub cmdClose_Click()

    'Close form
    DoCmd.Close
    
End Sub

Private Sub cmdSearch_Click()

    Dim LSQL  As String
    Dim LSearchString As String
    
    If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search string."
        
    Else
    
        LSearchString = txtSearchString
        
        'Filter results based on search string
        LSQL = "select * from Vehicles"
        LSQL = LSQL & " where Vehicle_Model LIKE '*" & LSearchString & "*'"
        
        Form_Vehicles.RecordSource = LSQL
        
        Form_Vehicles.Caption = "Vehicle Details:  Filtered by '" & LSearchString & "'"
        
        'Clear search string
        txtSearchString = ""
        
        MsgBox "Results have been filtered.  All Vehicles containing " & LSearchString & "."
        
    End If
    
End Sub

[ashjones86]

I Have chosen my own comment as best answer, but ALL credit goes to Slaxer13.

[Slaxer13]

Just a thing you forgot to mention. The code you showed has got Show All Records Button (cmdAll), Close Button (cmdClose) and Search Button (cmdSearch).

Cheers, Slaxer13

[NeoPa]

We don't encourage members to select their own posts as Best Answers, but in this case as you explain where the help from we can make an exception. You have, after all, posted the solution you came up with which is helpful.

As for post #32, there was indeed an error. The updated post #31 has the fix in it. The closing parenthesis was missing at the end.

As a general rule though, it is very helpful for all of us - especially yourself (and I say this understanding that you are learning this now so it's not criticism but helpful instruction for the future.) if you following the instructions in Before Posting (VBA or SQL) Code before posting problem code. Your time and efforts / frustration is increased if we take much longer to help you due to the lack of sensible information about a problem.

Also, whenever you post of an error in code it's very helpful if you include a reference to the line of code (which you did in this case) and include the error description and number (in order of importance) if there is one, or a description of what is not as expected if it fails in any other way.

[ashjones86]

Hi NeoPa,
My apologies for any forum etiquette i failed to follow, will make sure i go through some of the guides and the posts are more efficient to both myself and other users in future posts.

[NeoPa]

Your only culpability is that you're new Ash :-)

We do try to remember that and not hold it against people (as we like to think of ourselves as relativley intelligent and to forget that wouldn't be ;-)) Sometimes though, as many of us are so long in the tooth here, we forget. Luckily in this case not. You're doing a very fine job so far, and after reading and understanding the linked thread will do even better in future I'm sure.