Close Form, No Changes Written

[shelzmike]

I am working on a form and I want the users to have a button that when clicked will do several things:



First the button will be a "Close" button (I am hiding the "X" in the window).

1.) If the record is dirty, I want a message box to pop up and mention that the record has been changed, save changes before exiting.

a.) If they chose yes, the record will be save and the form will be closed.
b.) If they say no, the changes are not saved and the form closes. In this instance, changes should be reverted to last saved record and then the form closes (have a feeling this will be the hard, if even possible, part.

2.) If the record has not been changed (or was saved right before the close button was clicked), i.e. (I guess) not dirty, the form simply closes.

Thanks in advance!

Mike

[RuralGuy]

So what do you have so far?

[shelzmike]

Not sure what you are asking. I have lots so far. The form is pretty much finished and this would be the final touches on it. I know how to make a standard close command button, but was looking for something a little bit more. I am not really an Access expert or anything, but know my way around fairly well. At this point, I am just looking for a little bit of direction as I am at a roadblock. Thanks.

Mike

[RuralGuy]

Start by letting the wizard create the Close button for you. Then begin adding the components you want and testing as you go.

[shelzmike]

Okay, here we go. I am really close with only a few weird bugs (more than likely based on syntax, I am sure).

Private Sub close_btn_Click()
    Dim Msg, Style, Title, Response, MyString
    
    Msg = "Changes have been made to this record." + Chr(13) + _
        "Do you want to save first?"
    Style = vbYesNoCancel + vbExclamation + vbDefaultButton3
    Title = "WAIT! Before You Go"
    
    Response = MsgBox(Msg, Style, Title)
    
    If Me.Dirty = True Then
        If Response = vbYes Then  'User chooses yes button, so Save the Record
             DoCmd.RunCommand acCmdSaveRecord
        Else
            If Response = vbNo Then 'Otherwise if user chooses No, undo changes
                DoCmd.RunCommand acCmdUndo
            End If
        End If
            DoCmd.Close acForm, "tracker_frm" 'Close form after the above choices
    Else
        DoCmd.Close acForm, "tracker_frm"
    End If
End Sub

So, essentially if the record has been changed at all (i.e. Me.Dirty = True) then the user will be prompted with the Message Box telling them that the record has been changed and whether or not they want to save the record (Yes), discard the changes (No), or Cancel.

The function of the Yes and No buttons works well everytime. However, it works whether the record is dirty or not and I cannot figure out why.

So, I also have a couple of onCurrent functions that actually populate a couple of unbound fields with lookup data, shown here:

Private Sub Form_Current()
    ' Updates the proper tech name upon navigating through the record set
    txt_techName = DLookup("techName", "tech_tbl", "techNum=" & "tech_TechNum")
    
    
    ' Set Focus to W.O. Number upon new record
    If Me.NewRecord Then
        Me.trkr_woNumber.SetFocus
    End If
    
    
    ' Updates the proper FSM name upon navigating through the record set; without
    txt_FSM = DLookup("fsm_name", "qryFSM_Tech_Assignments", "techNum=" & "tech_TechNum")
    
    Me.Dirty = False
End Sub

So, before all of this, I did not have Me.Dirty = False at the end of the Sub. Only upon working with this message box did I realize that every record upon loading is technically dirty, right? Because although I did not make any changes, the VBA did, therefore = dirty.

However, this still does not work.

Basically, the message box will pop up under every circumstance. So, if I navigate to a record and make no changes and click the close button, I still get the message box. How can I fix that?

Also, the cancel button click now closes the form as well, which I do not want. I just want it to cancel the message box and go back to the form.

Thanks

Mike

[boblarson]

In the Before Update event it would be something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If MsgBox("The record has been changed, do you wish to save?", vbQuestion + vbYesNo, "Save Record?") = vbNo Then
      Cancel = True
      Me.Undo
   End If
 
End Sub

[shelzmike]

Thanks Bob for the reply. However, I may be being obtuse as I am not really sure what you are getting at . Will this work in conjunction with what I already have above?

I also caught the error on the Me.Dirty = False (my rookie mistake). I also fixed the Cancel button closing the form as well.

For some reason, the message box gets run every time I click the Close button (which is a custom close by the way) - whether or not the record is dirty. However, I cannot figure out if this is because the record is in fact being read as dirty (even when no changes are being made) because of the update on current or what.

This is kinda frustrating and I know it is something simple I am sure.

Thanks!

Mike

[boblarson]

If you are setting a control's value with code in the On Current event then it will be dirty.

My code should work with the close button. The button would simply use

