Combo lookup box returning error

[jhicsupt]

I have a combo lookup box that returns an error whenever I try to search for
a customer with an " ' " in the name, in other word, "ABC's" will return an
error, however "ABCs" will not. The error message is "syntax error (missing
operator) in expression. Error 3077.

Is there any way around this or do I need to change all the customers with
an " ' " in it to not have an " ' "?

Thanks in advance.

 

[John W. Vinson]

I have a combo lookup box that returns an error whenever I try to search for
a customer with an " ' " in the name, in other word, "ABC's" will return an
error, however "ABCs" will not. The error message is "syntax error (missing
operator) in expression. Error 3077.

Is there any way around this or do I need to change all the customers with
an " ' " in it to not have an " ' "?

Thanks in advance.

No; instead just delimit the search with " rather than with '. Perhaps you
could post the VBA code in your combo's AfterUpdate event so we can help with
the correction.

Basically you need an expression like

strCriteria = "[CustName] = """ & Me!comboboxname & """"

instead of

strCriteria = "[CustName] = '" & Me!comboboxname & "'"

Using two consecutive " characters in a string delimited by " will give a
single " as a result. A criterion like

[CustName] = "ABC's"

will work, whereas

[CustName] = 'ABC's'

will fail, inasmuch as the apostrophe before the s will prematurely terminate
the string.

 

[Ken Sheridan]

When searching by the customer name using double rather than single quotes
characters as the delimiter, as John explains, will do the tick – assuming
that is that you have no customers with double quotes in their name.
However, using a name as a key is best avoided as they can be duplicated.
This is less likely with company names than personal names of course, but
still theoretically possible, and consequently its better to cater for this
by having a 'surrogate' numeric key such as CustomerID. This can then be the
value of a combo box, but hidden so that it shows the corresponding names by
setting up the control's properties like this:

RowSource: SELECT CustomerID, Customer FROM Customers ORDER BY Customer;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Code in the combo box's AfterUpdate event procedure to find the selected
customer would then be along these lines:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "CustomerID = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Ken Sheridan
Stafford, England