Combo Box Problem when Apostrophe in Text String

[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

[Larry Linson]

Try not using the single quote, which confuses the issue but doubling up on
double-quotes, as

"[StudentName]=""" & Me![Combo22] & """"

Larry Linson
Microsoft Access MVP

"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

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

[Luke Fair]

"CSDunn" <cd***@valverde.edu> wrote in message
news:80**************************@posting.google.c om...

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

Best place to get answers is at the official MVP site:
http://mvp.org
Luke Fair MVP