Single quotes problem. Please help!!

[av]

Hi,

I'm having a problem with an unbound combo box, that is
used to look up the name of an organization. One of the
organizations has a single quote in its name, and this is
giving me an error. I have the following code for the
combo box:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProgramName] = '" & Me!
[cboProgram_frmProgram] & "'"
Me.Bookmark = rs.Bookmark
End Sub

The name of the Organization is "Women's Health". I
believe the single quote (') in the name is causing the
error. How can I fix this?

Thanks!

~~av

 

[Sandra Daigle]

Instead of using the single quote as your embedded string delimiter you can
use a pair of double quotes. These will be replaced with a single double
quote character and therefore not conflict with the single quote in your
data:

rs.FindFirst "[ProgramName] =""" & Me![cboProgram_frmProgram] & """"

Note that there are 3 double quotes (") before the first & and 4 after the
last &.

You could also use calls to chr(34):

rs.FindFirst "[ProgramName] =" & chr(34) & me![cboProgram_frmProgram] &
chr(34)

 

[Dirk Goldgar]

Hi,

I'm having a problem with an unbound combo box, that is
used to look up the name of an organization. One of the
organizations has a single quote in its name, and this is
giving me an error. I have the following code for the
combo box:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProgramName] = '" & Me!
[cboProgram_frmProgram] & "'"
Me.Bookmark = rs.Bookmark
End Sub

The name of the Organization is "Women's Health". I
believe the single quote (') in the name is causing the
error. How can I fix this?

Change it to

rs.FindFirst "[ProgramName] = " & _
Chr(34) & Me![cboProgram_frmProgram] & Chr(34)

That will use the double-quote character -- Chr(34) -- as the string
constant delimiter in the find-criterion, so the embedded single-quote
will be interpreted correctly as part of the text to be found. On the
other hand, if the [ProgramName] field might contain a double-quote
character, this approach isn't good enough because you'll have the same
problem with that character. If that's a concern, you can "double up"
any occurrences of the quote character inside the string being sought,
using the Replace function:

rs.FindFirst "[ProgramName] = " & _
Chr(34) & _
Replace(Me![cboProgram_frmProgram], _
Chr(34),
Chr(34) & Chr(34)) & _
Chr(34)

 

[av]

Thanks so much, for both the replies. I tried it and it
worked!!! You guys are awesome!!

~~av

-----Original Message-----
Instead of using the single quote as your embedded string delimiter you can
use a pair of double quotes. These will be replaced with a single double
quote character and therefore not conflict with the single quote in your
data:

rs.FindFirst "[ProgramName] =""" & Me! [cboProgram_frmProgram] & """"

Note that there are 3 double quotes (") before the first & and 4 after the
last &.

You could also use calls to chr(34):

rs.FindFirst "[ProgramName] =" & chr(34) & me! [cboProgram_frmProgram] &
chr(34)


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi,

I'm having a problem with an unbound combo box, that is
used to look up the name of an organization. One of the
organizations has a single quote in its name, and this is
giving me an error. I have the following code for the
combo box:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProgramName] = '" & Me!
[cboProgram_frmProgram] & "'"
Me.Bookmark = rs.Bookmark
End Sub

The name of the Organization is "Women's Health". I
believe the single quote (') in the name is causing the
error. How can I fix this?

Thanks!

~~av

.