CODEWITHSUNDEEP
vbams-access
Simon Godfrey
Simon Godfrey

Reputation: 1

Finding records in an MS Access table using VBA On Change

I am trying to adapt code posted on the web https://www.microsoft.com/en-us/microsoft-365/blog/2012/05/03/using-a-combo-box-to-search-as-you-type/

The idea is to find records in an MS Access form by looking for the LastName of a person.

My VBA is as follows, but I get the Compile Error: syntax error message

Private Sub cboLastNameFind_Change()
' If the combo box is cleared, clear the form filter.
If Nz(Me.cboLastNameFind.Text) = "" Then
 Me.Form.Filter = ""
 Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboLastNameFind.ListIndex <> -1 Then
Me.Form.Filter = "[LastName] = '" &
Replace(Me.cboLastNameFind.Text, "'", """) & "‘"
Me.FilterOn = True

' If a partial value is typed, filter for a partial company name match.
Else
    Me.Form.Filter = "[LastName] Like '*" & _
                 Replace(Me.cboLastNameFind.Text, "'", """) & "*‘"
Me.FilterOn = True

End If

' Move the cursor to the end of the combo box.
Me.cboLastNameFind.SetFocus
Me.cboLastNameFind.SelStart = Len(Me.cboLastNameFind.Text)
End Sub

The problem seems to be with Me.Form.Filter = "[LastName] = '" & (at least, that is what is highlighted with the error message. Any thoughts and fixes most welcome.

Thanks, Simon

Upvotes: 0

Views: 345

Answers (2)

Cahaba Data
Cahaba Data

Reputation: 622

Not sure if you are aware; finding records in an MS Access form by looking for the LastName of a person is a standard feature of the Combobox control. When you drag it into a form, the wizard pop up gives you look up options - one of which is to find a record for the form from its bound record source.

Upvotes: 0

Erik A
Erik A

Reputation: 32642

Apparently, that page you referred to contains multiple errors induced by a text processor (a RIGHT SINGLE QUOTE or LEFT SINGLE QUOTE where one should use an apostrophe, and a double quote where there should be two single quotes).

One should never copy-paste code to and from a text processor like Word. Apparently Microsoft made this error.

The corrected code is as follows:

Private Sub cboLastNameFind_Change()
' If the combo box is cleared, clear the form filter.
If Nz(Me.cboLastNameFind.Text) = "" Then
 Me.Form.Filter = ""
 Me.FilterOn = False

' If a combo box item is selected, filter for an exact match.
' Use the ListIndex property to check if the value is an item in the list.
ElseIf Me.cboLastNameFind.ListIndex <> -1 Then
Me.Form.Filter = "[LastName] = '" &
Replace(Me.cboLastNameFind.Text, "'", "''") & "'"
Me.FilterOn = True

' If a partial value is typed, filter for a partial company name match.
Else
    Me.Form.Filter = "[LastName] Like '*" & _
                 Replace(Me.cboLastNameFind.Text, "'", "''") & "*'"
Me.FilterOn = True

End If

' Move the cursor to the end of the combo box.
Me.cboLastNameFind.SetFocus
Me.cboLastNameFind.SelStart = Len(Me.cboLastNameFind.Text)
End Sub

Upvotes: 1

Related Questions