Now that you know how to build a filter to limit what the user sees, you can probably surmise that using a filter is a good way to open another form or report that displays information related to the current record or set of filtered records in the current form. This section shows you how to do this for both forms and reports. Later in this section, you will learn how to use events in class modules to build sophisticated links.
You’ve already seen the frmContactSummary form (see Figure 25-18) that uses a simple filter to link from the record selected in that form to full details in the frmContacts form. You can find similar code behind the fsubCompanyContacts form used as a subform in the frmCompanies form. Figure 25-21 shows you the frmCompanies form and the Edit This buttons we provided on the subform.
Figure 25-21. You can provide a link from the Companies / Organizations form to details about a particular contact.
To see the details for a particular contact, the user clicks the Edit This button on the chosen contact record, and code opens the frmContacts form with that contact displayed. The code behind the button is as follows:
Private Sub cmdEdit_Click()
' Open Contacts on the related record
DoCmd.OpenForm "frmContacts", WhereCondition:="ContactID = " & Me.ContactID
End SubAnd code in the form’s Current event prevents the user from clicking the button when in a new record that doesn’t have a contact ID, as shown here:
Private Sub Form_Current()
' Disable "edit this" if on a new row
Me.cmdEdit.Enabled = Not (Me.NewRecord)
End SubSetting the button’s Enabled property to False causes it to appear dimmed, and the user cannot click the button.
Now, let’s look at using the Filter technique to link to related information in a report. Open the frmInvoices form in the Conrad Systems Contacts application (Contacts.accdb) and move to an invoice that looks interesting (we used invoice number 50). Click Print to open the Print Invoices form (fdlgInvoicePrintOptions) that gives you the option to see the current invoice formatted in a report, display all unprinted invoices in a report, display only unprinted invoices for the current customer, or print all invoices currently shown in the frmInvoices form. (You can use Search to filter the displayed invoices to the ones you want.) Select the Current Invoice Only option and click Print again to see the invoice in a report, as shown in Figure 25-22. (The figure shows you the sequence you see after clicking the Print button on the frmInvoices form. The Print Invoices dialog box closes after opening the report.)
Figure 25-22. You can ask to print only the current invoice in the Conrad Systems Contacts database.
The code from the Click event of the Print button in the fdlgInvoicePrintOptions form is as follows:
Private Sub cmdPrint_Click()
Dim strFilter As String, frm As Form
' Set an error trap
On Error GoTo cmdPrint_Error
' Get a pointer to the Invoices form
Set frm = Forms!frmInvoices
Select Case Me.optFilterType.Value
' Current Invoice
Case 1
' Set filter to open Invoice report for current invoice only
strFilter = "[InvoiceID] = " & frm!InvoiceID
' All unprinted invoices
Case 2
' Set filter to open all unprinted invoices
strFilter = "[InvoicePrinted] = 0"
' Unprinted invoices for current company
Case 3
' Set filter to open unprinted invoices for current company
strFilter = "[CompanyID] = " & frm!cmbCompanyID & _
" AND [InvoicePrinted] = 0"
' Displayed invoices (if filter set on form)
Case 4
' Check for a filter on the form
If IsNothing(frm.Filter) Then
' Make sure they want to print all!
If vbNo = MsgBox("Your selection will print all " & _
"Invoices currently in the " & _
"database. Are you sure you want to do this?", _
vbQuestion + vbYesNo + vbDefaultButton2, _
gstrAppTitle) Then
Exit Sub
End If
' Set "do them all" filter
strFilter = "1 = 1"
Else
strFilter = frm.Filter
End If
End Select
' Hide me
Me.Visible = False
' Have a filter now. Open the report on that filter
DoCmd.OpenReport "rptInvoices", acViewPreview, , strFilter
' Update the Print flag for selected invoices
CurrentDb.Execute "UPDATE tblInvoices SET InvoicePrinted = -1 WHERE " & _
strFilter
' Refresh the form to show updated Printed status
frm.Refresh
' Execute the Current event on the form to make sure it is locked correctly
frm.Form_Current
cmdPrint_Exit:
' Clear the form object
Set frm = Nothing
' Done
DoCmd.Close acForm, Me.Name
Exit Sub
cmdPrint_Error:
' Got an error
' If Cancel, that means the filter produced no Invoices
If Err = errCancel Then
' Exit - report will display "no records" message
Resume cmdPrint_Exit
End If
' Got unknown error - display and log
MsgBox "Unexpected error while printing and updating print flags: " & _
Err & ", " & _
Error, vbCritical, gstrAppTitle
ErrorLog Me.Name & "_Print", Err, Error
Resume cmdPrint_Exit
End SubThis first part of this procedure sets an object reference to the frmInvoices form to make it easy to grab either the InvoiceID or the CompanyID and to reference properties and methods of the form’s object. The Select Case statement examines which option button the user selected on fdlgInvoicePrintOption and builds the appropriate filter for the report. Notice that if the user asks to print all the invoices currently displayed on the form, the code first looks for a user-applied filter on the frmInvoices form. If the code finds no filter, it asks if the user wants to print all invoices. The code uses the filter it built (or the current filter on the frmInvoices form) to open the rptInvoices report in Print Preview. It also executes a Structured Query Language (SQL) UPDATE statement to flag all the invoices the user printed. If you look at code in the Current event of the frmInvoices form, you’ll find that it locks all controls so that the user can’t update an invoice that has been printed.
Sometimes it’s useful to give the user an option to open a pop-up form that displays additional details about some information displayed on another form. As you move from one row to another in the main form, it would be nice if the form that displayed the additional information stayed in sync.
Of course, the Current event of a form lets you know when you move to a new row. In the Wedding List sample database built with macros (WeddingListMC.accdb), the macros do some elaborate filtering to keep a pop-up form with additional city information in sync with the main form. However, doing it with macros is the hard way!
The primary Wedding List sample application is in WeddingList.accdb, and it uses Visual Basic to provide all the automation. With Visual Basic, we were able to declare and use a custom event in the WeddingList form to signal the CityInformation form if it’s open and responding to the events. In the Current event of the WeddingList form, we don’t have to worry about whether the companion form is open. The code simply signals the event and lets the City Information form worry about keeping in sync with the main form. (The user can open the City Information form at any time by clicking the City Info button on the Wedding List form.) You can see these two forms in action in Figure 25-23.
Figure 25-23. The CityInformation form pops open over the main WeddingList form to display additional information about the invitee’s home city.
Here’s the code from the WeddingList form class module that makes an event available to signal the CityInformation form:
Option Compare Database
Option Explicit
' Event to signal we've moved to a new city
Public Event NewCity(varCityName As Variant)
' End of Declarations Section
Private Sub Form_Current()
On Error GoTo Form_Current_Err
' Signal the city form to move to this city
' and pass the city name to the event
RaiseEvent NewCity(Me!City)
Form_Current_Exit:
Exit Sub
Form_Current_Err:
MsgBox Error$
Resume Form_Current_Exit
End Sub
Private Sub cmdCity_Click()
On Error GoTo cmdCity_Click_Err
' If the city form is not open, open it
If Not IsFormLoaded("CityInformation") Then
DoCmd.OpenForm "CityInformation", acNormal, , , acFormReadOnly, acHidden
' Give the other form a chance to "hook" our event
DoEvents
End If
' Signal the form we just opened
RaiseEvent NewCity(Me!City)
cmdCity_Click_Exit:
Exit Sub
cmdCity_Click_Err:
MsgBox Error$
Resume cmdCity_Click_Exit
End SubIn the Declarations section of the module, we declared an event variable and indicated that we’re going to pass a parameter (the city name) in the event. In the Form_Current event procedure, the code uses RaiseEvent to pass the current city name to any other module that’s listening. The code doesn’t have to worry about whether any other module is interested in this event—it just signals the event when appropriate and then ends. (This is not unlike how Access works. When a form moves to a new record, Access signals the Form_Current event, but nothing happens unless you have written code to respond to the event.) Note that the variable passed is declared as a Variant to handle the case when the user moves to the new row at the end—the City control will be Null in that circumstance. A command button (cmdCity) on the WeddingList form allows the user to open the CityInformation form. The Click event of that button opens the form hidden and uses the DoEvents function to give the CityInformation form a chance to open and indicate that it wants to listen to the NewCity event on the WeddingList form. After waiting for the CityInformation form to finish processing, the code raises the event to notify that form about the city in the current row.
The CityInformation form does all the work (when it’s open) to respond to the event signaled by the WeddingList form and move to the correct row. The code is shown here:
Option Compare Database
Option Explicit
Dim WithEvents frmWedding As Form_WeddingList
' End of the Declarations Section
Private Sub Form_Load()
On Error GoTo Form_Load_Err
' If the wedding list form is open
If IsLoaded("WeddingList") Then
' Then set to respond to the NewCity event
Set frmWedding = Forms!WeddingList
End If
Form_Load_Exit:
Exit Sub
Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit
End Sub
Private Sub frmWedding_NewCity(varCityName As Variant)
' The Wedding List form has asked us to move to a
' new city via the NewCity event
On Error Resume Next
If IsNothing(varCityName) Then
' Hide me if city name is empty
Me.Visible = False
Else
' Reveal me if there's a city name, and go
' find it
Me.Visible = True
Me.Recordset.FindFirst "[CityName] = """ & _
varCityName & """"
End If
End SubIn the Declarations section, you can find an object variable called frmWedding that has a data type equal to the class module name of the WeddingList form. The WithEvents keyword indicates that code in this class module will respond to events signaled by any object assigned to this variable. When the form opens, the Form_Load procedure checks to see that the WeddingList form is open (just in case you opened this form by itself from the Navigation pane). If the WeddingList form is open, it “hooks” the NewCity event in that form by assigning it to the frmWedding variable.
The frmWedding_NewCity procedure responds to the NewCity event of the frmWedding object. Once the Load event code establishes frmWedding as a pointer to the WeddingList form, this procedure runs whenever code in the class module for that form signals the NewCity event with RaiseEvent.
The code in the event procedure is pretty simple. If the CityName parameter passed by the event is “nothing” (Null or a zero-length string), the procedure hides the form because there’s nothing to display. If the event passes a valid city name, the procedure uses the FindFirst method of the Recordset object of this form to move to the correct city.