Using Forms to Display Linked Data
3 4
When you have tables with one-to-many or many-to-many relationships, you need a way to display multiple records of linked data in the "many" table for every single record of data in the "one" table. Forms with embedded subforms are ideal for displaying linked data, and embedded datasheet subforms are especially useful for displaying large quantities of linked data.
See Chapter 3, "Introduction to Database Design," for more details on relationships.
Sometimes you might not want the linked data to appear on the main form, but you still want the data to be readily available to the user. You can place on the main form a command button that, when clicked, "pops up" a second form containing the linked data. The following sections discuss both embedded and pop-up forms as well as new record and hyperlinked forms.
Embedded Subforms
One method for displaying linked data on a form is to create a query that includes several (or many) linked tables and then use that query as the form’s record source. This is the correct approach if you are creating a PivotTable, PivotChart, grouped data access page, or grouped report because these types of Access objects have their own methods of separating data into groups, and you don’t need to—and can’t—modify their data. Figure 5-55 shows this kind of query in Design view.
Figure 5-55. This query includes a number of linked tables.
The qryNorthwindAll query shown in Figure 5-55 is an example of a query that uses linked tables. This query is used as a data source for PivotTables and PivotCharts in Chapter 18, "Working with Data Access Pages."
However, in most cases a different approach is preferable for forms intended to display linked data. If you use a query based on linked tables, some of the fields from that query might not be updatable, which can cause problems. Instead of using a single all-in-one query as the form’s record source, it is best to bind the main form to the "one" table in a one-to-many relationship and bind one or more subforms to the "many" table (or tables). You don’t need to use queries or SQL statements at all (unless you need to filter or sort the data); you can bind the main form and subforms directly to the tables.
Although you can use a subform in Single Form view to display linked data, in general a datasheet subform is more useful. A datasheet subform can display a number of fields in a single row, possibly using a horizontal scroll bar if each linked record has many columns of data.
tip
Set the LinkMasterFields and LinkChildFields properties of the subform control to the linking fields of the "one" and "many" tables to guarantee that, as you move from record to record on the main form, the appropriate linked data is displayed on the subform.
As an alternative to using a subform to display linked data, you can use a table directly in a subform. (Since Access 2000, tables can be selected as source objects for the subform control.) It is generally preferable, however, to use a datasheet subform, which allows you to decide what columns to display in the datasheet, what order to put them in, and what their column headings will be. None of this is possible if you use a table as the subform control’s source object.
The fpri tag indicates that a form is the primary form in the database; the Menu Manager add-in (Menu Manager.mda, available on the companion CD) uses this tag.
The main form in the Crafts database (see Figure 5-56), fpriBooksAndVideos, has several subforms on it, some linked and some unlinked. (The unlinked subforms are discussed in the section "Using Forms to Display Unlinked Data.")
Figure 5-56. The main fpriBooksAndVideos form in the Crafts database has several subforms.
The fpriBooksAndVideos form has three linked subforms: subBookAuthors, subBookSpecs, and subBookSources. Each of these subforms has as its own record source one of the linking tables used to create a many-to-many link. The subBookAuthors subform, for example, is bound to tblBookAuthors, a table that contains only the two linking BookID and AuthorID fields, and thus links tblAuthors and tblBooksAndVideos in a many-to-many relationship. Similarly, the other two subforms are linked to the tblBookSpecs and tblBookSource tables, which are linking tables that set up the tblSpecialties–tblBooksAndVideos and tblSources–tblBooksAndVideos many-to-many relationships.
Each of these subforms has its default view set to Continuous Forms and has only a single control: a combo box bound to the ID field for the linked table (AuthorID, Specialty, or Source). In the case of fsubBookAuthors, the combo box is set to display the author’s name, although it is bound to AuthorID. The end result of this setup is a multirow subform with a drop-down list that appears on the selected row for selecting an author, specialty, or source for the book. This allows you to select multiple authors, specialties, and sources for each book. Figure 5-57 shows a second author being selected for a book in the Book Authors subform.
Figure 5-57. The Book Authors subform permits the user to select multiple authors for a book.
tip
If you open a form that has a subform and see only a white rectangle where the subform should appear (as in Figure 5-58, where the Book Authors subform is blank), the subform is open in a separate window, possibly hidden behind the main form. Close both the main form and the subform. When you reopen the main form, the subform should appear normally.
Figure 5-58. The Book Authors subform will appear blank when the subform has been opened separately.
Linked Pop-Up Forms
A pop-up form is a form that pops up when you click a command button. Don’t confuse pop-up forms with the PopUp property (although pop-up forms often have their PopUp property set to True). In the Crafts database, the fpriBooksAndVideos form has two command buttons you can use to pop up linked forms. The Works By Same Author command button pops up the frmBooksBySelectedAuthor form, which displays books by the author selected in the top row of the linked Book Authors subform. You create this linkage by using a select query as the pop-up form’s record source. Figure 5-59 shows this query in Design view.
Figure 5-59. This filter query is used as the pop-up form’s record source.
In this example, the pop-up form provides more information to allow you to decide whether the author of the current book is the same as the author of some previously entered books. For example, if you encounter a book by Barbara Hoffman on the subject of creating duck decoys, you can click Works By Same Author to pop up the Books By Selected Author form, as shown in Figure 5-60.
Figure 5-60. The frmBooksBySelectedAuthor pop-up form offers more information about the other books in the database by the selected author.
Since the other books by an author named Barbara Hoffman are all about sewing, it’s likely that the current book (about carving duck decoys) is by another Barbara Hoffman, and therefore you should create a new author record for her. The AuthorIDAutoNumber field guarantees unique records in tblAuthors, even for authors with the same name. This is necessary because, unlike books, which have ISBNs, authors don’t have unique identifiers, and two very different authors can have the same name.
The Publishers pop-up form has a different purpose: It opens the full Publishers form for the selected publisher so that you can verify that this is the correct publisher or add extra information you might get from a book, such as phone numbers, Web site addresses, or a changed address. Figure 5-61 shows the Publishers pop-up form.
Figure 5-61. The Publishers pop-up form allows you to verify or add information about a selected publisher.
This form is the same one opened from the Other Forms combo box’s drop-down list on the Crafts database’s main menu (the fmnuMain form). Because the same form is used to work with all the publishers or to display data for only the selected publisher, the filtering for the pop-up form is done in VBA code from the command button’s OnClick event. This procedure is listed in the following code:
Private Sub cmdPublishers_Click() On Error GoTo ErrorHandler Dim strDocName As String Dim strLinkCriteria As String strDocName = "frmPublishers" strLinkCriteria = "[PublisherCode]=" & "'" _ & Me![cboPublisherCode] & "'" DoCmd.OpenForm strDocName, , , strLinkCriteria Forms![frmPublishers]![txtCalledFrom] = "Books" ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & _ Err.Description Resume ErrorHandlerExit End Sub
New Record Forms
A top-to-bottom layout is often useful for a quick data entry form, a special form used to enter new data, often in a focused data entry situation. A quick data entry form is popped up as an alternative to simply moving to a new record on the main form. A separate data entry form is especially useful when a main form is laid out in a way that organizes the data logically, and perhaps has subforms to display linked or unlinked data. These features make a form more useful for reviewing and editing data, but a plain top-to-bottom control layout is more useful for entering new data efficiently.
See the "Creating Quick Data Entry Forms" section for more information on the rationale for these forms.
To create a quick data entry form, select a table on the Tables tab of the Database window and then click the New Object: AutoForm button on the Database Design toolbar. The new form has its fields arranged in a single column from top to bottom. A New Record form needs to have its Data Entry property set to Yes so that only the new record shows, and the form needs a Close Form button to close the form and return to the main form. Optionally, you can also add a Cancel button to close the quick data entry form without adding a new record. The New Publisher command button on the main form also needs a line to open the quick data entry form instead of the usual method of moving to a new record on the same form, either by clicking New Record on the navigation bar or by using the DoCmd.GoToRecord, , acNext command in a command button’s Click event procedure.
The controls on the frmPublishers form in the Crafts database are grouped in a way that makes the form more comprehensible for review and editing, but it’s definitely easier to enter data quickly in a form that has all its controls laid out top to bottom. Therefore, the New Publisher command button on this form opens fqdfNewPublisher, which is a quick data entry form bound to the tblPublishers table. The New Publisher command button’s OnClick event procedure is listed here:
Private Sub cmdNew_Click() DoCmd.OpenForm "fqdfNewPublisher" End Sub
After I created this quick data entry form, I named it fqdfNewPublisher (the fqdf tag indicates that it’s a quick data entry form) and turned on the form header and footer by choosing View, Form Header/Footer. A New Record form needs only a footer, so I shrank the header down to zero height so that it wouldn’t appear. I added two command buttons to the footer: one to close the quick data entry form without saving the new record and one to save the record and return to the main form. These event procedures are listed here:
Private Sub cmdCancel_Click() On Error GoTo ErrorHandler DoCmd.SetWarnings False If Me.Dirty = True Then DoCmd.RunCommand acCmdDeleteRecord End If DoCmd.Close ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & _ Err.Description Resume ErrorHandlerExit End Sub Private Sub cmdSave_Click() On Error GoTo ErrorHandler Dim frm As Access.Form Dim strSearch As String If IsLoaded("frmPublishers") = False Then DoCmd.OpenForm "frmPublishers" End If Set frm = Forms![frmPublishers] If Me.Dirty = True Then DoCmd.RunCommand acCmdSaveRecord End If 'Find the Publisher record that matches the control. strSearch = "[PublisherCode] = " & Chr$(39) & _ Me![PublisherCode] & Chr$(39) Debug.Print "Search string: " & strSearch frm.Requery frm.RecordsetClone.FindFirst strSearch If frm.RecordsetClone.NoMatch = True Then Debug.Print "No match found" End If frm.Bookmark = frm.RecordsetClone.Bookmark frm![cboPublisherSearchList].Requery 'Close quick data entry form form. DoCmd.Close ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & "; Description: " & _ Err.Description Resume ErrorHandlerExit End Sub
See Chapter 20, "Customizing Your Database Using VBA Code," for more information about working with event procedures.
Figure 5-62 shows the finished quick data entry form. As is typical of these forms, you can’t see all the controls at once, so the form has a vertical scroll bar.
Figure 5-62. When complete, the New Publisher quick data entry form looks like this.
Hyperlinked Forms
You can also open an Access form by using a hyperlink on a command button, but this method simply opens the form without offering you an opportunity to run code. For this reason, it’s less useful than using the OpenForm method to open a form in VBA code. If you want to open a form using the hyperlink method, click the Build button next to the Hyperlink property of a command button to open the Edit Hyperlink dialog box. Then click the Object In This Database icon on the left side of the dialog box, and select the form (or other database object) from the TreeView control in the center of the dialog box, as shown in Figure 5-63.
Figure 5-63. The Edit Hyperlink dialog box allows you to open a form by using a hyperlink.
note
Although opening forms using hyperlinks has little utility in Access, opening Word documents or Excel worksheets using hyperlinks on command buttons on Access forms can be genuinely useful.
Click OK to save the hyperlink. The properties sheet’s HyperlinkAddress property will be blank (for a database object), but its HyperlinkSubAddress property is filled in with the name of the selected database object, as shown in Figure 5-64.
Figure 5-64. You’ll find the HyperlinkSubAddress property on the properties sheet of a command button.
EAN: 2147483647
Pages: 172