لطفا منتظر باشید ...
Practical Examples: Event Routines, User-Defined Functions, and Subroutines
The Chap7.mdb database includes two forms: frmClients and frmProjects. The frmClients form contains two command buttons. The first command button is used to save changes to the underlying record source (the tblClients table.) The code looks like this:Private Sub cmdSave_Click()
'Save changes to the client record
DoCmd.RunCommand acCmdSaveRecord
End Sub
The code, placed under the cmdSave command button on the frmClients form, executes the RunCommand method of the DoCmd object. The acCmdSaveRecord intrinsic constant, when used as a parameter to the RunCommand method, causes changes made to the form to be saved to the underlying data source.The second command button is used to undo changes made to the current record. The code looks like this:Private Sub cmdUndo_Click()
'Undo changes
DoCmd.RunCommand acCmdUndo
End Sub
This code is found under the cmdUndo button on the frmClients form. It executes the RunCommand method of the DoCmd object. The acCmdUndo intrinsic constant, when used as a parameter to the RunCommand method, undoes changes made to the form.The code originally located under the cmdViewProjects was generated by the command button wizard (as covered in Chapter 5, "What Every Developer Needs to Know About Forms"). It looked like this:Private Sub cmd_Click()
On Error GoTo Err_cmd_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmProjects"
stLinkCriteria = "[ClientID]=" & Me![txtClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmd_Click:
Exit Sub
Err_cmd_Click:
MsgBox Err.Description
Resume Exit_cmd_Click
End Sub
The code first declared two variables, one for the name of the form to be opened, and the other to hold the criteria used to open the form. It then assigned a value to the stDocName variable as well as to the stLinkCriteria variable. Finally, it used the OpenForm method of the DoCmd object to open the frmProjects form, passing the value in stLinkCriteria as the WHERE clause for the OpenForm method. This wizard-generated code is inefficient. It uses variables that are not necessary. More importantly, the use of the WHERE clause in the OpenForm method causes the form to open, filtering data to the data specified in the WHERE clause. Because the data is filtered, all projects travel over the network wire, even though only the projects that meet the criteria set in the WHERE clause are displayed. Although this is not of particular importance when your data is stored in an Access database (.mdb file), it makes a tremendous difference if the data is moved to a client/server database such as Microsoft SQL Server. To eliminate both problems, the code is changed as follows:Private Sub cmdViewProjects_Click()
On Error GoTo Err_cmdViewProjects_Click
DoCmd.OpenForm FormName:="frmProjects"
Exit_cmdViewProjects_Click:
Exit Sub
Err_cmdViewProjects_Click:
MsgBox Err.Description
Resume Exit_cmdViewProjects_Click
End Sub
Although it appears that the form is now opened without a WHERE clause, this is not the case. The key to the solution is found in the frmProjects form. The code in the Open event of the frmProjects form looks like this:Private Sub Form_Open(Cancel As Integer)
If Not IsLoaded("frmClients") Then
MsgBox "You must load this form from the Projects form", _
vbCritical, "Warning"
Cancel = True
Else
Me.RecordSource = "qryProjects"
End If
End Sub
This code first uses a user-defined function called IsLoaded to determine whether the frmClients form is loaded. (The mechanics of the IsLoaded function are discussed in the following text.) The function returns True if the frmClients form is loaded, and False if it is not. If the frmClients form is not loaded, a message is displayed to the user, and the loading of the frmProjects form is cancelled. If the frmClients form is loaded, the RecordSource property of the frmProjects form is set to a query called qryProjects. The qryProjects query is a parameter query that returns only those projects with a ClientID that matches the ClientID associated with the client displayed on the frmClients form.The IsLoaded function looks like this:Public Function IsLoaded(strFormName As String) As Boolean
Const FORMOPEN = -1
Const FORMCLOSED = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> FORMCLOSED Then
IsLoaded = True
Else
IsLoaded = False
End If
'IsLoaded = SysCmd(acSysCmdGetObjectState, acForm, strFormName)
End Function
The function declares two user-defined constants. These constants are intended to make the function more readable. The built-in SysCmd function is used to determine whether the form whose name is received as a parameter is loaded. The SysCmd function, when passed the intrinsic constant acSysCmdGetObjectState as the first argument and acForm as the second argument, attempts to determine the state of the form whose name is passed as the third argument. The IsLoaded function returns True to its caller if the form is loaded, and False if it is not. An alternative to this function is the following:Public Function IsLoaded(strFormName As String) As Boolean
IsLoaded = SysCmd(acSysCmdGetObjectState, acForm, strFormName)
End Function
This function is much shorter and more efficient, but is less readable. It simply places the return value from the SysCmd directly into the return value for the function.In addition to the save and undo that are included in the frmClients form, this version of the frmProjects form contains one other routine. The BeforeUpdate event of the form, covered in Chapter 9, "Advanced Form Techniques," executes before the data underlying the form is updated. The code in the BeforeUpdate event of the frmProjects form looks like this:Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtProjectBeginDate.Value > _
Me.txtProjectEndDate.Value Then
MsgBox "Project Start Date Must Precede " & _
"Project End Date"
Cancel = True
End If
End Sub
It tests to see whether the project begin date falls after the project end date. If so, a message is displayed to the user, and the update is cancelled.
