asked on

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!

PatHartman

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

TechNinja2

ASKER

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?

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TechNinja2

ASKER

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?)

PatHartman

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

TechNinja2

ASKER

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!