Disable/Enable Code if or if not new record

[Eljefegeneo]

I want to be able to use the following code if a record has already been entered, but is not a new record. That is, if the record exists, then the code will fire. If the record is a new entry, then it won't. I thought of using some sort of selection criteria if the record was entered previously; if Date/Time entered > five minutes, then the code will fire. But, of course, there must be a simpler way. The form is for data entry of "NewNames" into a DB, and a hundred or more can be entered at one sitting.
Thanks.
On Error GoTo BeforeUpdate_Error

If Me.Dirty Then
If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo
End If
End If

BeforeUpdate_Exit:
Exit Sub

BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit

 

[CJ_London]

I resume you have this code in the form beforeupdate event?

If so, you need to add in

If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo 'to reset form values back to their original value
cancel=true ' to cancel the update event
End If

 

[Eljefegeneo]

Thanks. I suppose that I did not explain it correctly. The code works fine, but if the record is new, the same message box pops up. I only want it to pop up if the record already exists, i.e. is an existing record. If not, and it is an entirely new record, then I don't want it to ask me if I want to save the record. And yes, it is in the BeforeUpdate event.

I tried what you said and it didn't change anything. Am I doing something wrong? Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Error

If Me.Dirty Then
If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo
Cancel = True
End If
End If

BeforeUpdate_Exit:
Exit Sub

BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
End Sub

 

[John Big Booty]

In that case test the NewRecord property your code might look something like;

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Error

If Me.Dirty And Me.NewRecord = False Then
     If MsgBox("The record has changed - do you want to save it?", _
          vbYesNo + vbQuestion, "Save Changes") = vbNo Then
          Me.Undo
          Cancel = True
     End If
End If

BeforeUpdate_Exit:
Exit Sub

BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
End Sub

 

[John Big Booty]

P.S. I'd also like to bring this post at the head of this forum to both previous posters attention for future reference

 

[CJ_London]

Sorry JBB, usually do but it was late!

 

[Eljefegeneo]

Works like a charm. Thank you JBB and CJL!

 

[Eljefegeneo]

The code works fine. But I have discovered that on two of my forms, it doesn't. The Message Box message pops up even though I haven't change anything in the record as I go from one record to the next. It doesn't happen on every record, just a number of them. The only think I can think of is that there is a calculated fields based on dates causing the problem. That is, this unbound control [Status] is equal to something based on the difference in dates.
The unbound control [Status] can trigger a change in another bound control [Class]. That is, if the record shows that the [Status] is "Inactive" and [Class] is "Active", then [Class] changes to "Previous". [Class] is a combo box of seven different selections. I realize that I shouldn't be populating a bound control from an unbound calculated control, but the data comes from an old DOS database, and I can't delete this field [Class].

These are the only records that the Message box pops up unexpectedly. So I am thinking that this is what is causing the problem. If so, is there any way I can avoid the Message Box to trigger on every record where the Status is changed from "Active" to "Inactive" or vice versa?

 

[pr2-eugin]

Is it possible to trace back the code that makes this calculation and stop it? As I felt like you are okay with deleting the field, but cannot..

but the data comes from an old DOS database, and I can't delete this field [Class].

So this calculation is not that important?? I am not sure.. just a suggestion..

 

[Eljefegeneo]

I also noticed that it did the same thing on a form that if a check box is checked, a text label pops up telling the user to do something. If it is not checked, it doesn't. So it seems as if any "update" occurs, even if it is not important, it triggers the update Message Box. If there is no way of disabling the code to check for updates if one field changes, then I will have to do without it. But thanks anyway, I do appreciate everyone's time.

 

[CJ_London]

I think it is the me.dirty that is causing the problem - it will be set to true if any field is changed - it won't unset itself if the field is changed back (except perhaps with using the esc key (not checked)) - i.e. the code has gone through the field afterupdate event. The msgbox you are referring to presumable happens in the afterupdate event of the checkbox - try moving the call to the msgbox to the beforeupdate event and then set undo and cancel if the response is 'do nothing'

 

[Eljefegeneo]

This is the code that I have been using. In the BeforeUpdate event. I don't see where I can change anything. Used on a new record produces the desired result. It is on an existing record that it doesn't; I suppose that the event is detecting some change in the form. Can I add some code to exclude any control change. I could then work it out control by control until I find it. That is, is there VBA that says if [ControlName] changes, disregard this code?

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Error

If Me.Dirty And Me.NewRecord = False Then
If MsgBox("The record has changed - do you want to save it?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo
Cancel = True
End If
End If

BeforeUpdate_Exit:
Exit Sub

BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
End Sub

 

[CJ_London]

I was referring to this

if a check box is checked, a text label pops up telling the user to do something.

The code that causes the text label to pop up is probably in the afterupdate event of the checkbox referred to in

that if a check box is checked

I'm suggesting that if the users response to the text box popping up is 'cancel' then cancel the event.

I am assuming that that the text box is actually an inputbox since it implies some sort of user response

 

[Eljefegeneo]

Thanks. I will try to figure this out with your suggestion. It actually isn't a check box, but a True/False field. If the field is true, then a Label becomes visible telling the user to do something. Sorry for the mislabeling of controls. I think the real problem is that when some control, bound or calculated, triggers an update in another control, the form thinks that it has been changed, that is, Dirty. Perhaps if I tried to do away with this feature, the original code would work. There are several instances which I will try. Thanks again.