COMM 1A Application Building

1

• COMM 1A Application Building
• Unit 12

2
Introduction

• Objectives for the session
• Understand how to apply SQL within code
• Be aware of how to build up complex queries from
  components
• Recognise that functions can be used to test if a
  piece of code completes correctly
• Understand that Access queries can be modified
  dynamically

3
VBA and Databases

• Access Queries can only be used on Forms not
  objects
• SQL can be written against objects but there are
  limitations
• VBA code can be used to query the database using
  SQL in a way that is powerful and flexible
• You do not need to understand SQL if you can
  create queries

4
Example
5
Total number of Purchases

• Dim db As Database
• Dim rec As Recordset
• Dim totalno As Integer
• Dim totalcost As Single
• Dim strSQL As String
• 'Assign the reference to the current database
  object to a variable
• Set db CurrentDb()
• 'Assign the reference to the recordset that is
  the Purchase Order table to a variable
• Set rec db.OpenRecordset("Purchase Order")
• 'Assign the number of records in the recordset to
  a variable
• totalno rec.RecordCount
• 'Assign the value of that variable to a form
  object
• txtTotalNo totalno
• 'Close the recordset
• rec.Close

6
Total Cost of Purchases query
SELECT Sum(Number of itemsCost of item) AS
Total cost of purchases FROM Stock INNER JOIN
Purchase Order Line ON Stock.Item code
Purchase Order Line.Item code
7
Total and Average Cost

• 'Construct SQL statement that calculates total
  cost of purchases
• strSQL "SELECT Sum(Number of itemsCost of
  item) AS Total cost of purchases"
• strSQL strSQL " FROM Stock INNER JOIN
  Purchase Order Line ON "
• strSQL strSQL "Stock.Item code Purchase
  Order Line.Item code"
• Open a recordset based on the SQL statement and
  assign rec to point to that recordset
• Set rec db.OpenRecordset(strSQL, dbOpenDynaset)
• 'Assign the total cost element of the SQL
  statement to a variable
• totalcost rec("Total cost of purchases")
• 'Format the variable as currency and assign it to
  a form object
• txtTotalCost Format(totalcost, "currency")
• 'Calculate the average, format it as currency and
  assign it to a form object
• txtAverage Format(totalcost / totalno,
  "currency")
• 'Close the recordset
• rec.Close
• 'Move the focus to the command button
• cmdClose.SetFocus

8
Selection Criteria
9
Explanation

• An SQL selection query has 3 major components
• SELECT
• FROM
• WHERE
• There are 3 separate SQL selection queries
• The components are constructed for each of the
  three queries
• The 3 SELECT components are concatenated together
  and the same is done for FROM and WHERE
• SELECT, FROM and WHERE are concatenated to form a
  single SQL statement

10
Handling the Checkboxes

• 'A Checkbox has the default property of Value
• 'The Value property can be either True (-1) or
  False (0)
• 'depending whether it is selected.
• 'The Enabled property uses the same values
• txtAgeLower.Enabled chkAge
• txtAgeUpper.Enabled chkAge

11
Initialise SELECT

• Function BuildSQLString(strSQL As String) As
  Boolean
• 'An SQL statement is made up of a number of
  elements
• 'These declarations create strings that may be
  concatenated
• 'to form a single SQL statement
• Dim strSELECT As String
• Dim strFROM As String
• Dim strWHERE As String
• 'Select all customers
• strSELECT "Customer."
• 'From the Customer table
• strFROM "Customer"

12
Determine Sex

• 'If the Sex checkbox is selected
• If chkSex Then
• 'and if a sex is selected
• If Not IsNull(cboSex) Then
• cboSex.SetFocus
• strWHERE strWHERE " AND Customer.Sex
  " "'" cboSex.Text "'"
• End If
• End If

13
Determine Age

• ' If the Age checkbox is selected
• If chkAge Then
• 'and if a lower age is selected
• If Not IsNull(txtAgeLower) Then
• txtAgeLower.SetFocus
• Whatever is currently in WHERE is
  concatenated with a text string and field
• strWHERE strWHERE " And Customer.Age
  gt " txtAgeLower.Text
• End If
• 'and if an upper age is selected
• If Not IsNull(txtAgeUpper) Then
• txtAgeUpper.SetFocus
• Whatever is currently in WHERE is
  concatenated with a text string and field
• strWHERE strWHERE " AND Customer.Age
  lt " txtAgeUpper.Text
• End If
• End If

14
Purchase Between

• 'If the checkbox for purchase is selected
• If chkPurchase Then
• strSELECT strSELECT " , Purchase
  Order.Date"
• strFROM strFROM " INNER JOIN Purchase
  Order " _
• "ON (Customer.Forename Purchase
  Order.Forename) AND (Customer.Surname
  Purchase Order.Surname) "
• 'and if a lower start date is selected
• If Not IsNull(txtPurchaseLower) Then
• strWHERE strWHERE " AND Purchase
  Order.Date gt " _
• "" Format(txtPurchaseLower,
  "dd/mm/yy") ""
• End If
• 'and if an upper start date is selected
• If Not IsNull(txtPurchaseUpper) Then
• strWHERE strWHERE " AND Purchase
  Order.Date lt " _
• "" Format(txtPurchaseUpper,
  "dd/mm/yy") ""
• End If
• End If

15
Construct the Query

• 'Construct the Select statement
• strSQL "SELECT " strSELECT
• 'Add the From statement
• strSQL strSQL " FROM " strFROM
• 'Add the Where statement if it has any values
• If strWHERE ltgt "" Then strSQL strSQL " WHERE
  " Mid(strWHERE, 6)
• 'Since this is a function return True as
  indicating its successful completion
• BuildSQLString True
• End Function

16
Calling the Function

• Dim strSQL As String
• 'If the function did not complete successfully
• If Not BuildSQLString(strSQL) Then
• MsgBox "There was a problem building the SQL
  string"
• End If
• 'Display the completed SQL statemet
• MsgBox strSQL
• 'Update the SQL within the query definition of
  the DynamicSQL query
• CurrentDb.QueryDefs("DynamicSQL").SQL strSQL

17
Summary

• Use temporary queries to generate the SQL strings
• Create a simple to use interface
• Code each option and test separately
• Build powerful systems that provide flexibility