form update

[slimjen]

All, using access 2010 I have a form that users can enter data or search by a combo box to review information about the record. If they make a change to an existing record; I want to have a prompt stating that they have made a change; are you sure you want to save the record or discard the changes. My decision making problem is where to put the code. The users have several ways to search to move to another record therefore they are not always closing the form to move to the next record. I don’t want them to be able to move to another record by searching and changing a record by mistake. I hope I’ve made clear what I’m asking in order for you to assist me. I’ve seen such before but can’t think how to make this happen. Thank you

[Bob Fitz]

Data validation should be done in the forms Before Update event which can be cancelled.
Sounds like you would need to check the forms Dirty property.
Post back if you need help with the actual code.

[slimjen]

Maybe something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim message As String
message = "You have made changes to this record.  Are you sure you would like to save?"
If MsgBox(message, vbQuestion + vbYesNo) = vbNo Then
Cancel = True
End If
End Sub

Will this work anytime they change the form? I don't want to annoy them when they create a new record; just change existing records. This is because some users don't pay attention and they start typing and move on causing a change; sometimes drastic to the record they are viewing. Thanks

[June7]

Yes, it will run with new record.

Try:

If Not Me.NewRecord Then

[slimjen]

Im getting an error when i click no to saving the record. "The setting you entered isn't valid for this property." Also each time I click on a different section of the record; say another subform; it gives me the question. I may want that but havent thought it through. This is the code: What am I doing wrong?

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim message As String
If Not Me.NewRecord Then
message = "You have made changes to this record.  Are you sure you would like to save?"
If MsgBox(message, vbQuestion + vbYesNo) = vbNo Then
Cancel = True
End If
End If
End Sub

[June7]

Which line does the debugger stop on?

Maybe after the Cancel:
Me.Undo

[slimjen]

O I have code on the close button also that I think is interferring:

If Me.Dirty Then Me.Dirty = False
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_ExitToSwitchboard_Click:
    Exit Sub

It's referring back to that.

So; My form has several subforms. The beforeupdate only fires when I change fields on the form. Ok. I don’t want users to accidentally change any fields. I don’t know how to prevent them from doing this since it is a data entry form also. I am noticing that the “before update” code fires when I change a field on the form. If I click no; the focus jumps back to the field I changed. If I keep saying no; it won’t let me proceed until I click yes. If I click yes; it will let me proceed to the next record as long as I don’t hit close. This is when it gives me the error. What do I do and where do I put the code?

[Dal Jeanis]

I wonder about having a close before you open the other form. I'd expect those would have to happen in the other order.