• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 9. Advanced Form Techniques > Switching a Form's RecordSource

Switching a Form's RecordSource

Many developers don't realize how easy it is to switch a form's RecordSource property at runtime. This is a great way to use the same form to display data from more than one table or query containing the same fields. It's also a great way to limit the data that's displayed in a form at a particular moment. Using the technique of altering a form's RecordSource property at runtime, as shown in Listing 9.1, you can dramatically improve performance, especially for a client/server application. This example is found in the frmShowSales form of the Chap9Ex database (see Figure 9.20).

Listing 9.1. Altering a Form's RecordSource at Runtime

Private Sub cmdShowSales_Click()

    'Check to see that Ending Date is later than Beginning Date.
    If Me.txtEndingDate < Me.txtBeginningDate Then
        MsgBox "The Ending Date must be later than the Beginning Date."
        Exit Sub
    End If

    'Create an SQL statement using search criteria entered by user and
    'set RecordSource property of ShowSalesSubform.

    Dim strSQL As String
    Dim strRestrict As String
    Dim lngX As Long

    lngX = Me.optSales.Value
    strRestrict = ShowSalesValue(lngX)

    'Create SELECT statement.
    strSQL = "SELECT DISTINCTROW tblCustomers.CompanyName,_
      qryOrderSubtotals.OrderID, "
    strSQL = strSQL & "qryOrderSubtotals.Subtotal ," &  _
        "tblOrders.ShippedDate "
    strSQL = strSQL & "FROM tblCustomers INNER JOIN _
      (qryOrderSubtotals INNER JOIN tblOrders ON "
    strSQL = strSQL & "qryOrderSubtotals.OrderID = " & _
        "tblOrders.OrderID) ON "
    strSQL = strSQL & "tblCustomers.CustomerID = tblOrders.CustomerID "
    strSQL = strSQL & "WHERE (tblOrders.ShippedDate _
    Between Forms!frmShowSales!txtBeginningDate "
    strSQL = strSQL & "And Forms!frmShowSales!txtEndingDate) "
    strSQL = strSQL & "And " & strRestrict
    strSQL = strSQL & " ORDER BY qryOrderSubtotals.Subtotal DESC;"

    'Set RecordSource property of ShowSalesSubform.
    Me.fsubShowSales.Form.RecordSource = strSQL

    'If no records match criteria, reset subform's
    'RecordSource property,
    'display message, and move focus to BeginningDate text box.
    If Me.fsubShowSales.Form.RecordsetClone.RecordCount = 0 Then
        Me.fsubShowSales.Form.RecordSource = _
        "SELECT CompanyName FROM tblCustomers WHERE False;"
        MsgBox "No records match the criteria you entered.", _
         vbExclamation, "No Records Found"
        'Enable control in Detail section.
        EnableControls Me, acDetail, True
        'Move insertion point to ShowSalesSubform.
    End If

    End Sub

Private Function ShowSalesValue(lngOptionGroupValue As Long) As String

    'Return value selected in Sales option group.

    'Define constants for option group values.
    Const conSalesUnder1000 = 1
    Const conSalesOver1000 = 2
    Const conAllSales = 3

    'Create restriction based on value of option group.
    Select Case lngOptionGroupValue
        Case conSalesUnder1000:
            ShowSalesValue = "qryOrderSubtotals.Subtotal < 1000"
        Case conSalesOver1000:
            ShowSalesValue = "qryOrderSubtotals.Subtotal >= 1000"
        Case Else
            ShowSalesValue = "qryOrderSubtotals.Subtotal = True"
    End Select
End Function




Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint