Apostrophe issue - unbound field insert into statement

[cballew]

Hi All so below is my code I am currently using. I'm struggling to make an apostrophe allowable in the comments field. I thought I knew how to fix by adding a " ' " but it will only work if only one apostrophe is used, if two are used it errors out. Any help is much appreciated!

Private Sub btnSave_Click()
Dim strprovidername As String
If Trim(Me.ContactType.Value & vbNullString) = vbNullString Then
MsgBox "Please select the contact type"
ElseIf Trim(Me.Provider.Value & vbNullString) = vbNullString Then
MsgBox "Please select a provider"
ElseIf Trim(Me.Subject.Value & vbNullString) = vbNullString Then
MsgBox "Please select the subject for this communication"
ElseIf Trim(Me.OpenedBy.Value & vbNullString) = vbNullString Then
MsgBox "Please enter your name in the opened by field"
ElseIf Trim(Me.OpenedDate.Value & vbNullString) = vbNullString Then
MsgBox "Please enter the opened date"
ElseIf Trim(Me.AssignedTo.Value & vbNullString) = vbNullString Then
MsgBox "Please enter the name of the staff member who should be assigned this contact"
ElseIf Trim(Me.Status.Value & vbNullString) = vbNullString Then
MsgBox "Please select a current status for this contact"

Else
CurrentDb.Execute "Insert into tblPRCalls(ContactType, Provider, OpenedBy, OpenedDate, Assignedto, ResolvedDate, Status, Subject, Comments, ProactiveOutreach) Values('" & Me.ContactType & "','" & Me.Provider & "','" & Me.OpenedBy & "','" & Me.OpenedDate & "','" & Me.AssignedTo & "','" & Me.ResolvedDate & "','" & Me.Status & "','" & Me.Subject & "','" & Me.Comments & "','" & Me.ProactiveOutreach & "')"
MsgBox "Record Save", vbInformation, "Success"
'start
strprovidername = "O:\KPI-Tracker\Trackers\PR Unit\New Test\Provider" & Me.Provider & ""
If ContactType = "In-Person" Then
On Error Resume Next
makefolder.makefolder ("O:\KPI-Tracker\Trackers\PR Unit\New Test\Provider" & Me.Provider)
On Error GoTo 0
DoCmd.OutputTo acOutputForm, "frmPRContactLog", acFormatPDF, strprovidername & Format(Date, "mmddyy") & " - " & [Forms]![frmPrContactLog]![Provider] & " Visit Record.pdf", False
MsgBox "Provider Visit log saved in provider folder!"
End If


'end
Me.ContactType.Value = "Call"
Me.Provider.Value = ""
Me.OpenedDate.Value = Date


Me.AssignedTo.Value = ""
Me.ResolvedDate.Value = ""
Me.Subject.Value = ""
Me.Comments.Value = ""
Me.ProactiveOutreach.Value = No

End If
End Sub

[orange]

In plain, simple English please tell us what you are trying to do. That's a lot of vba, but it isn't clear what you want to do.
Do you have some sample data that highlights your issue?

[cballew]

I actually found another thread on here that was able to help me out. Basically I needed to be able to do the insert function with a field that could or could not have multiple apostrophes. The replace function was the trick ie. replace(me.comments.value, "'", "''") was my solution.

[John_G]

I'm struggling to make an apostrophe allowable in the comments field.

You get the error because the comments field is already delimited by single quotes, and when you put an apostrophe in the comments, Access thinks it is the end of the field and the rest of the string generates a syntax error.

You can get around that by delimiting the comments with double quotes (see below), but then you can't use double-quotes in the comment itself. If you know the comments will not contain double-quotes, you can use this:

CurrentDb.Execute "Insert into tblPRCalls(ContactType, Provider, OpenedBy, OpenedDate, Assignedto, ResolvedDate, Status, Subject, Comments, ProactiveOutreach) Values('" & Me.ContactType & "','" & Me.Provider & "','" & Me.OpenedBy & "','" & Me.OpenedDate & "','" & Me.AssignedTo & "','" & Me.ResolvedDate & "','" & Me.Status & "','" & Me.Subject & "',""" & Me.Comments & """,'" & Me.ProactiveOutreach & "')"

If comments can contain double-quotes, or double-quotes and apostrophes at the same time, it's a bit more complicated, but doable.