We help IT Professionals succeed at work.

changing Form dirty bit

chobe
chobe asked
on
Medium Priority
440 Views
Last Modified: 2012-08-13
Form has before_update trigger to prompt user if should save record (no = Me.undo).  I also have a save button with trigger on_click = DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70 (btw - this causes error in design mode only). Even after saving record using save button, before_update still triggers prompting user to save record twice - very sloppy. How can I prevent this from happening?
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Use the following line of code to perform a Save:

Me.Dirty = False

mx

Author

Commented:
I placed the above code in module for save button.  Next, before_update trigger still sees form as dirty and prompts to save.

Private Sub cmdSaveCC_Click()
On Error GoTo Err_cmdSaveCC_Click
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   Me.txtCCName.SetFocus
   Me.cmdSaveCC.Enabled = False
   Me.[Update_Date] = Now()
   Me.Dirty = False
Exit_cmdSaveCC_Click:
    Exit Sub
Err_cmdSaveCC_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveCC_Click
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_BeforeUpdate
    If Me.Dirty = True Then
        Me.[Update_Date] = Now()
        If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
               Me.Undo
        End If
    End If
Exit_BeforeUpdate:
         Exit Sub
Err_BeforeUpdate:
         MsgBox Err.Number & " " & Err.Description
         Resume Exit_BeforeUpdate
End Sub
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:


Private Sub cmdSaveCC_Click()
On Error GoTo Err_cmdSaveCC_Click
        '  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70  'Don't use this.
   Me.txtCCName.SetFocus
   Me.cmdSaveCC.Enabled = False
   Me.[Update_Date] = Now()
On error resume next       ' in case Cancel happens in Before Update  
Me.Dirty = False                                           ' *** save the record
Err.Clear

Exit_cmdSaveCC_Click:
    Exit Sub
Err_cmdSaveCC_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveCC_Click
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_BeforeUpdate
     ' If Me.Dirty = True Then                      ' Dirty will be true - which is why the BU triggers

        If MsgBox("Do you want to save?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
               Me.Undo
               Cancel = True        ' ** add this
        End If

     '  End If
    'Validation passed - set values here
      Me.[Update_Date] = Now()

Exit_BeforeUpdate:
         Exit Sub
Err_BeforeUpdate:
         MsgBox Err.Number & " " & Err.Description
         Resume Exit_BeforeUpdate
End Sub

Author

Commented:
MX,
My lack of experience prevents me from seeing how this changes things.  When I click the Save button, I am still prompted whether or not I want to save the record.  I was hoping to bypass this prompt and use the message control in After_update to warn the user that the record has changed (perhaps by accident) and because they did not click "save" before they are moving to a new record, they are being given one last chance to save the changes before moving to the next record.

Would you suggest instead removing the save button altogether and always prompting to save whatever change - Maybe make the message more explicit "You have change record ####. Do you want to save your changes?"  Which makes more sense?

Author

Commented:
I need to revisit this problem.  My form has a command button to create a new record, a save button that is enabled only on new record, and a beforeupdate event. The form is bound to tblProjectControl.  Each record has a assigned (not autonumber) ID number (txtProjectNumber - control source is [Project Number]).

Clicking "new" opens new record, hides txtProjectNumber, enables the save button, displays and create a new ID number (txtNewRecNumber - also bound to [Project Number]).

Problem is, after cmdNewRec executes, the beforeupdate event fires, prompting me to save new record. How do I avoid? Do I have to create new recordset? If so, how and then how to save the new recordset when click cmdSaveRec?
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty = True Then
        If MsgBox("some message?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
            Me.Undo
        Else
            Me.Update_Date = Now()
        End If
    End If
 
End Sub
 
Private Sub cmdNewRec_Click()
 
    DoCmd.GoToRecord , , acNewRec
 
    Me.txtNextRec.Visible = True
    Me.txtNextRec.SetFocus
    Me.TxtProjectNumber.Visible = False
    Me.cmdSaveRec.enabled = True
    
    Me.txtNextRec = Format(Nz(DMax("[Project Number]", "tblProjectControl", txtSubmitDT = Date), 0) + 1, "0000")
 
End Sub
 
Sub cmdSaveRec_Click()
 
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   'code to reset buttons, etc. goes here
   DoCmd.GoToRecord , , acLast
    
End Sub

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
This:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.Dirty = True Then
        If MsgBox("some message?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
            Me.Undo
        Else
            Me.Update_Date = Now()
        End If
    End If
 
End Sub
 
You do not (and should not) need to check for Dirty in the Before Update ... because, by definition ... the form *will be* Dirty in order for the BU to trigger.  So, remove that test.  Dirty will *always* be true in the Before Update event.

And please, please do not use this command:

 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

This command we deprecated by M$ several years ago, even though sadly the wizard still generates the code.

Use
Me.Dirty = False
As I have showed  
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Basically, generically ... you need this

To Save the Record:

Sub cmdSaveRec_Click()
On Error Resume Next           ' In case validation in the BU fails and BU is Cancelled
Me.Dirty = False   ' ** Save the record
Err.Clear
End Sub

Perform any Validation in the Before Update - which will Trigger when you click the Save button:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     ' ask the user if they really want to save the record OR perform some other validation here ...
        If MsgBox("some message?", vbYesNo + vbQuestion, "Save Record") = vbNo Then
            Me.Undo
        Else
            Me.Update_Date = Now()
        End If
End Sub


These are the two basic code snippets you need to Save a record, with some validation ...

mx

Author

Commented:
I apologize mx, but I just don't get it....if I create a new record, modify the record, and click "Save", then on dirty triggers prompting the user to save again, this isn't what I want - isn't there any way to avoid this?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
ok ... post the exact code you have now.

mx

Author

Commented:
Do you need more than what I posted earlier?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Did you make any of the changes I suggested - here http:#a23646328 ?

mx

Author

Commented:
Sorry it took so long to reply - I got tied up and then the experts-echance server was down and couldn't get in.  Anyway, I was able to fix the problem by removing the BeforeUpdate event altogether and by moving the if me.dirty then undo code to the record selectors cmdfirstRec, cmdPrevRec, cmdNextRec, and cmdLastRec.  I also change the cmdSaveRec code to your suggestion which works fine - I just don't understand how it actually saves anything, but it does.
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:


Me.Dirty = False

forces a record to be saved - if the record is Dirty.  It's just a trick that has been around for a long time.

mx

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
That explains a lot! Thanks for all your help.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
you are welcome

mx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.