Text Strings that Contain an Apostrophe

[CSDunn]

Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box, if any
part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when there is
an apostrophe in either the first or last name of the student?

Thanks for your help!

CSDunn

 

[Steve Schapel]

CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP

 

[CSDunn]

Steve,
Thanks for your help, but the suggestion below did not work. After trying
your suggestion, and a few variations of it, any selection from the combo
box resulted in the error I mentioned before. I tried to SET
QUOTED_IDENTIFIER OFF in the View of the main form, but this did not help.

I've been given the go ahead to replace any name with an apostrophe with a
space.

Thanks again!

CSDunn

CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP

Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box, if any
part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when there is
an apostrophe in either the first or last name of the student?

Thanks for your help!

CSDunn

 

[Ken Snell]

You could use a function to automatically replace the apostrophes with two
apostrophes.

Put this function in a regular module:
Public Function SingleQDouble(ByVal xstrReplaceStringValue) As String
'***THIS FUNCTION CONVERTS ONE SINGLE-QUOTE CHARACTER INTO TWO SINGLE-QUOTE
'***CHARACTERS IN A TEXT STRING.

' xstrReplaceStringValue is string variable that contains the text string
that
' needs to be converted

SingleQDouble = Replace(xstrReplaceStringValue, "'", "''")
End Function


Then change your code to this:
rs.Find "[StudentName] = '" & SingleQDouble(Me![Combo22]) & "'"


You then can use this function anytime to always convert text strings with a
single apostrophe into double ones, thereby properly handling the text
string.
--
Ken Snell
<MS ACCESS MVP>

Steve,
Thanks for your help, but the suggestion below did not work. After trying
your suggestion, and a few variations of it, any selection from the combo
box resulted in the error I mentioned before. I tried to SET
QUOTED_IDENTIFIER OFF in the View of the main form, but this did not help.

I've been given the go ahead to replace any name with an apostrophe with a
space.

Thanks again!

CSDunn

CSDunn,

Try it like this...
"[StudentName] = " & """" & Me![Combo22] & """"
(that's 4 "s)

- Steve Schapel, Microsoft Access MVP

Hello,
I have a combo box designed to look up records in a subform based on the
selection made in the combo box. The Record Source for the combo box is a
SQL Server 2000 View. There is one bound column for the combo box called
'StudentName', and is a concatenated field comprised of
'LastName','FirstName'. These two fields are both the same datatype,
'nvarchar'.

I have run into a problem when either the 'FirstName' or 'LastName' contains
an apostrophe. Basically, when I make a selection from the combo box,

if

any

part of the name contains an apostrophe, I get the following message:
*********************
Run-time error '3001':
Arguments are of the wrong type, are our of acceptable range, or are in
conflict with one another.
*********************

The code for the AfterUpdate event of the combo box is as follows:
*******************
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[StudentName] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
*******************

The error message points to; rs.Find "[StudentName] = '" & Me![Combo22] &
"'"

How can I change the AfterUpdate code to handle 'StudentName' when

there

is

 

[Steve Schapel]

Sorry, CS. The method I suggested works fine with Access, and is
standard procedure. So I am very surprised to learn that it didn't
help in your case. But I am afraid I don't know anything about how
SQL Server handles stuff like this.

- Steve Schapel, Microsoft Access MVP