Notes field, quotations, apostrophes, and error messages (1 Viewer)

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
Hi All! It's been awhile. :) ....and now I'm in need of your expertise again.

The Manufacturing Quality Inspection DB has been performing well (even though I'm thinking of replicating it and modifying the design ...but that's a different discussion).

But yesterday, out of nowhere I began receiving a "Run-time error '94', Invalid Use of Null" when saving/closing a Notes form (frmNotesAndIssues) and I haven't changed any code at all (until an hour ago).

What I have is a primary inspection form frmInspectionEvent whose Record Source is tblInspectionEvent. This table includes fields like Date, Inspection Type, Job Number, etc......and Notes (long text datatype).

The workflow is that I (or one of two other inspectors) will open frmInspectionEvent and enter Job Number, Inspected By, Operator Name...but we do not enter Notes at this time. That occurs after the actual inspection. Then we choose which type of inspection we are going to perform by clicking the appropriate button on frmInspectionEvent.

There are actually several different individual inspection forms for different processes (each one has its own table as a record source) but the behavior I'm going to describe persists across all of the inspection type forms. For this explanation, I'll use frmWelding.

So...we enter basic data on frmInspectionEvent, then click the button named "Welding" that opens frmInspectWelding (frmInspectionEvent remains open in the background. This form (frmInspectWelding) is where we actually collect inspection data - but the "Save" button on frmInspectWelding is not enabled until the user clicks one more button on frmInspectWelding. That button is labelled "Notes and Issues" and it opens frmNotesAndIssues with several unbound controls for collecting more general data (frmInspectWelding also remains open in the background).

Then the user clicks save on frmNotesAndIssues which triggers the following code; closes frmNotesAndIssues; and Enables the "Save" button on frmInspectWelding."

Here's the current code that is triggered. The SET Notes line seems to be the problem child. That line is written that way in order to be able to type fractions in the notes field (e.g., 3/4" and/or 5/16") and sentences with apostrophes (e.g., I don't know why this isn't working?).

Code:
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
    ' more in line with the actual shop floor workflow rather than
    ' included on frmInspectionEvent
   Dim ctl As Control
   Dim strSQL As String

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = '" & Replace(Me.txtNotes, "'", "''") & "' , " & _
        " Photos = '#" & Me.txtLinkToPhotos & "' , " & _
        " OilCanning = '" & Me.cboCanningYesNo & "' , " & _
        " CanningStopLine = '" & Me.cboCanningLineStop & "' , " & _
        " CoatingIssues = '" & Me.cboCoatingIssueYesNo & "' , " & _
        " CoatingStopLine = '" & Me.cboCoatingIssueLineStop & "' , " & _
        " IssuesOther = '" & Me.cboOtherIssues.Column(1) & "' " & _
        " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
     
        Debug.Print strSQL
     
   On Error GoTo Err_BeforeUpdate

   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
         DoCmd.Close
      End If
      Else
        CurrentDb.Execute strSQL, dbFailOnError
        DoCmd.Close
     
    'MsgBox gFormName
 
    Select Case gFormName
        Case "frmInspectWelding"
            Forms!frmInspectWelding!cmdSaveWeldAssemble.Enabled = True ' now that notes, issues, and photos s have been entered frmInspectWelding can be saved.
        Case "frmInspectMill"
            Forms!frmInspectMill.cmdSaveMillInspection.Enabled = True
        Case "frmInspectAssemble"
            Forms!frmInspectAssemble.CmdSaveAssemble.Enabled = True
        Case "frmInspectFab"
            Forms!frmInspectFab.cmdSaveFabInspection.Enabled = True
        Case "frmInspectPaint"
            Forms!frmInspectPaint.cmdSaveInspectPaint.Enabled = True
    End Select
     
   End If



Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub

That's the entire code for the "Save and Close" button on frmNotesandIssues. The original code had the bolded line above as this:
Code:
        " SET Notes = '" & Me.txtNotes & "' , " & _

Using the original SET line, no error is thrown. Using longer SET Notes line above throws Runt-time error 94, Invalid Use of Null.
It is really important to also note that I have been using the more complex line above for months without issue. It is only in the past couple of weeks that I began receiving the error message and I don't know why. I haven't been in the back end modifying anything at all.

If I change the longer SET Notes line back to the simpler SET line, I don't receive the Null error message when typing fractions with commas. Instead, I receive an error message when I use an apostrophe in a sentence like: I'm not sure why this isn't working.

Thoughts?

Thanks,

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,359
Hi Tim. Can you also post the result of the Debug.Print?
 

SHANEMAC51

Active member
Local time
Today, 18:29
Joined
Jan 28, 2022
Messages
310
That's the entire code for the "Save and Close" button on frmNotesandIssues. The original code had the bolded line above as this:
first, check what Me.txtNotes
is, maybe it's null - an empty string
, try to work around it
Code:
Dim strSQL As String,s1 as string
s1=Me.txtNotes & ""
s1=Replace(s1, "'", "''")
debug.print s1
    strSQL = "UPDATE tbl Inspection Event " & _
        " SET Notes = '" & s1 & "' , " & _
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:29
Joined
May 7, 2009
Messages
19,175
you can use Querydef, to avoid such errors:
Code:
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
    ' more in line with the actual shop floor workflow rather than
    ' included on frmInspectionEvent
   Dim ctl As Control
   Dim strSQL As String

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = [prm00], " & _
        " Photos = [prm01] , " & _
        " OilCanning = [prm02] , " & _
        " CanningStopLine = [prm03], " & _
        " CoatingIssues = [prm04], " & _
        " CoatingStopLine = [prm05], " & _
        " IssuesOther = [prm06] " & _
        " WHERE InspectionEvent_PK = [prm07];"
       
    'strSQL = "UPDATE tblInspectionEvent " & _
    '    " SET Notes = '" & Replace(Me.txtNotes, "'", "''") & "' , " & _
    '    " Photos = '#" & Me.txtLinkToPhotos & "' , " & _
    '    " OilCanning = '" & Me.cboCanningYesNo & "' , " & _
    '    " CanningStopLine = '" & Me.cboCanningLineStop & "' , " & _
    '    " CoatingIssues = '" & Me.cboCoatingIssueYesNo & "' , " & _
    '    " CoatingStopLine = '" & Me.cboCoatingIssueLineStop & "' , " & _
    '    " IssuesOther = '" & Me.cboOtherIssues.Column(1) & "' " & _
    '    " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
   
        Debug.Print strSQL
   
   On Error GoTo Err_BeforeUpdate

   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
         DoCmd.Close
      End If
      Else
        'CurrentDb.Execute strSQL, dbFailOnError
        With CurrentDb.CreateQueryDef("", strSQL)
            .Parameters("prm00") = Me.txtNotes
            .Parameters("prm01") = "#" & Me.txtLinkToPhotos
            .Parameters("prm02") = Me.cboCanningYesNo
            .Parameters("prm03") = Me.cboCanningLineStop
            .Parameters("prm04") = Me.cboCoatingIssueYesNo
            .Parameters("prm05") = Me.cboCoatingIssueLineStop
            .Parameters("prm06") = Me.cboOtherIssues.Column(1)
            .Parameters("prm07") = Me.txtInspectionEvent_ID
           
            .Execute dbFailOnError
        End With
        DoCmd.Close
   
    'MsgBox gFormName

    Select Case gFormName
        Case "frmInspectWelding"
            Forms!frmInspectWelding!cmdSaveWeldAssemble.Enabled = True ' now that notes, issues, and photos s have been entered frmInspectWelding can be saved.
        Case "frmInspectMill"
            Forms!frmInspectMill.cmdSaveMillInspection.Enabled = True
        Case "frmInspectAssemble"
            Forms!frmInspectAssemble.CmdSaveAssemble.Enabled = True
        Case "frmInspectFab"
            Forms!frmInspectFab.cmdSaveFabInspection.Enabled = True
        Case "frmInspectPaint"
            Forms!frmInspectPaint.cmdSaveInspectPaint.Enabled = True
    End Select
   
   End If



Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
Hi Tim. Can you also post the result of the Debug.Print?
Hi DBGuy,

I just did a test comparison.

90% of the time there is no reason to enter anything in the Notes field so nothing is entered there. This has never been an issue before.

So I just ran through two inspections. The first one I wrote in the Notes field the following: I'm not sure what will happen next. 3/4" (Note the apostrophe and the double quotations).

The debug.print showed this in the Immediate window (and did not throw the error):
Code:
UPDATE tblInspectionEvent  SET Notes = 'I''m not sure what will happen next. 3/4"' ,  Photos = '#' ,  OilCanning = 'no' ,  CanningStopLine = 'N/A' ,  CoatingIssues = 'no' ,  CoatingStopLine = 'N/A' ,  IssuesOther = 'white rust'  WHERE InspectionEvent_PK = 1363 ;

I saved out and closed and did a second one and did not enter anything in the Notes field nor did I tab to it. And it threw the Run-time error 94, Invalid use of Null.

Again, this just started happening recently. I have not edited anything in the background or on this form or in the table.
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
Hi DBGuy,

I just did a test comparison.

90% of the time there is no reason to enter anything in the Notes field so nothing is entered there. This has never been an issue before.

So I just ran through two inspections. The first one I wrote in the Notes field the following: I'm not sure what will happen next. 3/4" (Note the apostrophe and the double quotations).

The debug.print showed this in the Immediate window (and did not throw the error):
Code:
UPDATE tblInspectionEvent  SET Notes = 'I''m not sure what will happen next. 3/4"' ,  Photos = '#' ,  OilCanning = 'no' ,  CanningStopLine = 'N/A' ,  CoatingIssues = 'no' ,  CoatingStopLine = 'N/A' ,  IssuesOther = 'white rust'  WHERE InspectionEvent_PK = 1363 ;

I saved out and closed and did a second one and did not enter anything in the Notes field nor did I tab to it. And it threw the Run-time error 94, Invalid use of Null.

Again, this just started happening recently. I have not edited anything in the background or on this form or in the table.
Sorry. I got interrupted and wrote the wrong sentence that input in the Notes field. I corrected it above.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,359
Sorry. I got interrupted and wrote the wrong sentence that input in the Notes field. I corrected it above.
But you said in the second inspection, you got an error. What was the result of the Debug.Print then?
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
But you said in the second inspection, you got an error. What was the result of the Debug.Print then?
Attached is what I get with the second condition.
 

Attachments

  • Capture.JPG
    Capture.JPG
    81.9 KB · Views: 229

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,359
Clicking Debug gives me this:
Oh, I get it. The Replace() function is throwing the error because it can't evaluate a Null. You'll have to use the Nz() function to handle that. For example:
Code:
" SET Notes = '" & Replace(Nz(Me.txtNotes,""), "'", "''") & "' , " & _
Or, use the QueryDef approach @arnelgp suggested.
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
Oh, I get it. The Replace() function is throwing the error because it can't evaluate a Null. You'll have to use the Nz() function to handle that. For example:
Code:
" SET Notes = '" & Replace(Nz(Me.txtNotes,""), "'", "''") & "' , " & _
Or, use the QueryDef approach @arnelgp suggested.
That fixed it! Thanks.

I need to read up on Nz and QueryDef. I'm not that familiar with either one.
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
That fixed it! Thanks.

I need to read up on Nz and QueryDef. I'm not that familiar with either one.
I am curious though: Why would the Nz or QueryDef not be required in the simpler SET Notes line of code? Or was it simply throwing the syntax error before it evaluated for Null?
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
I am curious though: Why would the Nz or QueryDef not be required in the simpler SET Notes line of code? Or was it simply throwing the syntax error before it evaluated for Null?
and, with the simpler SET Notes, it would not throw an error of any kind if I left the Notes field blank?
 

Zydeceltico

Registered User.
Local time
Today, 11:29
Joined
Dec 5, 2017
Messages
843
you can use Querydef, to avoid such errors:
Code:
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
    ' more in line with the actual shop floor workflow rather than
    ' included on frmInspectionEvent
   Dim ctl As Control
   Dim strSQL As String

    strSQL = "UPDATE tblInspectionEvent " & _
        " SET Notes = [prm00], " & _
        " Photos = [prm01] , " & _
        " OilCanning = [prm02] , " & _
        " CanningStopLine = [prm03], " & _
        " CoatingIssues = [prm04], " & _
        " CoatingStopLine = [prm05], " & _
        " IssuesOther = [prm06] " & _
        " WHERE InspectionEvent_PK = [prm07];"
      
    'strSQL = "UPDATE tblInspectionEvent " & _
    '    " SET Notes = '" & Replace(Me.txtNotes, "'", "''") & "' , " & _
    '    " Photos = '#" & Me.txtLinkToPhotos & "' , " & _
    '    " OilCanning = '" & Me.cboCanningYesNo & "' , " & _
    '    " CanningStopLine = '" & Me.cboCanningLineStop & "' , " & _
    '    " CoatingIssues = '" & Me.cboCoatingIssueYesNo & "' , " & _
    '    " CoatingStopLine = '" & Me.cboCoatingIssueLineStop & "' , " & _
    '    " IssuesOther = '" & Me.cboOtherIssues.Column(1) & "' " & _
    '    " WHERE InspectionEvent_PK = " & Me.txtInspectionEvent_ID & " ;"
  
        Debug.Print strSQL
  
   On Error GoTo Err_BeforeUpdate

   ' The Dirty property is True if the record has been changed.
   If Me.Dirty Then
      ' Prompt to confirm the save operation.
      If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
              "Save Record") = vbNo Then
         Me.Undo
         DoCmd.Close
      End If
      Else
        'CurrentDb.Execute strSQL, dbFailOnError
        With CurrentDb.CreateQueryDef("", strSQL)
            .Parameters("prm00") = Me.txtNotes
            .Parameters("prm01") = "#" & Me.txtLinkToPhotos
            .Parameters("prm02") = Me.cboCanningYesNo
            .Parameters("prm03") = Me.cboCanningLineStop
            .Parameters("prm04") = Me.cboCoatingIssueYesNo
            .Parameters("prm05") = Me.cboCoatingIssueLineStop
            .Parameters("prm06") = Me.cboOtherIssues.Column(1)
            .Parameters("prm07") = Me.txtInspectionEvent_ID
          
            .Execute dbFailOnError
        End With
        DoCmd.Close
  
    'MsgBox gFormName

    Select Case gFormName
        Case "frmInspectWelding"
            Forms!frmInspectWelding!cmdSaveWeldAssemble.Enabled = True ' now that notes, issues, and photos s have been entered frmInspectWelding can be saved.
        Case "frmInspectMill"
            Forms!frmInspectMill.cmdSaveMillInspection.Enabled = True
        Case "frmInspectAssemble"
            Forms!frmInspectAssemble.CmdSaveAssemble.Enabled = True
        Case "frmInspectFab"
            Forms!frmInspectFab.cmdSaveFabInspection.Enabled = True
        Case "frmInspectPaint"
            Forms!frmInspectPaint.cmdSaveInspectPaint.Enabled = True
    End Select
  
   End If



Exit_BeforeUpdate:
   Exit Sub

Err_BeforeUpdate:
   MsgBox Err.Number & " " & Err.Description
   Resume Exit_BeforeUpdate
End Sub
Hi Arnel - I'm unfamiliar with QueryDef. I'm looking at what you wrote and trying to figure out the basic logic of how it works. Are you able to give me a quick overview? It looks like a very useful tool to have in my toolbox.
Thanks,

Tim
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,359
and, with the simpler SET Notes, it would not throw an error of any kind if I left the Notes field blank?
The simpler SET is not using the Replace() function. That function can't handle null values.
 

jdraw

Super Moderator
Staff member
Local time
Today, 11:29
Joined
Jan 23, 2006
Messages
15,364
Good to see you're still active here Tim and good luck with your project!
 

Users who are viewing this thread

Top Bottom