لطفا منتظر باشید ...
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."
txtBeginningDate.SetFocus
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"
Me.txtBeginningDate.SetFocus
Else
'Enable control in Detail section.
EnableControls Me, acDetail, True
'Move insertion point to ShowSalesSubform.
Me.fsubShowSales!txtCompanyName.SetFocus
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
Figure 9.20. Changing the RecordSource property of a form at runtime.
Listing 9.1 begins by storing the value of the optSales option group on the frmShowSales main form into a Long Integer variable. It calls the ShowSalesValue function, which declares three constants; then it evaluates the parameter that was passed to it (the Long Integer variable containing the option group value). Based on this value, it builds a selection string for the subtotal value. This selection string becomes part of the SQL statement used for the subform's record source and limits the range of sales values displayed on the subform.The ShowSales routine builds a string containing a SQL statement, which selects all required fields from the tblCustomers table and qryOrderSubtotals query. It builds a WHERE clause that includes the txtBeginningDate and txtEndingDate from the main form as well as the string returned from the ShowSalesValue function.When the SQL statement has been built, the RecordSource property of the fsubShowSales subform control is set equal to the SQL statement. The RecordCount property of the RecordsetClone (the form's underlying recordset) is evaluated to determine whether any records meet the criteria specified in the RecordSource. If the record count is zero, no records are displayed in the subform, and the user is warned that no records met the criteria. However, if records are found, the form's Detail section is enabled, and focus is moved to the subform.
