Initiating or Opening a Windows Form in Microsoft Access

An introduction to Windows forms

A Windows form is a component that contains many controls. These forms help to insert data, display data, and also to update or delete data in the database. In short, they act as an interface between the user and the database.

Microsoft Access provides form controls directly to you in the host window, and thankfully, they don’t require much coding. These are easy-to-use forms compared to the other GUI forms offered in other programming languages or the VBA Editor.

Form controls such as text box, combo box, etc., can directly be connected to the fields of various tables through the Properties window. This saves you a lot of time and reduces coding and maintenance.

Here is a picture of a developed form with several controls in it.

To create a form , we can use the Create menu. It offers a variety of forms to choose from based on your requirements.

Opening a form during runtime

Docmd.Openform is the method which can be used to load / open a form during runtime.

Syntax

DoCmd.OpenForm (<Form Name> , [<Form View>] ,[ <Filter Name> ], [<Where Condition>] , [<Data Mode>] , [<Window Mode>] , [<Open Args>])

Explaining the syntax parameters

Form Name

The name of the form — duh! It can be found in the “All Access Objects" window on the left side of the Access application. Some form names of a sample project are available in the image below for reference.

Form View

There are several values for this as below. This parameter is optional.

  • acDesign has a value of 1 and opens the form in Design View — (i.e) enables the user to edit the opened form.
  • acFormDS has a value of 3 and opens the form in Datasheet View .
  • acFormPivotChart has a value of 5 and opens the form in PivotChart View.
  • acFormPivotTable has a value of 4 and opens the form in PivotTable View.
  • acLayout has a value of 6 and opens the form in Layout View.
  • acNormal has a value of 0 and opens the form in a Normal View.
  • acPreview has a value of 2 and opens the form in Print Preview.

If this argument is not passed, then acNormal becomes the default view and the form opens with a normal view.

Filter Name

The query string or the “Name of a query" that has already been created in the Access project.

Note: Any record source should not be used here.

Below, you can see a sample image of a query in an Access project. Several queries were created and are available on the left side.

Three views (Datasheet View, Design View, and SQL View ) are available under the Design menu to open any query. An “SQL View" of a query is opened for reference.

Where Condition

The where clause of the SQL query above, but without the “where" keyword. It is nothing but a condition that is automatically added to the previous parameter Filter Name.

Data Mode

The mode in which the form needs to be opened while in Form View or Datasheet View. It can be any of the options below. This is an optional argument.

  • acFormAdd with the value 0: we cannot edit existing records but can add new ones.
  • acFormEdit with the value 1: we can add new records and also edit the existing ones.
  • acFormPropertySettings with the value -1: we are allowed to change the properties of the form.
  • AcFormReadOnly with the value 2: we can only view the records of the interlinked table or database through the opened form interface.

acFormPropertySettings is the default value if data mode is not passed as an argument.

Window Mode

Sets the mode of the display window. It is an optional argument. The value can be any of the below values:

  • acDialog with the value 3. It sets the popup and modal properties of the form or report to “Yes"
  • acHidden with the value 1. It hides the report or the form.
  • acIcon with the value 2. This opens the form or report but keeps it minimized in the Windows taskbar.
  • AcWindowNormal with the value 0. This opens the form or report using the mode set using its properties. This is the default value if this argument is not passed.

Open Args

This is also an optional argument which can be used in both expressions and macros. It opens the form with a specific record whose value is provided in this argument. Once the form is open, the control can be moved back and forth or to any other specific record using the findRecord method.

Examples

Example 1

The code below opens an employee form in normal mode with all default options. However, all the default options have been provided here to help you understand the example better.

Private Sub Command583_Click()

    'set a value for the whereclause
    searchCriteria = "[Employee Details].[Designation] =" & "'Teacher'"
    
    'assign a query value to a variable for easy use
    strQuery = "Select * from [Employee Details]"
    
    'Open the form with the specified modes
    DoCmd.OpenForm "Employee Details", acNormal, strQuery, searchCriteria, acFormEdit, acWindowNormal
    
End Sub

Here’s a screenshot for when the corresponding command button is clicked.

Example 2

This code has different modes and a different whereclause. The display mode is datasheet view here.

Private Sub Command583_Click()

    'set a value for the whereclause
    searchCriteria = "[Employee Details].[Experience] =" & "‘Primary school teacher’"
    
    'assign a query value to a variable for easy use
    strQuery = "Select * from [Employee Details]"
    
    'Open the form with the specified modes
    DoCmd.OpenForm "Employee Details", acFormDS, strQuery, searchCriteria, acFormReadOnly, acDialog
    
    
End Sub

Example 3

Code below demonstrates a layout view.

Private Sub Command583_Click()

    'set a value for the whereclause
    searchCriteria = "[Employee Details].[Experience] =" & "‘High school teacher’"
    
    'assign a query value to a variable for easy use
    strQuery = "Select * from [Employee Details]"
    
    'Open the form with the specified modes
    DoCmd.OpenForm "Employee Details", acLayout, strQuery, searchCriteria, acFormReadOnly, acWindowNormal
    
End Sub

Conclusion

Essentially, the Docmd.OpenForm method is used to open a form on any event — like another form load or the click of a button. The parameters that the method offers help us to alter the display mode, the list of records to be displayed, and the edit mode of the form.

I recommend trying it out with all the available optional parameter values on any dummy database to help you understand these arguments more thoroughly.

Leave a Reply

Your email address will not be published. Required fields are marked *