Linking to Related Data in Another Form or Report

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.

Linking Forms Using a Filter

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.

Linking to a Report Using a Filter

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.

Synchronizing Two Forms Using a Class Event

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.

Note 

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.