Exercises

team lib
  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   
 
team lib


Beginning Access 2002 VBA
Beginning Access 2002 VBA (Programmer to Programmer)
ISBN: 0764544020
EAN: 2147483647
Year: 2003
Pages: 256

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