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.
DoCmd.OpenForm (<Form Name> , [<Form View>] ,[ <Filter Name> ], [<Where Condition>] , [<Data Mode>] , [<Window Mode>] , [<Open Args>])
Explaining the syntax parameters
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.
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.
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.
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.
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.
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.
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.
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.
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
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
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.