Checking If Memo Field Updated on Form

[maxx429]

I am trying to check if notes have been added to a Memo field on a form if certain other fields have changed. Example: If the user ticks a particular check box on the form, I want to force them to add some note detailing the change before the will be allowed to save the record.

I have the checks on the other fields working. What I can't seem to find an effective method for is checking whether the user has added notes to the Memo field on the form.

I've tried:

If Me.Q1_Comp_Check.Value <> Me.Q1_Comp_Check.OldValue Then
 
    If Me.txtSiteNotes.Value = Me.txtSiteNotes.OldValue Then
 
MsgBox "You Must Enter Notes Before You Can Save This Record", vbOKOnly
 
Me.txtAddNote.SetFocus
DoCmd.CancelEvent
 
Exit Sub
End If
End If

However, I get a Runtime 3251 error that the Operation Is not Supported For This Type Of Object. I'm guessing this is because the .Value operator is invalid when used with a Memo-type field.

So, any recommendations as to how I can determine if a Memo-type field has been updated before a record is saved?

Thanks in advance for any assistance.

Regards,
Garrett

[missinglinq]

"I'm guessing this is because the .Value operator is invalid when used with a Memo-type field"

The datatype, which in this case is memo field, has nothing to do with the .Value property! The property is the property of the textbox control that holds the memo field data, not the property of the memo field!

I suspect the problem is with the line

DoCmd.CancelEve nt

You don't say what event you have your code in, but not all events can be cancelled, as you're trying to do here. They way to determine whether an event can be cancelled is to check the first line of the event.

These two events

Private Sub Form_Load()

Private Sub Form_AfterUpdat e()

can't be cancelled, but

Private Sub Form_BeforeUpda te(Cancel As Integer)

can be cancelled, as indicated by the argument Cancel As Integer.

Also note that you can't force this by placing Cancel As Integer in the argument list of an event that Access doesn't place it in, such as

Private Sub Form_Load(Cancel As Integer)

Won't work, and is a good reason for never typing in the title line of a sub if it's one of the standard events that is accessable thru the (General) dropdown box in the code module.

Myself and half a dozen other experienced people spent many hours one nite trying to figure out why a very simple sub

Private Sub Form_Close(Cancel As Integer)
  Msgbox "Closing this Form!"
End Sub

kept erroring out, until someone finally noticed that Cancel As Integer was out of place!The OP had simply manually typed in the code, copying the style from a nearby sub, including the Cancel As Integer, which wasn't appropriate for the Form_Close event.

If the event you have the code in is one that can be cancelled, let us know and we'll look at it again.

Welcome to bytes!

Linq ;0)>

[maxx429]

"I'm guessing this is because the .Value operator is invalid when used with a Memo-type field"

The datatype, which in this case is memo field, has nothing to do with the .Value property! The property is the property of the textbox control that holds the memo field data, not the property of the memo field!

I suspect the problem is with the line

DoCmd.CancelEve nt

You don't say what event you have your code in, but not all events can be cancelled, as you're trying to do here. They way to determine whether an event can be cancelled is to check the first line of the event.

These two events

Private Sub Form_Load()

Private Sub Form_AfterUpdat e()

can't be cancelled, but

Private Sub Form_BeforeUpda te(Cancel As Integer)

can be cancelled, as indicated by the argument Cancel As Integer.

Also note that you can't force this by placing Cancel As Integer in the argument list of an event that Access doesn't place it in, such as

Private Sub Form_Load(Cancel As Integer)

Won't work, and is a good reason for never typing in the title line of a sub if it's one of the standard events that is accessable thru the (General) dropdown box in the code module.

Myself and half a dozen other experienced people spent many hours one nite trying to figure out why a very simple sub

Private Sub Form_Close(Cancel As Integer)
  Msgbox "Closing this Form!"
End Sub

kept erroring out, until someone finally noticed that Cancel As Integer was out of place!The OP had simply manually typed in the code, copying the style from a nearby sub, including the Cancel As Integer, which wasn't appropriate for the Form_Close event.

If the event you have the code in is one that can be cancelled, let us know and we'll look at it again.

Welcome to bytes!

Linq ;0)>

Sorry, I guess I wasn't clear.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   If Me.Q1_Comp_Check.Value <> Me.Q1_Comp_Check.OldValue Then
 
          If Me.txtSiteNotes.Value = Me.txtSiteNotes.OldValue Then
 
      MsgBox "You Must Enter Notes Before You Can Save This Record", vbOKOnly
 
      Me.txtAddNote.SetFocus
 
      DoCmd.CancelEvent
 
      Exit Sub
      End If
      End If

