Access And Windows API ShowWindow ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

This is all about controlling Form/Report Views and changing them from one state to another, like Hide, Minimized, Maximized, and Normal with the use of Windows APIs. I was aware of Windows APIs and came across their usage in MS-Access long back, it was a Form that Fades-in and after a few seconds it disappears slowly in the Application Window.

This VBA Code is still with me. I could not go after Windows APIs till now and thought of doing something about it.

Recently, I came across some discussions about finding Access Form's Window handle value and the limitations of using FindWindow API Function. The FindWindow API finds only the open Forms with its Popup or Modal Property value set to Yes. Once the Form's window identification number is obtained we can change the window State as mentioned above with the use of ShowWindow API Function.

The Windows API Functions.

We will learn the usage of the following Windows API Functions in our trial runs to know how they work in MS-Access:

Public Const SW_HIDE As Long = &H0
Public Const SW_NORMAL As Long = &H1
Public Const SW_MINIMIZED As Long = &H2
Public Const SW_MAXIMIZED As Long = &H3
Public Const SW_CLOSE As Long = &H10 '16

Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long


Public Declare PtrSafe Function ShowWindow Lib "user32.dll" ( _
     ByVal hwnd As Long, _
     ByVal nCmdShow As Long) As Long
    
Public Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
    ByVal lParam As Long) As Long

Experiments in VBA Debug Window.

Open one of your Access Databases, copy and paste the above Code into a Standard Module at the Global Declaration area and save the Module. If your Computer is a 32 Bit System then the above line may run into Errors. In that case, remove the word PtrSafe from the declaration lines and save the Module.

Open one of your existing Forms in Normal View.
Note down the Title of the open Form.
Come back to the VBA Window (Alt+F11).
Select Immediate Window (Ctrl+G) option from the View Menu.
Now, type the following command (to run the FindWindow() API Function) directly on the Immediate Window to obtain the Windows Identification Number of the open Form.

 
? FindWindow(vbNullString,"Form1")
0

The FindWindow API takes two Parameters. The first string parameter is the Class Name of the Application. MS-Access Application class name is OMain.

The second string parameter is the Window's (Form's) Title Text.

Even though both parameters are mandatory only one of these parameter values is given and the other parameter is passed as NullString. In the above example, we have passed the vbNullString constant as the first parameter, and the second parameter is the Form's Title. The FindWindow() function will not find the Form's Title Text if it is not a Popup Form and returns 0.

Now, open Form1 in Design View.
Open the Form's Property Sheet and change the Popup property value to Yes.
Save the Form and Open it in Normal View again.
Try the above FindWindow command in the Debug Window one more time.

? FindWindow(vbNullString,"Form1")Result: 197654

If the above command was successful and returned a number, then you have succeeded in getting the Windows handle number similar to the above example.

If you were able to get the windows identification number, then we can call the ShowWindow () API Function from the Debug Window as follows to change its Form View to different modes:

x = ShowWindow(197654, SW_MINIMIZED)

Form1 is minimized and stationed at the bottom of the Application Window.

Application, Forms/Reports Windows Handle Property hwnd.

The MS-Access Application Object has a property that holds the Access Application Window's handle value. From within the Access Application, we can read the Application Window's Handle value as shown below:

hwnd = Application.hwndAccessApp 
OR
hwnd = FindWindow("OMain",vbNullString)

We cannot make all Forms and Reports into Popups for the sake of the ShowWindow API or for any other.

The Built-in .hwnd Property

Fortunately, we don't have to depend on the Popup Property or FindWindow API to get the Form or Report Windows identification number.

All open Forms and Reports have the expression.hwnd Property, which is already set with the Windows handle Number. All you have to do is to read this value directly and use it in the ShowWindow API function, to change its view mode to one of the constant values given in the Code at the top of this page, except the last one: SW_CLOSE.

Open one of your Forms (say Employees) in Normal View.
Go to the VBA Immediate Window (Alt+F11).
Type the following command to display the Employees Form's windows handle value:

hndl = Forms("Employees").hwnd 
? hndl
Result: 1508749

If you want to change the Employees form's current NORMAL View mode to MINIMIZED then call the ShowWindow API Function, with the form's window number, like the following example on the Debug Window:

whndl = Forms("Employees").hwnd
rtn = ShowWindow(whndl, SW_MINIMIZED)

OR

rtn = ShowWindow(Forms("Employees").hwnd,2)

You may try out other options 0-3, hide, normal, minimize, and maximize options on the open Form.

Open a Report in Print Preview Mode and try the above example as you did in the Employees Form.

To try out the SW_CLOSE option we need another Windows API the PostMessage() Function.

rtn = PostMessage(whndl, SW_CLOSE,  0, 0)

The PostMessage() Function accepts four parameters. The first parameter is the windows handle and the second parameter accepts the action to perform. The third and fourth parameters are not optional and zero values are passed.

The Sample Demo Database.

There is a Demo database with a few sample Forms and Reports attached. You may download and try it out. The Image of the Main Form is given below:

When the above Form is open the first two ListBoxes will be empty. Click on the 'Open Forms' Command Button to open sample forms and create their list in the ListBox. The second ListBox is for sample Reports. Only one of the first two ListBoxes will be enabled at one time.

Click on the Command Button on the top with the Caption: Click to Enable a disabled ListBox. Selecting an Item (Form or Report) from the List will enable the Windows State options. Click on the Window State option that you would like to set on the selected Form/Report.

If you select another Form or Report to set the selected window's state, then you need to click again on the already selected windows state option.

The Close All Command Button closes all open Forms/Reports including the above Main Form.

AccessWinAPI.zip