Error 2001 When using VBA for filtering Records

[J. B.]

Hi all,

I have been trying to use VBA to filter records on a form. I keep
recieving the run time error 2001 "You have canceled the last operation".
Here is the code that I am using:

Private Sub cboCompanyName_AfterUpdate()
If IsNull(Me.cboCompanyName) Then
Me.FilterOn = False
Else
Me.Filter = "Cust_ID = """ & Me.cboCompanyName & """"
Me.FilterOn = True
End If
End Sub

I recieve the same error is I was to use this syntax:
Me.Filter = "tblCustomer.Cust_ID = """ & Me.cboCompanyName & """"

The combo box is using a query that displays the Customers Name in the first
column and is bound by the second column which is the Cust_ID value. I
don't know if this has anything to do with the error.

Any help would be much apreciated.

Thanks in advance,

J. B.

 

[Allen Browne]

You must supply the appropriate data type to the filter expression.

If you open your table in Design view, what kind of field is "Cust_ID"?
Number, or Text?

Which is the Bound column of the combo? Based on the combo's RowSource, what
field is in that column? Is it a Number or a Text type? The Bound column
must be the same data type as the Cust_ID field.

Assuming that both are numeric, drop the additional quotes, i.e.:
Me.Filter = "Cust_ID = " & Me.cboCompanyName


Even if the filter string is correctly formed, you could also get this
message if the form is dirty with edits that cannot be saved. Add this to
the top of your event procedure (i.e. above the "If IsNull(... " line):
If Me.Dirty Then
Me.Drity = False
End If