Content of variable crashes VBA code. (1 Viewer)

[AdrianThorn]

I have a form I use to create photo captions. The form has a field where users can edit a pre-generated caption and a field that stores their updates as a final caption in the field "selected_caption".. On my form is a button that runs the code below to check and see if a field ("updated") has been marked true, then copies the content of caption field into the selected_caption field. It works fine 90 percent of the time but for some reason this caption:

LA 109535, LA109535 partial site overview showing blowout with PL's 01- 04. Crew person is excavating a shovel test probe. Photo direction facing southwest. (Roll 31066, Frame 4958)

Results in a missing operator error. I thought it was like a special character thing, so I removed the "'" and the "-". Same error. Any ideas what is going on?

Dim db As Database
Dim rst As Recordset
Dim upcap As String
Dim strSQL As String


Set db = CurrentDb
strSQL = "SELECT * from alb_cr_nm_site_photo_captions WHERE nm_site_id = '" & [nm_site_id] & "'"
Set rst = db.OpenRecordset(strSQL)

While Not rst.EOF
If rst!updated = False Then
upcap = "UPDATE alb_cr_nm_site_photo_captions SET selected_caption = '" & rst!caption & "' WHERE swca_photo_id = '" & rst!swca_photo_id & "'"
DoCmd.RunSQL upcap
rst.MoveNext
Else
rst.MoveNext
End If
Wend
Me.Refresh

 

[Gasman]

Does any other caption have a ' in it?

 

[The_Doc_Man]

You have a quote-mark (single-quote or apostrophe) in the text AND you are using syntax in which you use single-quote inside of a double-quoted string to delimit text.

"SELECT * from alb_cr_nm_site_photo_captions WHERE nm_site_id = '" & [nm_site_id] & "'"

Probably doesn't like the section of the string that contains

showing blowout with PL's 01- 04.

because that embedded quote terminates the input string.

 

[AdrianThorn]

That makes sense. Whats a work around, beyond changing the actual content of the string?

 

[vbaInet]

That makes sense. Whats a work around, beyond changing the actual content of the string?

1. Create an UPDATE query
2. Add a reference to the [nm_site_id] textbox in the query
3. Add the reference to the Parameters list
4. Execute the query in this format (aircode):

dim qdf as dao.querydef

set qdf = currentdb.querydefs("name of query")

with qdf
    .parameters(0) = me.txtNm_site_id
    .execute dbfailonerror
end with

set qdf = nothing