Strange Macro Behaviour

[sdp_tws]

Hi everyone, can anybody help me with my very frustrating problem?

I have a form which contains a button named "cmd_OK", in vba I have an event "cmd_OK_Click" which contains the following code:

Private Sub cmd_OK_Click()
MsgBox "OK Button Pressed" ' TROUBLESHOOTING MESSAGE
Close_Save
End Sub

And then a further sub called "Close_Save" which looks like this:

Sub Close_Save()

    MsgBox "Close_Save Macro"
    
    If InAddMode = True Then
        ' IF THE FORM HAS BEEN OPENED IN ADD MODE, THEN FIRST CHECK
        ' THAT ALL THE REQUIRED FIELDS HAVE BEEN ENTERED
        MissingField = ""
        If Me.cmb_Currency.Value = 0 Then MissingField = "CURRENCY"
        If Me.cmb_Country.Value = 0 Then MissingField = "COUNTRY"
        If IsNull(Me.txt_PostCode) Then MissingField = "POSTCODE"
        If IsNull(Me.txt_Address) Then MissingField = "ADDRESS"
        If IsNull(Me.txt_CompanyName) Then MissingField = "COMPANY NAME"
        If IsNull(MissingField) Then
            ' IF ALL FIELDS HAVE BEEN ENTERED, CLOSE AND SAVE
            DoCmd.Close , , acSaveYes
        Else
            ' IF FIELDS ARE MISSING, WARN THE USER AND GIVE THEM THE
            ' CHANCE TO RETRY
            ch = MsgBox("You have not entered anything in the " & _
            MissingField & " box." & Chr(10) & Chr(10) & _
            "Select retry to go back and enter." & Chr(10) & _
            "Select cancel to exit, and your new company details will not be saved.", _
            vbRetryCancel, "Items Missing")
            ' IF CANCEL IS SELECTED, THE FORM WILL BE CLOSED AND THE
            ' RECORD NOT SAVED
            If ch = 2 Then DoCmd.Close '(cancel selected)
        End If
    Else
        DoCmd.Close , , acSaveYes
    End If
End Sub

And when I click the OK button I expect to see a message box stating "OK Button Pressed" followed by another stating "Close_Save macro", and then for it to run the rest of the macro.

What I get is the "OK Button Pressed" message only and nothing else.

Now, the weird thing is, if I remove all the code from the "Close_Save" sub completely, and leave on the the first msgbox line of text it seems to run fine and when I press the OK button I get both messages.

Any ideas?

 

[pbaldy]

Have you set a breakpoint and stepped through it to see how it's going through the code? My first guess would be that this test isn't being met:

If InAddMode = True Then

 

[sdp_tws]

Have you set a breakpoint and stepped through it to see how it's going through the code? My first guess would be that this test isn't being met:

If InAddMode = True Then

I'm not sure what a breakpoint is but even if the afforementioned test isn't being met, then it should still display my message "Close_Save Macro" first as this is before the if statement?

 

[pbaldy]

Good point; yes it should. Does the code compile, and does Option Explicit appear near the top?

Looking more closely at the code, it's not going to do what you think it will. For starters, you test for MissingField being Null, but it never will be (you start out by setting it to a zero length string (ZLS), which is different). The big killer is that the save argument in the Close command, "acSaveYes", determines whether changes to the form object itself are saved, not data. The best way to validate data is in the before update event of the form:

http://www.baldyweb.com/BeforeUpdate.htm

You could add your message box and the way to unwind the data entry would be with the Undo method. Also note the test in that example, which tests for both Null and ZLS.

 

[sdp_tws]

It compiles fine and option explicit does not appear.

The example code you supply looks much cleaner. I will try that, but I'm stil confused as at least the message box should work in my code. Incidentally it actually does work if I only include the message box in that sub and nothing else?

 

[Rabbie]

It compiles fine and option explicit does not appear.

The example code you supply looks much cleaner. I will try that, but I'm stil confused as at least the message box should work in my code. Incidentally it actually does work if I only include the message box in that sub and nothing else?

Option Explicit should appear at the beginning of your code modules. It will ensure that all variables have been explicitly declared. This often shows up errors in your code.

 

[boblarson]

Just an FYI for you -

You have a couple of things going wrong here.

1. This code:
' IF ALL FIELDS HAVE BEEN ENTERED, CLOSE AND SAVE
DoCmd.Close , , acSaveYes

Does not save the record. acSaveYes in this instance means save the DESIGN CHANGES to the form. It does NOT mean save the record changes. For that you would need to either use:

If Me.Dirty Then Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

2. You can't cancel the record by simply closing the form. You would need to UNDO the record first and/or use the Before Update event of the form to cancel.

 

[pbaldy]

Darn, I must have made another invisible post.

 

[boblarson]

Darn, I must have made another invisible post.

I see them now, but I swear that they weren't showing up when I was posting. I think someone is messing with the site or something.

 

[sdp_tws]

OK, I have it working after reading your advice, so thanks for that guys. I've taken it on board about the acSaveYes.

Just a couple questions though.

If I wanted to close and save, I just need to do the DoCmd.Close command and if I want to close and NOT save I need to precede it with a Me.Undo, is this correct?

And what difference in effect would

If Me.Dirty Then Me.Dirty = False

and

DoCmd.RunCommand acCmdSaveRecord

have?

And finally, I tried using breakpoints in my code. From the description I have seen the code should stop at the point where the breakpoint is and from there you can step through it. Is this correct? Because it doesn't work for me.

 

[boblarson]

Just a couple questions though.

If I wanted to close and save, I just need to do the DoCmd.Close command and if I want to close and NOT save I need to precede it with a Me.Undo, is this correct?

That should be correct.

And what difference in effect would

If Me.Dirty Then Me.Dirty = False

and

DoCmd.RunCommand acCmdSaveRecord

have?

The code
If Me.Dirty Then Me.Dirty = False
will ONLY attempt a save if something has been changed on a bound form (new record started, field changed, data typed in, etc.) whereas

DoCmd.RunCommand acCmdSaveRecord
will attempt to save the record even if there is nothing to save. So, I prefer the one that checks to see if there is something to save first.

And finally, I tried using breakpoints in my code. From the description I have seen the code should stop at the point where the breakpoint is and from there you can step through it. Is this correct? Because it doesn't work for me.

Make sure you haven't unchecked the USE ACCESS SPECIAL KEYS checkbox in the database options.

 

[sdp_tws]

Make sure you haven't unchecked the USE ACCESS SPECIAL KEYS checkbox in the database options.

Perfect! Thanks.

 

[boblarson]

Perfect! Thanks.

¡No problema! But remember to uncheck it again when you deploy (so the users can't use the F11 key to get to the database window/navigation pane).