Invalid Use of Null error

[pfunk]

i get a invalid use of null error if there is not previous note or data in the field. if there is a users note or the field is already been populated it works fine can anyone help me with this one?


Private Sub cmdUpdateNotes_Click()
On Error GoTo Err_cmdUpdateNotes_Click

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to save your Notes?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Notes" ' Define title.
Help = "" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
MyString = "Yes" ' Perform some action.
DoCmd.SetWarnings False
DoCmd.RunSQL "Update T_Collection_Records Set T_Collection_Records.Notes = '" & _
Replace([Notes], "'", "''") & Chr(13) & Chr(10) & Date & " " & Time() & " - " & txtNotes & _
"'" & " Where T_Collection_Records.[Account #]= '" & [Account #] & "'", False
DoCmd.Close
DoCmd.SetWarnings True

Else ' User chose No.
MyString = "No" ' Perform some action.
End If


Exit_cmdUpdateNotes_Click:
Exit Sub

Err_cmdUpdateNotes_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_cmdUpdateNotes_Click
End Sub

 

[PHV]

Replace this:
Replace([Notes], "'", "''")
with this:
Replace([Notes] & "", "'", "''")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pfunk]

like a charm thanks, to bring this a little further how could i force my user to enter a note on theis form if they dont they cnnot move to the next record

 

[PHV]

In the note control BeforeUpdate event procedure:
If Trim(Me![note control] & "") = "" Then
MsgBox "You have to enter a note"
Cancel = True
Exit Sub
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pfunk]

no that did not work. mabee i did not clarify what i am looking to do . i have a form called Q_T_Collection_Records if a user is on a phone call and lets say three minutes go by and they have not moved to any field within that form. a message box saying: wrap it up! or get a manager.

 

[Remou]

There is code in this FAQ for detecting idle time:

http://www.tek-tips.com/faqs.cfm?fid=1432