Special characters in Long Text box (1 Viewer)

[Zydeceltico]

Hi All,

I have a form that has a text box that is meant for inspection notes. When I try to save out of the form I get an error message whenever the notes include a hyphen, comma, etc. This is a problem as it is routinely necessary to add a fractional dimension in the notes (e.g., 1/4").

How can I resolve this?

Thanks,

Tim

 

[Isaac]

Do you have code in the form's BeforeUpdate event?

 

[Zydeceltico]

Do you have code in the form's BeforeUpdate event?

no

 

[theDBguy]

When I try to save out of the form...

How exactly do you "save out of the form?"

 

[Isaac]

What dbGuy said.

 

[Zydeceltico]

How exactly do you "save out of the form?"

Private Sub cmdSaveClose_Click()
   ' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.
    ' the SQL portion of this procedure updates various fields in tblInspectionEvent

   Dim ctl As Control
   Dim strSQL As String
 
    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Me.txtNotes & "' , " & _
        " Photos = '" & Me.txtLinkToPhotos & "' " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
       
        Debug.Print strSQL
       
   On Error GoTo Err_BeforeUpdate

But I don't have any code for the BeforeUpdate event associated with this form.

 

[Zydeceltico]

Private Sub cmdSaveClose_Click()
   ' This procedure checks to see if the data on the form has
   ' changed. If the data has changed, the procedure prompts the
   ' user to continue with the save operation or to cancel it. Then
   ' the action that triggered the BeforeUpdate event is completed.
    ' the SQL portion of this procedure updates various fields in tblInspectionEvent

   Dim ctl As Control
   Dim strSQL As String

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Me.txtNotes & "' , " & _
        " Photos = '" & Me.txtLinkToPhotos & "' " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
      
        Debug.Print strSQL
      
   On Error GoTo Err_BeforeUpdate

But I don't have any code for the BeforeUpdate event associated with this form.

This is triggered by clicking a "Save" button.

 

[theDBguy]

strSQL = "UPDATE tblInspectionEvent " & _
" SET Notes = '" & Me.txtNotes & "' , " & _
" Photos = '" & Me.txtLinkToPhotos & "' " & _
" WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"

Try changing that part to this:

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Replace(Me.txtNotes,"'","''") & "' , " & _
        " Photos = '" & Me.txtLinkToPhotos & "' " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"

 

[Isaac]

Quotation marks in string expressions | Microsoft Docs

 

[Zydeceltico]

Try changing that part to this:

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Replace(Me.txtNotes,"'","''") & "' , " & _
        " Photos = '" & Me.txtLinkToPhotos & "' " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"

That worked. Wow - A LOT of quotation marks! lol..... I don't understand the second set ( " ' ' " ).

 

[theDBguy]

That worked. Wow - A LOT of quotation marks! lol..... I don't understand the second set ( " ' ' " ).

That's basically saying "double up any single quote in the notes."

 

[Zydeceltico]

That's basically saying "double up any single quote in the notes."

Thank you - again!

 

[jdraw]

Hi Tim
Long time since I've seen you in the forum. Hope all is well.

 

[theDBguy]

Thank you - again!

You're welcome! Glad we could assist. Good luck with your project.

 

[Zydeceltico]

Hi Tim
Long time since I've seen you in the forum. Hope all is well.

Hi jdraw! Yep - all is well....and getting better all the time. Hope the same for you!

 

[Zydeceltico]

You're welcome! Glad we could assist. Good luck with your project.

Thx! It's going really well finally. Now I'm just working through the minor kinks. We've been using the DB for a year now. I initially wanted to use it on two rugged tablets in order to collect data. The owner is so excited about having usable information rather than just recorded numerical dimensional data that he wants us to have 5 tablets and for me to expand the db to include our other plant.

And I couldn't have done any of it without ya'll on this forum! Thanks!