MS Access Error Handling

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!
TechNinja2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
0
TechNinja2Author 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?
0
PatHartmanCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

TechNinja2Author 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?)
0
PatHartmanCommented:
Please post your code.  If it is in the correct event, it will fire when the record is saved.
0
TechNinja2Author 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.