DoCmd.Close acForm, Me.Name, acSaveNo

acSaveNo by the way only deals with design changes to the form. It has nothing to do with records.

But then it would close the form and then if there is something to be saved it would fire the Before Update event. So it appears that you have code that sets a value so you would need to also use the Before Update event to look to see if all of the fields that are needed are filled out and if not then go to the Cancel = True/ Me.Undo part.

[shelzmike]

Thanks for that. I think that I am realizing that I am making this more difficult than it needs to be.

I understand 90% of what you are saying; however, to clarify (hoping this isn't redundant). Your code will essentially do what I want to do, which is:

If a user navigates to a new record, fills in some info and decides to click the close button it is for either 1 of 2 reasons:

1.) They decided they didn't need to enter the info after all and does NOT want to save it

2.) Or They didn't realize they didn't save it (or it was pressed on accident), and they really do want to save the data.

(Well, 3 I guess if they didn't mean to hit it and wanted to cancel it back out)

So, the message box would pop up in all instances (EXCEPT if the record is NOT dirty - i.e. they moved to a new record, didn't enter any info and are leaving for lunch, etc. If the record is not dirty then there is nothing to worry about because either it is saved already from previous or nothing was entered at all)

If they do want to save, they click save and changes are saved and the form is closed.

If they do not want to save, any changes are undone and the form is closed.

If it was accident, the cancel button closes the message box and the form stays open.

Finally, two more ? related to this.

1.) If the OnCurrent event is causing the record to become dirty whenever it is displayed, where and how would I make it not dirty. I assumed that at the end of the onCurrent sub I would just put Me.Dirty (true). But that does not seem to work.

2.) Do default values that are autopopulated cause a record to be Dirty as well? i.e. I have the date field's default value set at today's date.


Thanks again. This thing has been driving me nuts all day today - but at least I am learning.

Mike

[shelzmike]

I apologize, I re-read your posts and misread them before. Are you saying that I should call the Before Update event FROM the Close button on click event?

[boblarson]

I apologize, I re-read your posts and misread them before. Are you saying that I should call the Before Update event FROM the Close button on click event?

Not really. I was saying that you can simply use

DoCmd.Close acForm, Me.Name, acSaveNo

in the close button's Click event and if there is anything to save, the Before Update event will fire and then the message can go up. If nothing has changed it would just close. But since you are setting a field to a value in the On Current event there will ALWAYS be a change to the form. You would need to get rid of that code or you would have to decide how to handle it in the before update event. If that field is automatically filled out that isn't necessarily a problem but we'll know that the Before Update event is going to happen ALWAYS then. So you would have to rely on checking other fields in that event to determine if there was anything changed/added by the user and then go from there.

[shelzmike]

WOW! You are so right. I was making it WAAAY harder than it needed to be. Almost comical when I look back on it!

Simply using

Private Sub close_btn_Click()
       
        DoCmd.Close acForm, "tracker_frm", acSaveNo 'Close form after the above choices
         
End Sub

Does exactly what I needed to do.

Thanks so much!

MIke

[boblarson]

WOW! You are so right. I was making it WAAAY harder than it needed to be. Almost comical when I look back on it!

Simply using

Private Sub close_btn_Click()
 
        DoCmd.Close acForm, "tracker_frm", acSaveNo 'Close form after the above choices
 
End Sub

Does exactly what I needed to do.

Thanks so much!

MIke

Also, you don't need to provide the "tracker_frm" part. Just use the GENERIC code I gave you. That way you can copy and paste it on any form and it will refer to that form it is on.

DoCmd.Close acForm, Me.Name, acSaveNo

[shelzmike]

Nice! Its always the little things that turn code that works into "good" code!

Couple of other interesting points - believe it or not, the code in the on Current event does not cause any problems with closing. If nothing is changed, I do not get the popup. Could that be because the controls that are getting updated by the on current event are not bound and not written to the database? They are for information only.

However, now my custom Save Button (I say custom because I prefer VBA coding to Macros for a variety of reasons and Access 2010 creates Macros if using the button wizard) triggers the pop up message when it doesn't need to - it is redundant. Not sure why. I will have to look at that one.

Also, I tried adding a Cancel Button (i.e. vbYesNoCancel) to the Before Update event, but it clicking it does not save and closes the form. Do I need to add specific code to handle that click?


One more thing to clarify in case someone else ever finds this message - in order to make this work you must add the code to both the click event for the cancel button AND the code for the Before Update event.

Mike

[boblarson]

As for the vbYesNoCancel, if you close the form the form also tries to Unload it. That has to be canceled as well in order to keep the form open.