Good morning all
I am hoping some can help... I have written the following code which allows a user to save notes on a phone call taken (for a CRM system). There is a 'notes' field (memo) which the user is free to type whatever they want in. I am having major problems if the user decides to use certain characters as they conflict with the SQL, e.g. quotation marks / speech marks etc.
Can anyone give me some help to find away around this?? Someone recommended parameter queries but I am not very familiar with these and dont really know where to start... :banghead:
Example of some code below that is involved in this procedure:-
Sub SaveCorrespondenceOnly()
Dim lsSQL As String
Dim lsFormatDate As String
' saves the correspondence record only - with no linking action
lsFormatDate = "#" & Format(Me.txtDate.Value, "DD-MM-YYYY") & "# "
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrType, Contact, DateofCorr, TimeofCorr, Notes, CreatedBy, CreatedWhen ) "
lsSQL = lsSQL & " VALUES ( " & 0 & ", " & ICompanyRef & ", '" & lstCorrType.Value & "', '" & CboContact.Value & "', " & lsFormatDate & ", #" & txtTime.Value & "#,'" & Replace(txtNotes.Value, "'", "`") & "', '" & SUser & "', #" & Now() & "#)"
CurrentDb.Execute lsSQL
I am hoping some can help... I have written the following code which allows a user to save notes on a phone call taken (for a CRM system). There is a 'notes' field (memo) which the user is free to type whatever they want in. I am having major problems if the user decides to use certain characters as they conflict with the SQL, e.g. quotation marks / speech marks etc.
Can anyone give me some help to find away around this?? Someone recommended parameter queries but I am not very familiar with these and dont really know where to start... :banghead:
Example of some code below that is involved in this procedure:-
Sub SaveCorrespondenceOnly()
Dim lsSQL As String
Dim lsFormatDate As String
' saves the correspondence record only - with no linking action
lsFormatDate = "#" & Format(Me.txtDate.Value, "DD-MM-YYYY") & "# "
lsSQL = ""
lsSQL = lsSQL & " INSERT INTO Tbl_Correspondence ( DiaryActionID, CompanyRef, CorrType, Contact, DateofCorr, TimeofCorr, Notes, CreatedBy, CreatedWhen ) "
lsSQL = lsSQL & " VALUES ( " & 0 & ", " & ICompanyRef & ", '" & lstCorrType.Value & "', '" & CboContact.Value & "', " & lsFormatDate & ", #" & txtTime.Value & "#,'" & Replace(txtNotes.Value, "'", "`") & "', '" & SUser & "', #" & Now() & "#)"
CurrentDb.Execute lsSQL