Beginning Access 2002 VBA (Programmer to Programmer)

  1. In this chapter, we used the BuildResultsTable procedure to build up the tblResults table from a given SQL string. We built the table by running a make-table query. See if you can rewrite the BuildResultsTable procedure to build the table using the Data Access Object hierarchy instead. Once the table has been built using DAO, the procedure should populate it with an append query, for example:

    INSERT INTO tblResults SELECT SalesID FROM

  2. Modify frmCriteria to allow the user to enter a name for the results table. Then modify cmdFind_click to use the name given to save the results table under (after first checking that a valid name has been entered, of course). This is a useful feature to add to the application. The user can then create any number of different queries and view the stored results from each at any time.

  3. Next (if you are feeling really brave) see if you can modify the BuildResultsTable so that it looks like this:

    Function BuildResultsTable(ByRef strSQL As String, _ ByVal strTableName As String, _ ByRef lngRecordsAffected As Long, _ Optional blnIndexed As Boolean = False, _ Optional strMethod As String = "Query") as Boolean

    This argument

    Does this

    strSQL

    Supplies the SQL statement that was built up from the selections made on the criteria selection form.

    strTableName

    Supplies the name for the table to be created.

    lngRecordsAffected

    Is used to return a long integer signifying the number of records placed into the new table.

    blnIndexed

    Is a Boolean value (default False ) used to indicate whether the new table should be indexed on the SalesID field. If this argument is not supplied, the field will not be indexed.

    strMethod

    Is used to specify what method will be used to build the new table. If this argument is " Query " or is not supplied, the table will be created using a make-table query. The alternative, used when strMethod is " DAO ", is to use DAO, which you should have completed in the previous exercise.

  4. Finally, see if you can modify the application so that it informs the user how many records met the criteria and asks whether the frmSales form should be displayed. Use this for the cmdFind_Click procedure on the criteria form and then put the required functionality into the DisplayResults procedure.

    Private Sub cmdFind_Click() Dim strSQL As String Dim lngRecordsAffected As Long If Not EntriesValid Then Exit Sub If Not BuildSQLString(strSQL) Then MsgBox "There was a problem building the SQL string" Exit Sub End If If Not BuildResultsTable(strSQL, "tblResults", lngRecordsAffected, strMethod:="DAO", _ blnIndexed:=True) Then MsgBox "There was a problem building the results table" Exit Sub End If If Not DisplayResults(lngRecordsAffected) Then MsgBox "There was a problem displaying the results" Exit Sub End If End Sub

 

Related

Категории