The Data Behind Forms and Reports

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Most of the forms you create will be designed to display or collect data. Forms can display data for viewing, editing, or input. Forms are also used to create dialog boxes that collect information from a user, but do not display data. Reports display static data only, and aren't used to edit or collect data.

The source of the data behind a form or report is specified by the object's RecordSource property. The RecordSource property can be a table, a query, or a Structured Query Language (SQL) statement. You can display subsets of the data contained in the object's RecordSource property by using the Filter property to filter the data or by using the wherecondition argument of the OpenForm or OpenReport method to specify a subset of data. When you have specified a record source for a form or report, you can use the field list (in form or report Design view) to drag fields from the object's source of data to the object.

If you set the RecordSource property by using Microsoft® Visual Basic® for Applications (VBA), you can use the name of an existing table or query, or a SQL statement. The easiest way to create a SQL statement to use in code, whether from within a Microsoft® Access module or another Microsoft® Office application, is to use the Access query design grid to create a query that displays the appropriate records. When the query contains the records you want, click SQL View on the View menu and copy the SQL string that defines your query. You can then paste the SQL string into your VBA code and replace any hard-coded criteria with variables that will contain the data you want to use as criteria.

The following figure shows a query created in the query design grid that selects all fields from the Customers table for the customer named B's Beverages.

Specifying Criteria in the Query Design Grid

The SQL view for this query contains the following SQL statement:

SELECT * FROM Customers WHERE CompanyName = "B's Beverages";

You can modify this SQL statement for use in the following VBA procedure so that it will display a single customer record for any company passed in the strCompanyName variable:

Option Explicit
Dim frmTempForm As Form

Sub ShowCustomerRecord(strCompanyName As String)
   Dim strSQL As String

   strSQL = "SELECT * FROM Customers WHERE CompanyName = " _
      & """" & strCompanyName & """"
   Set frmTempForm = New Form_Customers
   With frmTempForm
      .RecordSource = strSQL
      .Visible = True
   End With
End Sub

Specifying String Criteria by Using Variables in Code

When you specify criteria for a query, filter, or wherecondition argument from code, you typically use a variable. For example, you could specify the wherecondition argument of the OpenReport method as in the following:

DoCmd.OpenReport ReportName:="CustomerPhoneList", _
      WhereCondition:="CompanyName = " & "'" & strCompanyName & "'"

When the criteria used is a string, the variable can be surrounded with single quotation marks ('). However, if the value of the variable contains a single quotation mark, this technique will not work. For example, if you are searching for records that match the criteria "CompanyName = 'B's Beverages'", you will encounter errors. If there is any chance that a variable will contain a value that itself contains a single quotation mark, you should surround the variable with two sets of double quotation marks ("), as shown in the following example:

DoCmd.OpenReport ReportName:="CustomerPhoneList", _


   WhereCondition:="CompanyName = " & """" & strCompanyName & """"

For more information about using quotation marks in strings, search the Microsoft® Access Visual Basic® Reference Help index for "quotation marks," and then open the topic "Quotation Marks in Strings."

When you are working with forms, you can also use the new Recordset property to specify the Recordset object that contains the records of the form or the subform. The following example illustrates how to change the source of data for a currently open form:

Sub ChangeRecordsetProperty()
   Dim frmNewRecords      As Form
   Dim rstNewRecordset   As New ADODB.Recordset

   Call ShowCustomerRecord("B's Beverages")
   Stop ' View Customers form containing 1 record.

   Set frmNewRecords = Forms(Forms.Count - 1)
   rstNewRecordset.Open "SELECT * FROM Customers", _
      CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   Set frmNewRecords.Recordset = rstNewRecordset
   Stop ' View Customers form containing 91 records.
End Sub

The Recordset property of forms is new in Access. You use the Recordset property to specify or determine the Recordset object representing a form's source of data. The recordset represented by the Recordset property is a read-only recordset. If you must programmatically work with the data contained in the records displayed in a form, you must to use the Data Access Object (DAO) RecordsetClone property or the ActiveX Data Objects (ADO) Clone method to create a second recordset that you can manipulate with VBA code. The Recordset property can be accessed only by using VBA code and can be used to bind multiple forms to a single recordset or to synchronize multiple forms or multiple Recordset objects. When you change a form's Recordset property, you must use the Set statement, as illustrated in the preceding code sample.

Note   Changing a form's Recordset property might also change the RecordSource, RecordsetType, and RecordLocks properties. In addition, other data-related properties also might be overridden, for example, the Filter, FilterOn, OrderBy, and OrderByOn properties might all be affected when you change the Recordset property of a form.

See Also

Working with Reports, Forms, and Data Access Pages | Referring to Open Objects | Working with Controls on Forms and Reports | Working with Data Access Pages