Check Record Status if Changed Before Navigating To Next Record: Prompt User to Save

>>Navigating To Next Record: Prompt User to Save
When the user moves to the next record the data is automatically saved...

Also, if the form is bound to a table you can use the dirty property of the form.

If Me.Dirty then
    'do something here...
End If

By 'record status' ... do you mean if the record is being edited? or that some other user has changed the record on another system?

Yes.  If the record is being edited on the form.  Oracle is backend and that appears to be managing multi user.  I get a prompt if record is edited by another user.

I put the Me.Dirty on Form.Before Update event and it works.  The issue I am facing is that I also have a "SAVE" button.  The Form.Before Update event is also firing prompting the user with my message box if they want to save the changes.  I prefer not to have this msg box appear as they intentionally hit the "SAVE" button.  Please advise.

Thank you for your help.  

-CK

Well ... IF ... the record has been edited ... then that IS the Status ... and if you have code in the BU ... then it will trigger.  So, what exactly do you want to get out of you 'checking'.  The Save button is going to save the record ... assuming all required field conditions have been met, etc.  Not really sure what you are trying to accompolish? Sorry.

mx

Just don't want the message box I have in the form.before update to fire when the user clicks on the SAVE button.  When they click on the SAVE button now, the message box appears will always appear asking them if they want to SAVE.  I prefer not to have the message box appear when they are clicking the SAVE button I have added using the button wizard.

So...somwhere in the form.before update event..if the user clicked the "SAVE" button, how do I bypass the message box in this event?  Or is there an alternative approach.

-CK

>>form.before update to fire when the user clicks on the SAVE button
Can you post the code for both of the? The for event and the button...

Well ... it's a 'Catch 22'.  Any code in the BU will execute when you try to save the current record - if ... the record is actually Dirty ... which it will be if any edits have been made.

'Dirty' and 'changed' and 'edited' are synonymous.  So ... when DO you want the msg box to appear ?

mx

Wow! missing some keystrokes there...

Should read

  Can you post the code for both of these? The before event and the button...

lol :-}

I only want the message box to appear if they do not click the "SAVE" button.

-CK

Well .... some Event will have to run code to trigger the msg box

I suppose you could use the Form OnDirty event to display the message box ... try that.  OnDirty will trigger as soon as an edit is made.

In fact ... forget 'I suppose' ... that is what you need to do ... now that I see what you are trying to do ... I think ?

mx

Here is my SAVE button code:
Private Sub Save_Click()
On Error GoTo Err_Save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

----
Here is my BU Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Dirty Then
    If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        Me.Undo
     Else
    LST_UPD_USR_ID.Value = CurrentUser()
    LST_UPD_DT.Value = Now()
    End If
    End If
   
End Sub

--

I guess to bypass this message in BU, I need a variable that I can assign in the SAVE funtion...then check the variable in the BU function in IF statement..... If you can provide this that may be just what I want.

-CK

Did you try the Form OnDirty Event?

Use the BU to do input validation ... not to display the kind of message.

Also ... for the 'Save' button ... all you need is:

Me.Dirty = False.
Thank will save the record.

mx

opps .... s/b 'THAT' will save the record

I put me.Dirty in SAVE button per below.  I still have the message box in my BU event..do not understand why not to have it there...where should I put it.

Having tested the me.dirty in SAVE button, my BU if statement still thinks it is TRUE.  I still get my message box asking if user wants to save.  My SAVE code below.

Private Sub Save_Click()
On Error GoTo Err_Save_Click
Me.Dirty = False

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

-----

Put your msg box code in the Form OnDirty. This lets the user know an edit has been started - if that's your intent.

Remove all code from the BU - for now.

The code in your Save button needs to be:

Private Sub Save_Click()
    Me.Dirty = False
End Sub

Take out the  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Executing Me.Dirty=False will do the Save.

I did put the code in the form.dirty but the problem is that on show message once the record has been changed on the first cell I update.

My intent is for the message to appear if user updated record, did not click save to save the record, and attempts to navigate to another record.  

SAVE:
Private Sub Save_Click()
On Error GoTo Err_Save_Click
Me.Dirty = False

'   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

---

BU
Private Sub Form_BeforeUpdate(Cancel As Integer)
  ' If Me.Dirty Then
   ' If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
    '    Cancel = True
     '   Me.Undo
     'Else
    'LST_UPD_USR_ID.Value = CurrentUser()
    'LST_UPD_DT.Value = Now()
    'End If
    'End If
   
