Just getting into VBA

[1jet]

Hi all,
I've realised the limitations of macros and had a quick crash course today.
I'm already stuck and was hoping to find some help here.

My form frmEditEmployee is pretty self explanatory. You can edit employee's details. Please keep in mind that when changes are made, they are saved automatically when the form is closed.
frmEditEmployee has a button cmdClose which must do the following two,
1. Undo any changes made, if any
2. Close the form
Simple enough?
Now cmdClose's purpose is to close the form WITHOUT saving any changes (if any).

I've tried using a macro, and was told that VBA is the solution to get rid of that annoying "there's nothing to undo" message when no changes are made.

Here is my code...

'------------------------------------------------------------
' mcrCloseFormEditEmployee <-- old deleted macro
' vbaCloseFormEditEmployee <-- new
'------------------------------------------------------------
Function vbaCloseFormEditEmployee()
On Error GoTo vbaCloseFormEditEmployee_Err

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close acForm, "frmEditEmployee"
    DoCmd.SetWarnings True


vbaCloseFormEditEmployee_Exit:
    Exit Function

vbaCloseFormEditEmployee_Err:
'   MsgBox Error$ <-- no more error messages
    Resume vbaCloseFormEditEmployee_Exit

End Function

Now with the current code, the only way for me to close the form is to make changes first. If I don't make any changes, the form will not close.

Please help.
Thanks in advance.

 

[majhl]

Hi all,
I've realised the limitations of macros and had a quick crash course today.
I'm already stuck and was hoping to find some help here.

My form frmEditEmployee is pretty self explanatory. You can edit employee's details. Please keep in mind that when changes are made, they are saved automatically when the form is closed.
frmEditEmployee has a button cmdClose which must do the following two,
1. Undo any changes made, if any
2. Close the form
Simple enough?
Now cmdClose's purpose is to close the form WITHOUT saving any changes (if any).

I've tried using a macro, and was told that VBA is the solution to get rid of that annoying "there's nothing to undo" message when no changes are made.

Here is my code...

'------------------------------------------------------------
' mcrCloseFormEditEmployee <-- old deleted macro
' vbaCloseFormEditEmployee <-- new
'------------------------------------------------------------
Function vbaCloseFormEditEmployee()
On Error GoTo vbaCloseFormEditEmployee_Err

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close acForm, "frmEditEmployee"
    DoCmd.SetWarnings True


vbaCloseFormEditEmployee_Exit:
    Exit Function

vbaCloseFormEditEmployee_Err:
'   MsgBox Error$ <-- no more error messages
    Resume vbaCloseFormEditEmployee_Exit

End Function

Now with the current code, the only way for me to close the form is to make changes first. If I don't make any changes, the form will not close.

Please help.
Thanks in advance.

You could use the Ondirty event of the form and prompt the user for action

if me.dirty =true then

       if msgbox("Save the changes?",vbyesno)=vbyes then
          
           DoCmd.Close acForm, me.name 

       else
 
            me.undo
            me.close

        end if

end if

Or something similar.

 

[boblarson]

You could use the Ondirty event of the form and prompt the user for action

Not a good option because the second you dirty the record by editing or adding new data then this fires. So you would get this message box for every character you typed and you would need to click yes to save and it would save a single character to the record and by the end you could not cancel anyway if you decided not to, but after 100 message boxes you might have some pretty angry users.

What I would do is to have a global boolean (in the declarations section of a standard module)

 Public blnClose As Boolean

called blnClose, for example, and then set it in the button's click event.

blnClose = True
DoCmd.Close acForm, Me.Name, acSaveNo

If you set the flag to true in the click event of the button and then in the form's BEFORE UPDATE event you put

If blnClose Then
   Cancel = True
   Me.Undo
   blnClose = False
End If

Then if someone closes the form without using the button it will save the record automatically because the flag has not been set. But if they use the close button it will cancel any entry and undo it.

 

[1jet]

Thanks sir,
I've taken note of your "boolean" approach for next time.
I've found a solution as I posted this thread.
What I've done is as follows.

'------------------------------------------------------------
' mcrCloseFormEditEmployee <-- old deleted macro
' vbaCloseFormEditEmployee <-- new
'------------------------------------------------------------
Function vbaCloseFormEditEmployee()
On Error GoTo vbaCloseFormEditEmployee_Err

    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdUndo
    DoCmd.Close acForm, "frmEditEmployee" <-- copied this line
    DoCmd.SetWarnings True


vbaCloseFormEditEmployee_Exit:
    Exit Function

vbaCloseFormEditEmployee_Err:
'   MsgBox Error$ <-- no more error messages
    DoCmd.Close acForm, "frmEditEmployee" <-- to here
    Resume vbaCloseFormEditEmployee_Exit

End Function

So now, when the invisible "there's nothing to undo" message comes, the form will close.

Thanks all for your input.