لطفا منتظر باشید ...
Practical Examples: Putting Advanced Techniques to Use
The examples in this section put into practice all that you have learned throughout this chapter. Concepts covered include the use of Null, intrinsic constants, and type structures.
Examples of Null, the DoCmd Object, and Intrinsic Constants
The following event routine illustrates how you could view all the projects associated with the selected client. It illustrates the importance of the ability to work with Null values and intrinsic constants.Private Sub cmdViewProjects_Click()
On Error GoTo Err_cmdViewProjects_Click
'Evaluate the ClientID text box to determine if it is null
'If it is null, display a message to the user
'Otherwise, save the current record and open the
'projects form (which is set up to only show projects
'related to the selected client)
If IsNull(Me.txtClientID.Value) Then
MsgBox "You Must Enter Client Information Before " & _
"Viewing the Projects Form"
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm FormName:="frmProjects"
End If
Exit_cmdViewProjects_Click:
Exit Sub
Err_cmdViewProjects_Click:
MsgBox Err.Description
Resume Exit_cmdViewProjects_Click
End Sub
The routine first invokes error handling (discussed in Chapter 16); then uses the IsNull function to test whether the user has entered a ClientID. The IsNull function returns True if the value in the txtClientID control is Null. If it is, the code displays an error message. If the txtClientID control contains a non-Null value, the code performs two methods on the DoCmd object.The first method performed on the DoCmd object is the RunCommand method. This method receives the constant associated with the name of the menu command you want to execute. The use of intrinsic constants makes this code more readable, and the RunCommand method makes it much easier to call menu commands from code. The second method performed on the DoCmd object is OpenForm, which opens the frmProjects form. The code programmatically sets the RecordSource property of the frmProjects form to a query that only displays projects associated with the currently selected customer.
An Example of Using a Type Structure
If many parts of an application require the same information that is stored in a specific table, it would be inefficient to read the data from this table each time the application needs it. It would be much more efficient to read this data once, when the application loads, and store it in a type structure. Because it remains in memory at all times, you can efficiently retrieve it whenever needed. The type structure is defined, and a Public Type variable based on the type structure is declared in a module's Declarations section. It looks like this:Type CompanyInfo
SetUpID As Long
CompanyName As String * 50
Address As String * 255
City As String * 50
StateProvince As String * 20
PostalCode As String * 20
Country As String * 50
PhoneNumber As String * 30
FaxNumber As String * 30
DefaultPaymentTerms As String * 255
DefaultInvoiceDescription As String
End Type
Public typCompanyInfo As CompanyInfo
You must build a subroutine that is invoked when your startup form is first loaded. This routine populates all the elements of the type structure. The routine looks like this:Sub GetCompanyInfo()
Dim strSubName As String
Dim rst As ADODB.Recordset
'Instantiate and open a recordset
'based on the tblCompanyInfo table
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "Select * from tblCompanyInfo", Options:=adCmdText
'Populate the elements of the type structure
'with data from the table
With typCompanyInfo
.SetUpID = rst!SetUpID
.CompanyName = rst!CompanyName
.Address = rst!Address
.City = rst!City
.StateProvince = rst!StateOrProvince
.PostalCode = rst!PostalCode
.Country = rst!Country
.PhoneNumber = rst!PhoneNumber
.FaxNumber = rst!PhoneNumber
End With
'Close the recordset and destoy the object
rst.Close
Set rst = Nothing
End Sub
Don't be concerned with the recordset handling included in this routine. Instead, notice that the code loads the value from each field in the first (and only) record of the tblCompanyInfo table into the elements of the Global Type variable. Here's an example of how the code uses the Type variable:Sub PopulateControls()
'Populate the text boxes on the report
'with data from the type structure
txtCompanyName.Value = Trim(typCompanyInfo.CompanyName)
txtAddress.Value = Trim(typCompanyInfo.Address)
txtCityStateZip.Value = Trim(typCompanyInfo.City) & ", " & _
Trim(typCompanyInfo.StateProvince) & _
" " & Format(Trim(typCompanyInfo.PostalCode), "!&&&&&-&&&&")
txtPhoneFax.Value = "PHONE: " & _
Format(Trim(typCompanyInfo.PhoneNumber), "(&&&)&&&-&&&&") & _
" FAX: " & _
Format(Trim(typCompanyInfo.FaxNumber), "(&&&)&&&-&&&&")
End Sub
This routine populates four different controls on a form with the company information retrieved from the elements of the Global Type variable.
