making the close event public

billy bob
billy bob used Ask the Experts™
on
Hello.  I have been assigned an existing A2013 db (from a person that is gone) that the boss wants to have streamlined (his words), which means go thru and consolidate code, remove unneeded code, etc.  I came across the code in the click event of a button for closing the form.  Further checking shows the exact same code on a total of 11 close buttons on 11 different forms.  I have moved this into a public sub (also showing below) but I get error message.  For the button click I 'Call CloseForm'.  The error is "Error 91 (object variable or with block variable not set) ...."  This occurs on the line frm.btnSave.SetFocus.  So, now I'm puzzled as I have done this several times before on other apps that I've been assigned but getting error on this one.  

I am wondering if another pair of eyes can see the error of my ways not to mention the code's.

Thanks in advance.

.... John

========== this is what was found  on 11 buttons =======
    Dim strMsg As String

    strMsg = "Changes have not been saved."
    strMsg = strMsg & vbCr & vbCr & "    Click Yes -> Go to SAVE button."
    strMsg = strMsg & vbCr & vbCr & "    Click No to close the form without saving."

    If blnFormHasBeenChanged = True Then
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Changes") = vbYes Then
            Me.btnSave.SetFocus
        Else
            DoCmd.Close     'the NO response
        End If
    Else
        DoCmd.Close
    End If
=========================================

======== this is what I want to replaced it with =========
Public Sub CloseForm()
    Dim frm As Access.Form
    Dim strMsg As String

    strMsg = "Changes have not been saved."
    strMsg = strMsg & vbCr & vbCr & "    Click Yes -> Go to SAVE button."
    strMsg = strMsg & vbCr & vbCr & "    Click No to close the form without saving."

    If blnFormHasBeenChanged = True Then
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Changes") = vbYes Then
            frm.btnSave.SetFocus
        Else
            DoCmd.Close     'the NO response
        End If
    Else
        DoCmd.Close
    End If

End Sub
=========================================
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Without further context, this may be okay.. or not.

Where is this procedure placed? What is blnFormHasBeenChanged? You don't set the frm variable.

But: Access uses an auto-save strategy in the first place. So why bothering with it?
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
You haven't set the variable frm.

You need to pass in a reference to the form that's calling the code:

Public Sub CloseForm(frm as Form)

Then when you call it:

  CloseForm([Form])

  or

 CloseForm([Me])

Jim.

Author

Commented:
I found this 'Public blnFormHasBeenChanged As Boolean' at the top of the module.  The forms and controls are unbound.  Controls (not all) have another function 'ControlUpdated' attached to them that has 'blnFormHasBeenChanged = True' in it.  This determines if the control has changed.  I consider it the Me.Dirty version for unbound controls/forms.  This all worked nicely when the closing code was inside each click event of the button.  

The original procedure was found in each click event of the close buttons.  Trying to consolidate.
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:
Mr Dettman, thank you.  That seems to work, the (Me) line.  I will need to test it more to make certain.  When I was trying the public sub as you show, when I called it I never used Me.  I was trying to use the form name.  I never considered 'Me'.  A simple 2 letter word was the hold up.  
Thank you.
... John
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<This determines if the control has changed.  I consider it the Me.Dirty version for unbound controls/forms.>>

 That would be correct and you'll need to make "blnFormHasBeenChanged " a property of the form in order to consolidate this or still hold it in the variable and pass it into the sub.

Jim.
ste5anSenior Developer

Commented:
In this case:

1. Make that property private and add a read-only property for it.
2. Add a public SaveChanges() method to it.
3. Change the function to:

Public Sub FormClose(AFormName As String)

  Const MSG_CONFRIM_SAVE As String = _
    "Your message goes here."

  On Local Error Resume Next

  Dim Form As Access.Form

  Dim UnsavedChanges As Boolean

  Set Form = Forms(AFormName)
  UnsavedChanges = False
  UnsavedChanges = Form.UnsavedChanges  ' The read-only property.
  If UnsavedChanges Then
    If MsgBox(MSG_CONFRIM_SAVE, vbQuestion + vbYesNo, "Save Changes") = vbYes Then
      Form.SaveChanges                  ' The public SaveChanges() method in your form.
    End If
  End If

  Set Form = Nothing
  DoCmd.Close acForm, AFormName

End Sub

Open in new window

4. Now you can use =FormClose(Form.Name) as an expression instead of an event procedure in the close button.

Now you can have for every form the same button with that expression to close the form. Just add the property and method where you need the extra control of the save action.
Thank you for the question and answers. Useful information for me too.

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