Learn Access Now! Chapter 20 Previous Section in Chapter 21 Next Section in Chapter 21
(This is section 5 of 7 in this chapter)
When you were learning about VBA in the previous chapter, you learned that you should only use VBA procedures when you cannot accomplish your needs with a macro. I followed this maxim in implementing the CD database. I used Macros in many places. There were a few places, however, where I needed to use VBA procedures. For example, I used procedures in both the Track List form and the Music Input form.
To see which procedures the database defines, open the Track List form in the Design window. Then, choose the Code option from the View menu. Microsoft Access, in turn, will display the VBA Editor, which you recognize from the previous chapter. In the Module window are two subroutines, as follows:
The Form_BeforeUpdate event procedure simply makes sure that the Group ID field of the Individual Tracks table is set to the same group as noted for the entire CD. I implemented this procedure using the following code:
Sub Form_BeforeUpdate(Cancel As Integer)
[Group ID] = Forms![Music Input]![Field18]
End Sub
Notice the code's use of object names to access individual objects in the form or table. You might not be familiar with the use of the Field18 object, however. This is the name of the group Combo Box object in the Music Input form. So, in effect, the Group ID field of the Individual Tracks table is being set to the same value that is in the Combo Box.
Notice that the Form_Error event procedure has been defined, as well. This procedure is called automatically by Microsoft Access if it detects an error while the form is displayed. As used here, the Form_Error event procedure handles an Access quirk. Assume you are looking through the database in Read Only mode (this is done if you choose Review Music from the Startup form). When you double-click your mouse on a sound clip icon, you can hear the clip; the OLE server linked to the object plays it. However, when you try to leave that record to select another, Access thinks you have edited the record (even though you have not). Since editing of records is not allowed in Read-Only mode, Access generates an error. The code attached to the Error event is designed to capture this type of error, and "undo" the edit that never occurred. This is done with the following code:
Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2278 Then
DoCmd DoMenuItem acFormBar, acEditMenu, acUndo
Response = acDataErrContinue
End If
End Sub
If error code 2278 occurs (this is the one Microsoft Access generates when you try to edit a record that cannot be edited), the code uses the DoCmd statement to choose the Undo option from the Access menus. The code then sets the Response value so Access ignores the error and displays no message. After this code is executed, you can then safely leave the record.
To see which procedures the database defines in the Music Input form, open the form in the Design window. Then, choose the Code option from the View menu. Access displays the VBA Editor, which you should be familiar with from the previous chapter. In the Object box, choose the Field18 object. This is the object name for the Combo Box object, which lets you choose different singing groups from the Groups table.
There are two procedures in the Module window that are used by the Field18 object. These are as follows:
Both of these events accomplish the same thing. I designed the procedures to update the caption of a Text Box object, as shown in the following code:
Sub Field18_Enter()
Text24.Caption = GetGroupName(Field18)
End Sub
The Text24 object is a Text Box that appears just to the right of the Combo Box. Setting the caption of this object causes Access to display the name of the singing group you have selected. The GetGroupName function returns the name of the group. You can view this function by choosing General in the Object box, and then choosing GetGroupName in the Procedure box. The code in this function is as follows:
Function GetGroupName(ByVal GroupID As Variant) As String
'open database and search for groupid
If Not IsNull(GroupID) Then
Dim GroupDB As Database, GroupSet As Recordset
Set GroupDB = DBEngine.WorkSpaces(0).Databases(0)
Set GroupSet = GroupDB.OpenRecordset("Groups", dbOpenDynaset)
GroupSet.FindFirst "[Group Id] = " & GroupID
If GroupSet.NoMatch Then
GetGroupName = ""
Else
GetGroupName = GroupSet("Group Name")
End If
End If
End Function
This code uses the object addressing techniques you learned in Chapter 20, "Using Visual Basic for Applications," to find a group name for the group that has the ID passed to the function. The function then returns this name to the calling function.
Learn Access Now! Chapter 20 Previous Section in Chapter 21 Next Section in Chapter 21