How Do I Hide A Form But Leave It Running?

There are times when it is preferable to hide a form, in other words to have it open but invisible.

1. Global Variables:
   There are circumstances under which global variables lose their values in Access. These circumstances are not common, but they happen often enough to be of concern. One way around this problem is to create a hidden form with unbound controls, each of which would hold the value of one of your global variables. So instead of using a global variable, you set and reference the value of a control on the form.
2. Timer events:
   If you want run a process at a particular time or multiple times throughout the day, you can create a timer event in a form. This form must be open in order for the timer event to fire. So to keep this form active, but out of the way, you can hide it.
3. Performance:
   Some forms take a long time to load. This may be because it has a large, bound dataset, or it may be a complex form with several subforms. Whatever the reason, once the form is loaded, it makes sense to not close it again.

There are several ways to hide a form. The one you use depends on what you're trying to accomplish.

If you're using the form for global variables or a timer event (or both), the simplest thing is to open it in Hidden mode. To do that, create a macro and name it AutoExec. (see How do I run a macro or code when the database first starts? ). The AutoExec macro is a special macro that will automatically execute when the database opens. In this macro, you'll want to choose the OpenForm action, name the form, and choose Hidden in the Window Mode property.

Like this:

Access 2010-2013

 

Access 2000-2007

If some of your forms take a long time to load, however, this is not a good method. It will appear as if Access has frozen while the form or forms load at Start Up. In such cases, it is preferable to create a Splash Screen. A Splash Screen is simply a form which tells the user that the database is opening and to wait. This gives the user feedback and reassures them that everything is functioning normally.

I'll discuss splash screens in a later post.

But once the form is open, it makes sense to hide it rather than close it. To do that, you can use a button. I usually use the button wizard because it creates the button's Onclick event code with error trapping automatically. I use the Form Close wizard, which has a single line of code:

DoCmd.Close

I will replace that with the following:

Me.Visible = False

The next time you "open" the form, it will simply make the already open form visible.   So instead of opening a Form, you can instantly by make the form visible and set the focus on it like this:

Forms("MyFormName").Visible = True

Forms("MyFormName").SetFocus

How do I run a macro or code when the database starts?

Access has a special macro called Autoexec, which automatically executes when the database opens. You can use this macro to do practically anything: open one or more forms in hidden mode, relink your tables, pop up a tickler list, or whatever startup functions your application requires.

At its simplest, simply create a new macro, choose the Actions and associated arguments required, and save it under the name Autoexec. The next time you open your application, this macro will execute.

However, most experienced developers agree that macros are not the optimal solution. Most prefer VBA code to macros. Fortunately, it is simple to convert a macro to VBA code (see: How Do I Convert A Macro to VBA Code?) .

So a much more elegant solution would be to create an Autoexec macro that does what you want it to do. After you test it, convert it to a VBA function.

The conversion will create a new function called Autoexec() in a general module. Next delete all the actions from your existing Autoexec macro and replace it with the RunCode action, with Autoexec() as the function name. Like this:

Access 2010 and 2013

 

In Access 2000 - 2007

How Do I Convert A Macro to VBA Code?

Although Microsoft has made great strides in improving the capabilities of macros in Access, most experienced developers agree that Visual Basic for Applications (VBA) code is still far superior to using macros in Access client apps. (Web apps, of course, cannot use VBA, so the following is only pertinent to applications that run in the Access Client.)

So suppose you have a completed macro that you want to convert to VBA? Fortunately, Access makes it easy.

Access 2010 and 2013

Open the Macro in Design View

 

Click the File Tab then

1. Click Save As
2. Save Object As
3. Save As

You will get a pop-up Dialog box.  Give the macro a meaningful name in the top box and in the AS box, choose Module. Click OK.  

Click OK.  A second box will appear asking you if you want error trapping and comments.

Leave both checked and click Convert.

The macro will be converted and saved in a Module.

'------------------------------------------------------------
' MyMacro
'------------------------------------------------------------
Function MyMacro()
On Error GoTo MyMacro_Err

    DoCmd.TransferSpreadsheet acExport, 8, "table7", "table7.xls", True, ""

MyMacro_Exit:
    Exit Function

MyMacro_Err:
    MyMacroError$
    Resume MyMacro_Exit

End Function

'------------------------------------------------------------

In Access 2007

Open the macro in Design View, Click the Office Button (Pizza), Choose Save As, and you will immediately be given the Save As dialog.

A second box will appear asking you if you want error trapping and comments. Leave both checked and click Convert.

In Access 2003 or previous

In the Database Window, right-click on the macro you want to convert and choose Save As. Everything else works the same.