This should be clearer now. The above code is being executed in the Form's BeforeUpdate event.

DoCmd.CancelEve nt is intended to Cancel the update if the user has ticked the Q1_Comp_Check box and has NOT updated the txtSiteNotes field.

The first line of code checks to see if the Value of Q1_Comp_Check is different from the OldValue. If it is, the second line is attempting to check if the value of txtSiteNotes has changed, indicating that the user added some sort of note.

What I need is a way of determining if txtSiteNotes has changed.

The Runtime error is being generated on this line of code:

If Me.txtSiteNotes.Value = Me.txtSiteNotes.OldValue Then

Since I am not getting an error on this,

If Me.Q1_Comp_Check.Value <> Me.Q1_Comp_Check.OldValue Then

that seems to indicate that I cannot compare .Value to .OldValue on that type of field.

I hope that clears things up. I think I may have included too much information. :) All I really need is a way to determine if a Memo field's contents have changed.

Thanks again.

Garrett

[maxx429]

Bumping this on the off chance it was missed the first time around. :)

If there is not an effective way to check if my Memo field has been updated, does anyone have a suggestion as to how I can ensure that the user adds a note whenever certain fields on the Form are changed? I can't be the only person to have needed this sort of functionality, right?

I've considered using and Add Notes button and trying to trap whenever it is clicked, but I cannot seem to come up with away to do that.

Understand that I am not a professional developer. I'm simply the closest we have in my little company. :)

Thanks again for any help yo guys can offer.

Regards,
Garrett

[Stewart Ross]

Hi. I've tested the comparison of value to oldvalue on a memo field of my own using a version of your code without any run-time error at all. The test memo had 799 characters, so was much bigger than a normal text string.

I can't explain the error you are experiencing; it works fine for me.

You could find out more about the conditions at the time of the error by setting a breakpoint at the beginning of your code and using debug.print to print the oldvalue and current value of the notes field, and its length (using the len() function to do so). It will all help to understand why this is happening for you when it is not happening for me.

-Stewart

[missinglinq]

I've duplicated this entire thing and I, like Stewart, have no problem with it working the way you intend! I suppose we have to consider some form of corruption.

The only other thing I've noted is that you set focus to a control named txtAddNote. Is this a way of indirectly adding data to your txtSiteNotes control?

Linq ;0)>

[maxx429]

I've duplicated this entire thing and I, like Stewart, have no problem with it working the way you intend! I suppose we have to consider some form of corruption.

The only other thing I've noted is that you set focus to a control named txtAddNote. Is this a way of indirectly adding data to your txtSiteNotes control?

Linq ;0)>

Thanks to both Stewart and Linq for looking at this.

In answer to Linq's question, the txtAddNote control is an unbound Text Box where the user enters any new notes. These notes are then appended, using code on a Command Button, to the txtSiteNotes control which is a bound control tied to a Memo field in the table to which the Form is bound.

I'm not sure if this will help, but here is a bit of additional information:

This is the code on the Command button used to append new notes to txtSiteNotes control:

Private Sub cmdAddNotes_Click()
If IsNull(Me.txtAddNote) Then
 
MsgBox "Please Enter A Valid Note To Be Saved", vbOKOnly
 
Me.txtAddNote.SetFocus
 
Exit Sub
End If
 
Dim rs As Recordset, frm As Form, m As Long, str1 As String, CurForm As String
 
CurForm = Me.Name
 
Set frm = Me.Form
Set rs = frm.RecordsetClone
m = frm.CurrentRecord
rs.MoveFirst
rs.Move (m - 1)
 
If IsNull(rs!Site_Notes) Then
 
str1 = Me.txtAddNote & " " & _
Format(Now(), "dd mmm yy at hh:nn:ss ampm") & " by " & NetworkUserName
rs.Edit
Me.Site_Notes = str1
Me.txtAddNote = ""
rs.Update
 
Else
 
str1 = rs!Site_Notes
 
str1 = str1 & vbCrLf & Me.txtAddNote & " " & _
Format(Now(), "dd mmm yy at hh:nn:ss ampm") & " by " & NetworkUserName
rs.Edit
Me.Site_Notes = str1
Me.txtAddNote = ""
rs.Update
 
End If
End Sub

I'm using Access 2007, but the database I am working in is formatted as Access 2000.

I have tested using the .Value and .OldValue properties on multiple Text Controls on the same form and had no issues. I only get the Runtime error about the Operation Is Not Supported for This type Of Object when I attempt to compare Value to OldValue on the Memo field.

Thanks for looking.

Garrett