Debug find box in form to ignore apostrophes in names. Eg: O'Neil.

[Guest]

I have set up a "Find" box in a data entry form to search for existing
customer names. If a name contains an apostrophe, such as O'Neill or Zeke's,
I recieve a runtime error 3077. I don't speak VBA and am hoping that there is
a simple solution short of renaming my customers.

 

[Guest]

No, this is not simple new year trix. You have to change the VBA code. Do you
know how it is? Post us a sample of it.
Enjoy,
Arni Laugdal MMI
www.til.is/msaccess

 

[Guest]

Here is the code from the debugger. The line
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
is highlighted

Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks for the prompt response. Hoping you can help.
Ken Paulson

 

[Marshall Barton]

Let's analyze this a bit. After the concatenation take
place the search condition will look like:

[CustName = 'O'Brian'

which obviously contains uunbalanced apostrophes.

In many situations involving names, you can avoid this issue
by using quotes instead of apostrophes:

rs.FindFirst "[CustName] = """ & Me![Combo20] & """"

that results in the search condition:

[CustName = "O'Brian"

The robust approach is to double up any occurances of the
outer delimiter character that may be contained in the name.
(Be careful to note the " and the ' with all this stuff.)

rs.FindFirst "[CustName] = '" _
& Replace(Me![Combo20], "'", "''") & "'"

which will result in this search condition:

[CustName = 'O''Brian'
--
Marsh
MVP [MS Access]

Here is the code from the debugger. The line
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
is highlighted

Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Guest]

Marsh;
Wow. The first solution did the trick.
I don't know how yuz done it, but I know yuz done it.
My thanks.
KP

Let's analyze this a bit. After the concatenation take
place the search condition will look like:

[CustName = 'O'Brian'

which obviously contains uunbalanced apostrophes.

In many situations involving names, you can avoid this issue
by using quotes instead of apostrophes:

rs.FindFirst "[CustName] = """ & Me![Combo20] & """"

that results in the search condition:

[CustName = "O'Brian"

The robust approach is to double up any occurances of the
outer delimiter character that may be contained in the name.
(Be careful to note the " and the ' with all this stuff.)

rs.FindFirst "[CustName] = '" _
& Replace(Me![Combo20], "'", "''") & "'"

which will result in this search condition:

[CustName = 'O''Brian'
--
Marsh
MVP [MS Access]

Here is the code from the debugger. The line
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
is highlighted

Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

:
I have set up a "Find" box in a data entry form to search for existing
customer names. If a name contains an apostrophe, such as O'Neill or Zeke's,
I recieve a runtime error 3077. I don't speak VBA and am hoping that there is
a simple solution short of renaming my customers.

 

[Guest]

Happy new year,
Yes, Marsh is known for good replies.
Best regards,
Arni Laugdal.

Marsh;
Wow. The first solution did the trick.
I don't know how yuz done it, but I know yuz done it.
My thanks.
KP

Let's analyze this a bit. After the concatenation take
place the search condition will look like:

[CustName = 'O'Brian'

which obviously contains uunbalanced apostrophes.

In many situations involving names, you can avoid this issue
by using quotes instead of apostrophes:

rs.FindFirst "[CustName] = """ & Me![Combo20] & """"

that results in the search condition:

[CustName = "O'Brian"

The robust approach is to double up any occurances of the
outer delimiter character that may be contained in the name.
(Be careful to note the " and the ' with all this stuff.)

rs.FindFirst "[CustName] = '" _
& Replace(Me![Combo20], "'", "''") & "'"

which will result in this search condition:

[CustName = 'O''Brian'
--
Marsh
MVP [MS Access]

Here is the code from the debugger. The line
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
is highlighted

Private Sub Combo20_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustName] = '" & Me![Combo20] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

:
I have set up a "Find" box in a data entry form to search for existing
customer names. If a name contains an apostrophe, such as O'Neill or Zeke's,
I recieve a runtime error 3077. I don't speak VBA and am hoping that there is
a simple solution short of renaming my customers.