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 (Figure 20–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 20–21 shows you the frmCompanies form and the Edit This buttons we provided on the subform.
Figure 20–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 Sub
And code in the form’s Current event prevents the user from clicking on the button when on 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 Sub
Setting the button’s Enabled property to False causes it to appear dimmed, and the user cannot click the button.
Now let’s take a 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. Click the Print button 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 20–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 20–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 Sub
This 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 fdlgInvoicePrintOptions 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 an 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 20–23.
Figure 20–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 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 not open, do 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 Sub
In 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 case. 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 Sub
In 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.
The Recordset property of a form in an Access database (.accdb file) returns a DAO recordset in Access 2007. For this reason, you should use a DAO FindFirst method, not an ADO Find method, to locate rows in a form recordset.