Form Record Sources

3 4

The data displayed and modified on a form comes from its record source (also called a data source). When a record source has been specified for a form, that form is called a bound form. (Similarly, a bound control displays data from a specific field in the form’s data source.) Unlike Visual Basic forms (for example), Access forms can be directly bound to data by simply selecting the data source (usually a table or query) as the form’s Record Source property. Once you select the record source, all of its fields are available for placement on the form, using the field list.

In addition to selecting a table or query for a form’s Record Source property, you can also use a SQL statement to get data for a form.

Tables and Queries

Specifying a table or query as a form’s record source is easy. If you create the form by using the Form Wizard, you can choose a table or query as the record source for the new form in the New Form dialog box, as shown in Figure 5-51.

figure 5-51. choose a table or query as a form’s record source in the new form dialog box.

Figure 5-51. Choose a table or query as a form’s record source in the New Form dialog box.

SQL Statements

Sometimes you won’t have a saved query that does the filtering or sorting you need, and you’ll need to create a record source for a form on the fly. You can do this with a SQL statement. (You don’t have to be an expert in the SQL language—Access allows you to create SQL statements visually by using the Query Builder.)

For more information on using queries, see Chapter 9, "Using Queries to Select Data."

To create a SQL statement that filters the fpriBooksAndVideos form in the Crafts database for books published in 1980 or later, follow these steps:

  1. Open the fpriBooksAndVideos form in Design view.
  2. If the properties sheet is not open, open it by pressing F4.
  3. Click the Data tab of the properties sheet, and then click the Record Source property.
  4. Click the Build button, which appears to the right of the property, as shown in Figure 5-52.

    figure 5-52.click the build button to create a sql statement record source.

    Figure 5-52. Click the Build button to create a SQL statement record source.

  5. A message box appears, asking whether you want to create a query based on the table. Click Yes.
  6. The Query Builder window opens, much the same as when you create a query, but without the Query Design toolbar.
  7. Drag the asterisk from the top of the tblBooksAndVideos field list to the query grid.
  8. Drag the PublicationYear field to the query grid, and then clear the Show check box to prevent this field from appearing twice in the recordset.
  9. Type >="1980" in the Criteria cell for the PublicationYear field. Figure 5-53 shows the completed SQL statement in the Query Builder.

    figure 5-53.the sql statement looks like this in the query builder.

    Figure 5-53. The SQL statement looks like this in the Query Builder.

  10. Close the Query Builder window, and click Yes in the confirmation message box that appears.
  11. The SQL statement now appears as the Record Source property for the form, as shown in Figure 5-54.

    figure 5-54.the finished sql statement appears in the record source property of the form’s properties sheet.

    Figure 5-54. The finished SQL statement appears in the Record Source property of the form’s properties sheet.

If you want to see the entire SQL statement, open the Query Builder again and switch to SQL view. The SQL statement is listed here:

 SELECT tblBooksAndVideos.* FROM tblBooksAndVideos WHERE (((tblBooksAndVideos.PublicationYear)>="1980")); 

note


Access 2002 has a higher limit (32,750 characters) for the size of SQL statements used as record and row sources. If you want to use a database in both Access 2002 and Access 2000, either keep your SQL statement shorter than 2000 characters or use a saved query instead of a SQL statement.

The Books And Videos form will now display only books published in 1980 or later.

Saved Queries vs. SQL Statement Record Sources

You can use either a saved query or a SQL statement as the record source of a form or report. In earlier versions of Access, using saved queries led to some performance improvement, but in Access 2002, you’ll find little difference in performance between saved queries and SQL statements. However, if you need to use the same record source for several database objects (such as a form and a report, or several reports), you’re better off creating and saving a query rather than creating a SQL statement as the record source of each form or report.

Once you have created the query and selected it as the record source for all the database objects that need it, any changes you make to the query will be picked up automatically by all the forms or reports that use the query as their record source. With SQL statements, on the other hand, you have to open each form or report in turn and make the same changes to the SQL statement. This process is tedious and can also lead to errors.

Recordsets

You can use either Data Access Objects (DAO) or ActiveX Data Objects (ADO) recordsets as record sources for forms, but only in VBA code. The first code sample that follows assigns a DAO recordset to a form; the second code sample assigns an ADO recordset to the same form. Both procedures use the same SQL statement created in the previous example except that the ADO procedure filters for books published in 1980 or later.

 Public Function ApplyDAORecordset()     Dim dbs As DAO.Recordset     Dim rst As DAO.Recordset     Dim strSQL As String     Dim frm As Access.Form          strSQL = "SELECT * FROM tblBooksAndVideos " & _         "WHERE PublicationYear>='1980’;"     Set dbs = CurrentDb     Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)     DoCmd.OpenForm "fpriBooksAndVideos"     Set frm = Forms![fpriBooksAndVideos]     frm.Recordset = rst     End Function Public Sub ApplyADORecordset()     Dim frm As Access.Form     Dim strSQL As String     Dim rst As ADODB.Recordset     strSQL = "SELECT * FROM tblBooksAndVideos " & _         "WHERE PublicationYear>='1980’;"     DoCmd.OpenForm "fpriBooksAndVideos"     Set frm = Forms![fpriBooksAndVideos]     Set rst = New ADODB.Recordset     rst.Open strSQL, CurrentProject.Connection, adOpenKeyset,          adLockOptimistic     Set frm.Recordset = rst     End Sub 



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net