Last time we learned how to use a Custom Function to save time and coding.  Now we will learn how to create another simple Function that give you the option of closing your form before opening another.  Too many open Forms can be confusing and wastes memory.  Also, as before, we have the benefit of using less code in our database.  After all, why use practically the same code over again many times when we can just write it once in a Function?

In a module paste the following code: 

Public Function OpenClosemyfrm(callingfrm As String, strOpenDocName As String)

   Select Case MsgBox("Do you want to close this form" & _
      " before opening another?", vbYesNo Or vbQuestion Or _
      vbDefaultButton1, "                         Close This Form?")

      Case vbYes

          ‘ Select the Form to be closed
          DoCmd.SelectObject acForm, callingfrm
          

          ‘ Save the current record so no data is lost

          DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

          ‘ Close the form
          DoCmd.Close acForm, callingfrm

      Case vbNo
            ' Do Nothing

      End Select
   

   DoCmd.OpenForm strOpenDocName   ‘ Open your Form

End Function

This Function opens the Message Box and gives the user a choice of whether to close the current form before opening another or leaving the current form open.  

If the Yes button is clicked, then the current record is saved and the form is then closed.  The form’s name has been passed on to the Function as the callingfrm variable.

If the user clicks the No button then the new form is opened and the old form is not closed.

To call this Function we use place the following code in the On Click Event of a button.  You will need to add a button to your Form with a caption like "View/Edit Customers Form" or "Open Customers Form" etc.

We do not use VBA, but rather enter an expression in the Properties Sheet as in the previous example.  Here is a sample expression that is used to open the new form.  In order to tell Access which Form to close, we had to include the Form’s name in the expression.

=OpenClosemyfrm("frmCustomers","frmEmployees")

The first Form name in quotation marks is the current Form’s name.  The second Form name is the Form to be opened. As we look again at the opening line of the Function we see two variable declared as String: callingfrm and strOpenDocName.

Public Function Openmyfrm(callingfrm As String, strOpenDocName As String)

The frmCustomers name is passed to the callingfrm variable and the name of the Form to be opened is passed to the strOpenDocName variable.  This enables us to use these variables in our Function code.

If you have any questions, comments, or corrections please post them here.  We would also like for you to post your Access Tips or code samples here so others can benefit.

Happy Computing,

Pat Wood