MS Access Error Handling

TechNinja2
TechNinja2 used Ask the Experts™
on
Experts!

My deadline is nearing and my time is shortening.  This may be an easy answer.  I'm just running out of time to troubleshoot and figure it out.  I have this code on the click event of a command button to switch to another form (I used generic control references since it is for a client):

    If Err.Number = 0 Or Err.Number = 2501 Then
        Err.Clear
    End If
    On Error Resume Next
    If IsNull(Me.ComboBox) Then
        MsgBox "No Item Was Selected." & vbCrLf & "Please select an item from the pull-down menu before proceeding.", vbExclamation + vbOKOnly, "Select Item"
    End If
    If (Form.Dirty Or Not Me.subForm.Form.NewRecord) Then     'if I use Me.subForm.Form.Dirty the undo doesn't work on the subform
        If MsgBox("This record has been changed.  Do you want to save changes?", vbQuestion + vbYesNo, "Save Changes?") = vbNo Then
            DoCmd.RunCommand acCmdUndo     'to undo changes on the main form
            Me.subIMenuCondition.SetFocus
            DoCmd.SetWarnings False     'hides the do you want to delete this record message
            DoCmd.RunCommand acCmdUndo     'to undo changes on the subform
        Else
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.OpenForm "FRM_NewForm", acNormal
        End If
    Else
        DoCmd.OpenForm "FRM_NewForm", acNormal
    End If
    DoCmd.SetWarnings True

Open in new window


A little background on the form.  The user selects an item from the unbound Me.ComboBox, which then populates a few other unbound combo boxes in the header, jumps to the record by ID# in Me.ComboBox and shows details in the subform.  It all works great and the undo works fine.  I just get the "This record has been changed" message when I select an Item from the unbound Me.ComboBox but have made no other changes to the form.  How do I change the code so I only get this message when I change bound controls/fields?

Thx!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Your error check doesn't make sense to me.  You are checking for 0 or 2501 and clearing the error.  0 isn't an error and 2501 is generally benign and can be ignored and yet you are continuing in all cases.  Since the code is out of context, it is confusing.

Once you are no longer in the subform, all references to it are to the CURRENT record, whatever that may be and it is not likely to be a "new" record.  You also cannot undo changes to a subform from outside that subform.  Once focus leaves the subform, Access automatically saves the record so it is too late to undo it.

Any message to the user asking if he wants to save belongs in the Form(or subform)'s BeforeUpdate event.  If the user says he wants to cancel, you can undo at that point and cancel the save.  Having this code in any other event will not work in 100% of the cases.  Something will slip through.  Always use the correct event for your desired effect and in this case, that event is the Form's BeforeUpdate event.  The BeforeUpdate event only runs if a record has been changed or added and it is the last event to run before the change is committed.

If MsgBox("Do you want to save?", vbYesNo) = vbNo Then
    Cancel = True
    Me.Undo
    Exit Sub
End If

Author

Commented:
Thx Pat!  I was testing options for 0 and 2501.  I should have not included it in this post.  Your suggestion makes logical sense, but for some reason, it doesn't trigger the message when I change a bound control then click on a command button to go to another form.  Suggestions?
Distinguished Expert 2017
Commented:
The BeforeUpdate event is triggered when something causes Access to save the current record.  If you want to trigger the save in your button, you can.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Still not working.  I want to trigger the popup message to confirm user wants to save the form before going to another form.  (Note that I am not closing the form since I have a few lookups pulling into the next form so maybe that's why the popup message is not showing?)
Distinguished Expert 2017

Commented:
Please post your code.  If it is in the correct event, it will fire when the record is saved.

Author

Commented:
I finally figured it out (with your help :)  It required both a vbYesNo MsgBox when I clicked a command button on the main form (I changed Form.Dirty Or Not Me.subForm.Form.NewRecord to just Form.Dirty to stop triggering the unbound combobox), then added your vbYesNo MsgBox in the before update event on the subform as you suggested.  

Thx!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial