| Microsoft Office 2000/Visual Basic Programmer's Guide | |
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, query, or 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. Once 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 VBA, you can use the name of an existing table or query, or an SQL statement. The easiest way to create an SQL statement to use in code, whether from within an Access module or another Office application, is to use the Access query design grid to create a query that displays the appropriate records. Once 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 (Figure 5.1) shows a query created in the query design grid that selects all fields from the Customers table for the customer named B's Beverages.
Figure 5.1 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
The ShowCustomerRecord procedure is available in the modRecordsetCode module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
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 like this:
DoCmd.OpenReport ReportName:="CustomerPhoneList", _
WhereCondition:="Country = " & "'" & strCountry & "'"
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:="Invoice", _
WhereCondition:="CompanyName = " & """" & strCountry & """"
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 form's or subform's records. 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 ChangeRecordsetProperty procedure is available in the modRecordsetCode module in Solutions9.mdb in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
The Recordset property of forms is new in Access 2000. 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 need to programmatically work with the data contained in the records displayed in a form, you will need to use the DAO RecordsetClone property or the 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 may also change the RecordSource, RecordsetType, and RecordLocks properties. In addition, other data-related properties may also be overridden; for example, the Filter, FilterOn, OrderBy, and OrderByOn properties may all be affected when you change the Recordset property of a form.
To see sample code that displays different Access reports depending on the criteria supplied by the user, see the ShowAccessReports.doc file in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.