Runtime Error 3075

[puniverse]

Any help would be highly appreciated. I am getting the following error when I erase the text and type a letter. I want to be able to type a letter and it takes me to the list that starts with the letter. I can select the drop down and select the customer and it works but I get the error when I select the first letter.

Run-time error '3075':
Syntax error (missing operator) in query expression 'Customer ='.

Private Sub CustomerCombo_Change()

'If the combo box is cleared, clear the form filter.
  If Nz(Me.CustomerCombo.Text) = “” Then
    Me.Form.Filter = “”
    Me.FilterOn = False
     Else: Me.Filter = "Customer = " & Me.CustomerCombo
        DoCmd.RunCommand acCmdApplyFilterSort
            
  End If
End Sub

 

[sneuberg]

Edit: Sorry misread the post. You still might want to do this.


Probably being caused by Auto Correct which will cause two change events when encountering "i". Suggest setting Allow Autocorrect to No. It's on the Other tab in the textboxes properties.

 

[sneuberg]

Try this code

Private Sub CustomerCombo_Change()

Dim strTextBox As String
'If the combo box is cleared, clear the form filter.
If Nz(Me.CustomerCombo.Text) = "" Then
    Me.Form.Filter = ""
    Me.FilterOn = False
Else
    strTextBox = Me.CustomerCombo.Text
    Me.Filter = "Customer like '*" & ESQ(strTextBox) & "'"
    Me.FilterOn = True
    Me.CustomerCombo = strTextBox
    Me.CustomerCombo.SelStart = Len(strTextBox)
End If

End Sub

'function to escape single quotes
Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

 

[puniverse]

sneuberg,

Your code does not give the error but it also does not go to the section of the list and the filter not longer filters.

 

[puniverse]

I will make it easier. My code does exactly what I want except it is allowing me to erase what is in the drop down menu completely and thats when I get the error. Any way I can just not allow the customers text to be deleted.

 

[sneuberg]

I want to be able to type a letter and it takes me to the list that starts with the letter. I can select the drop down and select the customer and it works but I get the error when I select the first letter.]

When you say list do you mean the list in the combo box drop down. Do you want the combo to act like the combo in the attached database?

 

[Cronk]

OP wants list which starts with the letter

You probably meant to write
Me.Filter = "Customer like '" & ESQ(strTextBox) & "*'"

 

[puniverse]

yes thats what I need.

 

[sneuberg]

OP wants list which starts with the letter

You probably meant to write
Me.Filter = "Customer like '" & ESQ(strTextBox) & "*'"

Oops, I put the asterisk on the wrong side. I think I might have dyslexia

 

[puniverse]

Your code still does not filter the items anymore and also does not put the drop down to the first letter.

 

[sneuberg]

Your code still does not filter the items anymore and also does not put the drop down to the first letter.

That code may be ten miles from what you what to do? It was a shot in the dark. What do you want to happen. Do you want the combo to act like the combo in the database I attached to post #6. It might help if you upload your database

 

[puniverse]

here is the database. Please check it out. When I apply your code it does not filter. The form I use is Project list. click in the search customer and type a letter. Thats when I get the error.

 

[sneuberg]

The Customer in the Record Source for this form which in the Projects table is not a text field. It's a number ; the ID from the Customers Extended table. So I don't know of anyway to do what I think you are trying to do with this combo box.