End Sub

-----

Private Sub Form_Dirty(Cancel As Integer)
MsgBox ("The current record has been updated.")
End Sub

if you only want the prompt if they DO NOT click the save record then make a miodule level variable that gets set BEFORE the record save in the buton click. You will need to reset that variable in the form current event ...

Private mblnSaveClick As Boolean

Private Sub Form_Current()
    mblnSaveClick  = False
End Sub
Private Sub cmdSave_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If mblnSaveClick = False Then
        If Msgbox("Do You want to save?", vbYesNo) vbNo Then
            Me.Undo
        End If
    End If
End Sub

Steve

I tried the recommendation above...but when I altered a field on the form and did not click save and navigated to another record...I did not get the msge.  

- Opened Form
- Altered Field
- Clicked Save
-Moved to another record
- Altered Field
- Moved to another record
>> Problem is "NO Message Appeared to prompt User to SAVE"

My code below:
---
Option Compare Database

Private mblnSaveClick As Boolean
---

Private Sub Save_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
End Sub

-----
 If mblnSaveClick = False Then
        If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        Me.Undo
        Else
            LST_UPD_USR_ID.Value = CurrentUser()
            LST_UPD_DT.Value = Now()
        End If
    End If
---

Private Sub Form_Current()
  mblnSaveClick = False
End Sub

I added the following code and it appears to be working now.  Any thoughts/concerns?

Private Sub Form_Dirty(Cancel As Integer)
mblnSaveClick = False
End Sub

the BeforeUpdate should not fire unless a change was made so I don't see why you need to force mblnSaveClick to be false in the Form_Dirty, that is what is already happening in the Form_Current. The only issue I can see is that if you dirty the form but then undo those chnages you may still get prompted.

Steve

I added a piece of code to my BU which checks field value is correct and if not, then I cancel the save.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.AVAIL_FROM_SOURCE_TIME_REFMT > 23.99 Then
MsgBox ("Available From Source Time cannot exceed 23.99 hundred hours military time.  Please enter time of day (i.e 2:30 PM = 14.50)")
Cancel = True  >>>>>>> I just added this code to stop record from saving....after I added it, I get an error message below

Private Sub Save_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False  >>>>> errors out here!!!!!
    End If
End Sub

I image that is the 2501 error ... "action was cancelled" that is what you want to happen if they need to fix the data so you can just capture and bury that error

Private Sub Save_Click()
On Error GoTo ErrHanlder
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False  >>>>> errors out here!!!!!
    End If

ExitHandler:
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case 2051    'cancelled because invalid record caught in BeforeUpdate
                             'do nothing
        Case Else
            Msgbox Err.Number ": " & Err.Description
            'you should have a central error log function to call here.
    End Select
    Resume ExitHandler
End Sub

I do not have a central error log to capture....novice here.  Can you provide one please?

-CK

The error is a '2101' error message.  "The setting you entered is not valid for this property".

Ok ... the 2051 I posted should have been a 2501 (blame it on typo or old age, thake your pick:-)

The 2101 I *think* really amounts to the same thing, just bury it. As for a central error handler, you need to decide where you want to log to, local db, back end db, a sperate error loggin db, a text file, your system events, what ever and then you need some code to push it all there. I think that would be another question entirely. For the question today, I think the message box should suffice.

ErrHandler:
    Select Case Err.Number
        Case 2501    'cancelled because invalid record caught in BeforeUpdate
                             'do nothing
        Case 2101    'invalid property, you can't save a record that has been cancelled
                             'do nothing
        Case Else
            Msgbox Err.Number ": " & Err.Description
            'you should have a central error log function to call here.
    End Select
    Resume ExitHandler
End Sub


Steve

<ckaliveas>
While I consider my solution to be very easy and does address your concern about writing code in all the your controls afterupdate events, it is obvious that you could care less about anything offered because you just accepted a post that was only asking where you were with this question.

Good luck,
Steve

So much effort for free ... so little reward sometimes.  Geeze !!!
I didn't even know you could accept your own answer !!

mx

ckaliveas - can you chime in here and explain why my question (which was merely a follow-up) was accepted as the answer? Perhaps one of the many other comments was actually the answer?

>>I didn't even know you could accept your own answer !!
mx - I'm confused by this statement?