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

user_8807264 0
Viewed 110

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

screenshot of form

And the error:

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
ms-access vba
2 Answers

... 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 & "*'"
user_38202710

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
user_88072640
Related