470,631 Members | 2,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

Combo Box Problem when Apostrophe in Text String

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
Nov 12 '05 #1
2 7903
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

Nov 12 '05 #2

"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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Mike L | last post: by
6 posts views Thread by Ron L | last post: by
5 posts views Thread by Thomas Beyerlein | last post: by
1 post views Thread by Rose | last post: by
7 posts views Thread by Rebecca Smith | last post: by
9 posts views Thread by Thomas 'PointedEars' Lahn | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.