Prev: Pass a String Var to a Function
Next: read focus of controls
From: Steve Sanford limbim53 at yahoo dot on 2 Jun 2010 08:46
Add this to a standard module:

Function ConvertQuotesSingle(InputVal)
ConvertQuotesSingle = Replace(InputVal, "'", "''")
End Function


Then modify your code to:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[Name] = '" & ConvertQuotesSingle(Me![Combo80]) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


BTW, "Name is a reserved word in Access and shouldn't be used for object
names. Also, it is not descriptive.... name of what??? A company, the dog,
street...?????


--
HTH
---
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Nigel" wrote:

> I use a control on a form which I have concatenated from the users First and
> last names. All works well if I select, Harry Smith, but when I select a name
> with an apostrophe in it, Kelly O'Malley, I get a run time error 3077. The
> coding is:
>
> Private Sub Combo80_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[Name] = '" & Me![Combo80] & "'"
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> End Sub
>
> Any suggestions?
>
> Thanks
From: Stefan Hoffmann on 2 Jun 2010 09:08
hi Steve,

On 02.06.2010 14:46, Steve Sanford wrote:
> Add this to a standard module:
>
> Function ConvertQuotesSingle(InputVal)
> ConvertQuotesSingle = Replace(InputVal, "'", "''")
> End Function
I use this function:

Public Function SqlQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SqlQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

Makes it less error prone as you don't need the enclosing quotes while
building a SQL or filter statement:
> rs.FindFirst "[Name] = '"& ConvertQuotesSingle(Me![Combo80])& "'"

rs.FindFirst "[Name] = " & SqlQuote(Combo80.Value)


mfG
--> stefan <--
From: Nigel on 2 Jun 2010 11:55
Hi Steve

Many thanks, works a treat, just what I wanted. Also, thanks for the advice
regarding "Names"

Thanks

"Steve Sanford" wrote:

> Add this to a standard module:
>
> Function ConvertQuotesSingle(InputVal)
> ConvertQuotesSingle = Replace(InputVal, "'", "''")
> End Function
>
>
> Then modify your code to:
>
> Private Sub Combo80_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[Name] = '" & ConvertQuotesSingle(Me![Combo80]) & "'"
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> End Sub
>
>
> BTW, "Name is a reserved word in Access and shouldn't be used for object
> names. Also, it is not descriptive.... name of what??? A company, the dog,
> street...?????
>
>
> --
> HTH
> ---
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
>
>
> "Nigel" wrote:
>
> > I use a control on a form which I have concatenated from the users First and
> > last names. All works well if I select, Harry Smith, but when I select a name
> > with an apostrophe in it, Kelly O'Malley, I get a run time error 3077. The
> > coding is:
> >
> > Private Sub Combo80_AfterUpdate()
> > ' Find the record that matches the control.
> > Dim rs As Object
> >
> > Set rs = Me.Recordset.Clone
> > rs.FindFirst "[Name] = '" & Me![Combo80] & "'"
> > If Not rs.EOF Then Me.Bookmark = rs.Bookmark
> > End Sub
> >
> > Any suggestions?
> >
> > Thanks
 | 
Pages: 1
Prev: Pass a String Var to a Function
Next: read focus of controls