Form object (Access)
A Form object refers to a particular Microsoft Access form.
A Form object is a member of the Forms collection, which is a collection of all currently open forms. Within the Forms collection, individual forms are indexed beginning with zero. You can refer to an individual Form object in the Forms collection either by referring to the form by name, or by referring to its index within the collection.
If you want to refer to a specific form in the Forms collection, it's better to refer to the form by name because a form's collection index may change. If the form name includes a space, the name must be surrounded by brackets ([ ]).
|AllForms ! formname||
|AllForms ![ form name]||
|AllForms (" formname")||
|AllForms ( formname)||
Each Form object has a Controls collection, which contains all controls on the form. You can refer to a control on a form either by implicitly or explicitly referring to the Controls collection. Your code will be faster if you refer to the Controls collection implicitly. The following examples show two of the ways you might refer to a control named NewData on the form called OrderForm:
' Implicit reference. Forms!OrderForm!NewData
' Explicit reference. Forms!OrderForm.Controls!NewData
The next two examples show how you might refer to a control named NewData on a subform
ctlSubForm contained in the form called OrderForm:
Links provided by Luke Chung, FMS, Inc..
- Microsoft Access Form Tips and Avoiding Common Mistakes
- Microsoft Office Access 2007 Form Design Tips
The following example shows how to use TextBox controls to supply date criteria for a query.
UtterAccess members can download a database that contains this example.
Sample code provided by the UtterAccess community.
Private Sub cmdSearch_Click() Dim db As DAO.Database Dim qd As QueryDef Dim vWhere As Variant Set db = CurrentDb() On Error Resume Next db.QueryDefs.Delete "Query1" On Error GoTo 0 vWhere = Null vWhere = vWhere & " AND [PayeeID]=" + Me.cboPayeeID If Nz(Me.txtEndDate, "") <> "" And Nz(Me.txtStartDate, "") <> "" Then vWhere = vWhere & " AND [RefundProcessed] Between #" & _ Me.txtStartDate & "# AND #" & Me.txtEndDate & "#" Else If Nz(Me.txtEndDate, "") = "" And Nz(Me.txtStartDate, "") <> "" Then vWhere = vWhere & " AND [RefundProcessed]>=#" _ + Me.txtStartDate & "#" Else If Nz(Me.txtEndDate, "") <> "" And Nz(Me.txtStartDate, "") = "" Then vWhere = vWhere & " AND [RefundProcessed] <=#" _ + Me.txtEndDate & "#" End If End If End If If Nz(vWhere, "") = "" Then MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _ "Search Cancelled.", vbInformation, "Search Canceled." Else Set qd = db.CreateQueryDef("Query1", "SELECT * FROM tblRefundData? & _ " WHERE " & Mid(vWhere, 6)) db.Close Set db = Nothing DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly End If End Sub
The following example shows how to use the BeforeUpdate event of a form to require that a value be entered into one control when another control also has data.
Sample code provided by the Microsoft Access 2010 Programmer's Reference.
Private Sub Form_BeforeUpdate(Cancel As Integer) If (IsNull(Me.FieldOne)) Or (Me.FieldOne.Value = "") Then ' No action required Else If (IsNull(Me.FieldTwo)) or (Me.FieldTwo.Value = "") Then MsgBox "You must provide data for field 'FieldTwo', " & _ "if a value is entered in FieldOne", _ vbOKOnly, "Required Field" Me.FieldTwo.SetFocus Cancel = True Exit Sub End If End If End Sub
The following example shows how to use the OpenArgs property to prevent a form from being opened from the Navigation Pane.
Private Sub Form_Open(Cancel As Integer) If Me.OpenArgs() <> "Valid User" Then MsgBox "You are not authorized to use this form!", _ vbExclamation + vbOKOnly, "Invalid Access" Cancel = True End If End Sub
The following example shows how to use the WhereCondition argument of the OpenForm method to filter the records displayed on a form as it is opened.
Private Sub cmdShowOrders_Click() If Not Me.NewRecord Then DoCmd.OpenForm "frmOrder", _ WhereCondition:="CustomerID=" & Me.txtCustomerID End If End Sub
About the contributors
Luke Chung is the founder and president of FMS, Inc., a leading provider of custom database solutions and developer tools.
UtterAccess is the premier Microsoft Access wiki and help forum.
Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.