receiving error 3075: syntax error for combo box linked to text box

Question

I keep getting this error whenever I try to run my form,

And the error:

The idea of this is to choose a field, enter in the text and have it pull the records base on the text input and it will say "no records found" if there is no match. Still a VBA novice, any help would be appreciated. The code is below. cboField is the combobox and txtBox is the textbox. I'm running Access 2010 for reference.

Option Compare Database

Private Sub cboField_Enter()

   Dim oRS As DAO.Recordset, i As Integer
   If Me.Form.FilterOn = True Then DoCmd.ShowAllRecords

   Set oRS = Me.RecordsetClone

   cboField.RowSourceType = "Value List"
   cboField.RowSource = ""

   For i = 0 To oRS.Fields.Count - 1
      If oRS.Fields(i).Type = dbText Then cboField.AddItem oRS.Fields(i).Name
   Next i

End Sub

Private Sub txtBox_Exit(Cancel As Integer)

   Dim sfilter As String, oRS As DAO.Recordset
   If IsNull(cboField) Then
      DoCmd.ShowAllRecords
      MsgBox "select a field"
      Exit Sub
   End If

   If IsNull(txtBox) Then DoCmd.ShowAllRecords: Exit Sub

   sfilter = cboField & "LIKE '" & txtBox & " *'"
   DoCmd.ApplyFilter , sfilter

   Set oRS = Me.RecordsetClone

   If oRS.RecordCount = 0 Then
      MsgBox " no record matches"
      DoCmd.ShowAllRecords
   End If

End Sub

whenever I run debug it highlights docmd.applyfilter, sfitler. Are there extra parameters needed here maybe ? — user8807264, Nov 07 '17 at 18:14
^^ "Do... or do not. There is no try" - and in this case ... "Do". The missing space will be causing that message - look at the message closely and try to find the operator in the three tokens `FirstNameLIKE`, `joe` and `*`. — YowE3K, Nov 07 '17 at 18:37
I added the space before, and that fixed the error, however, now when i select a field and enter in a name, and click outside of the textbox, it returns my "no records found message" when I enter joe for the firstname. is the wildcard place awkwardly in the filter statement ? here is a capture of the entered data against the vba : sfilter = "FirstName LIKE 'joe *'". — user8807264, Nov 07 '17 at 19:45
^^ "Have or don't have. There is no probably" - and in this case... "Don't have". Yoda could have said that. — itsLex, Nov 07 '17 at 22:00

score 0 · Answer 1 · answered Nov 07 '17 at 22:55

0

... Avoid answering questions in comments.

Anyway. When building SQL strings in VBA, Debug.Print and reading the output closely are your friends. How to debug dynamic SQL in VBA

Debug.Print sfilter

FirstNameLIKE 'joe *'

Watch for missing and superfluous spaces, make that

sfilter = cboField & " LIKE '" & txtBox & "*'"

answered Nov 07 '17 at 22:55

Andre

26,751
7
36
80

Questions are answered in comments because: 1. Answerer is not sure if they have a correct answer, 2. Answerer is not interested in being downvoted for posting an answer that someone doesn't like. I do it all the time, – nicomp Nov 08 '17 at 14:00

score 0 · Answer 2 · answered Nov 08 '17 at 13:35

It is working now, thanks to all for the help, I have posted the source with the edits.

Option Compare Database

Private Sub cboField_Enter()
Dim oRS As DAO.Recordset, i As Integer
If Me.Form.FilterOn = True Then DoCmd.ShowAllRecords
Set oRS = Me.RecordsetClone
cboField.RowSourceType = "Value List"
cboField.RowSource = ""

For i = 0 To oRS.Fields.Count - 1
If oRS.Fields(i).Type = dbText Then cboField.AddItem oRS.Fields(i).Name
 Next i

 End Sub

 Private Sub txtBox_Exit(Cancel As Integer)
 Dim sfilter As String, oRS As DAO.Recordset
 If IsNull(cboField) Then
 DoCmd.ShowAllRecords
 MsgBox "select a field"
 Exit Sub
 End If
 If IsNull(txtBox) Then DoCmd.ShowAllRecords: Exit Sub
 sfilter = cboField & " LIKE '" & txtBox & "*'"
 DoCmd.ApplyFilter , sfilter
 Debug.Print sfilter


 Set oRS = Me.RecordsetClone
 If oRS.RecordCount = 0 Then
 MsgBox " no record matches"
 DoCmd.ShowAllRecords
 End If

 End Sub

receiving error 3075: syntax error for combo box linked to text box

2 Answers2