Notes field, quotations, apostrophes, and error messages

Z

Zydeceltico

Registered User.
Local time
Today, 00:51
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
 
Hi Tim. Can you also post the result of the Debug.Print?
 
Zydeceltico said:
That's the entire code for the "Save and Close" button on frmNotesandIssues. The original code had the bolded line above as this:
Click to expand...
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 & "' , " & _
 
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
 
theDBguy said:
Hi Tim. Can you also post the result of the Debug.Print?
Click to expand...
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 said:
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.
Click to expand...
Sorry. I got interrupted and wrote the wrong sentence that input in the Notes field. I corrected it above.
 
Zydeceltico said:
Sorry. I got interrupted and wrote the wrong sentence that input in the Notes field. I corrected it above.
Click to expand...
But you said in the second inspection, you got an error. What was the result of the Debug.Print then?
 
theDBguy said:
But you said in the second inspection, you got an error. What was the result of the Debug.Print then?
Click to expand...
Attached is what I get with the second condition.
 

Attachments

  • Capture.JPG
    Capture.JPG
    81.9 KB · Views: 273
Zydeceltico said:
Clicking Debug gives me this:
Click to expand...
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.
 
theDBguy said:
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.
Click to expand...
That fixed it! Thanks.

I need to read up on Nz and QueryDef. I'm not that familiar with either one.
 
Zydeceltico said:
That fixed it! Thanks.

I need to read up on Nz and QueryDef. I'm not that familiar with either one.
Click to expand...
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 said:
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?
Click to expand...
and, with the simpler SET Notes, it would not throw an error of any kind if I left the Notes field blank?
 
arnelgp said:
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
Click to expand...
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
 
Zydeceltico said:
and, with the simpler SET Notes, it would not throw an error of any kind if I left the Notes field blank?
Click to expand...
The simpler SET is not using the Replace() function. That function can't handle null values.
 
Good to see you're still active here Tim and good luck with your project!
 
You must log in or register to reply here.

Similar threads

C
Solved Majp Treeview
Replies
13
Views
291
ClaraBarton
C
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
378
CJ_London
C
A
Restrictions Update Message
Replies
17
Views
272
Gasman
Gasman
Papa_Bear1
Getting Run-time error '3625' (The text file specification 'x' does not exist...) - but it does.
Replies
12
Views
255
The_Doc_Man
The_Doc_Man
T
Invalid Qualifier
Replies
5
Views
164
TheSearcher
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom