Reworking Forms to Enter and Display Many-to-Many Linked Data

Because of the changes to tables, some changes to the database’s forms are also required. Displaying (and entering) data into tables linked many-to-many requires some rather unintuitive techniques. The EBook Companion database has two forms used to enter and display data, fpriEBookNotes, a standard form with a combobox record selector for selecting books by author or title, and frmTreeViewEBookNotes, which uses a TreeView control for book selection. Both of these forms need reworking as a consequence of the normalization of the tables in the database, and a new form for working with author data is also needed.

Reworking the fpriEBookNotes Form

The original data entry form, fpriEBookNotes, has two sets of fields for the author and coauthor name, as shown in Figure 9.29.

Figure 9.29

Now that the tables have been normalized so that author information is stored in its own table, a different interface is needed to allow users to select authors for an ebook. I have found that the best way to work with many-to-many relationships on a form is a subform with the linking table (in this case, tblEBookAuthors) as its record source. The subform has a single control: a combobox that displays the information to be selected (in this case, a query based on tblAuthors). Figure 9.30 shows the fsubEBookAuthors subform in Design view. The subform’s LinkChildFields and LinkMasterFields properties are set to BookID, so it always displays the correct author information for the current book record.

The author information is most conveniently displayed in last name first format, so I made a query (qryEBookAuthors) containing only the AuthorID field and a calculated expression for a LastNameFirst field (I made a query, rather than just creating the expression in the subform’s record source so that it would be available for use elsewhere in the database).

Figure 9.30

The LastNameFirst calculated expression is:

 LastNameFirst: Trim(IIf([AuthorFirstName],[AuthorLastName]  & IIf([AuthorPrefix],[AuthorPrefix] & " ","") & IIf([AuthorFirstName], ", " & [AuthorFirstName],"") & IIf([AuthorMiddleName]," "  & [AuthorMiddleName]),[AuthorLastName])) & IIf([AuthorSuffix], " " & [AuthorSuffix],"") 

The expression uses the Iif and Trim functions to concatenate the various components of an author’s name, without creating extra spaces if some components are missing. qryEBookAuthors is the row source of the cboAuthor combobox on fsubEBookAuthors. The significant properties of this combobox are shown in Figure 9.31.

Figure 9.31

The combobox’s control source is the AuthorID field in tblEBookAuthors (the linking table in the many-to-many relationship). Because the subform is linked to the main form on the BookID field, BookID is automatically filled with the correct value when a new subform record is created. These two fields are the only fields in tblEBookAuthors, so that means that no code is needed to enter data into the linking table. Just select an author from the drop-down list (as shown in Figure 9.32), and both fields in the linking table are filled in with the BookID and AuthorID values.

Figure 9.32

The combobox’s row source (as mentioned above) is qryEBookAuthors, with the author’s name displayed last name first. The column count is 2, the column widths are 0" and 3", and the bound column is 1, which means that the first column (the AuthorID column) is invisible in the drop-down list, but its value is written to the AuthorID field in the subform’s record source, tblEBookAuthors. Sixteen rows of author names are displayed, and the Limit to List property is set to Yes so that only authors in tblAuthors can be selected.

The fsubEBookAuthors subform looks like a datasheet, but it is actually a Continuous Forms–type form, with its single control (cboAuthor) taking up the entire area of the subform. The reason for making this form Continuous Forms rather than Datasheet is that a datasheet subform would have a visible column heading, instead of just showing the author name selector combobox. The subform technique lets you add as many authors to a book as needed—just move to a new row on the subform, and select another author.

The fpriEBookNotes form header has two record selector comboboxes and two command buttons that let you switch the comboboxes (only one is visible at any time). Because of the normalization of the database’s tables, I had to modify the code on the AfterUpdate event procedures of the comboboxes, and also their row sources. cboTitleSearchList originally had qryEBooksByTitle (based on the non-normalized tblEBooks) as a row source, and cboAuthorSearchList also had a row source query that needs to be replaced.

The event procedures for both record selectors need changes; instead of using the old (possibly nonunique) search fields, I revised them to use the new unique AutoNumber field, BookID. I made two queries (qryEBookAuthorSearch and qryEBookTitleSearch) based on qryEBooksAndAuthors, containing only three fields: LastNameFirst, Title, and BookID. Whether you are searching by author or by title, the target is a specific book, so only BookID is needed for synchronizing. The only difference between these queries is the order of the LastNameFirst and Title fields. The qryEBookAuthorSearch query is shown in Design view in Figure 9.33.

Figure 9.33

Several of the comboboxes’ properties need to be modified to correctly reference the new row source query’s columns:

• Column Count is set to 3

• The Bound Column is set to 2 (the BookID field)

• ColumnWidths are set to 3";1.5";0" (or 1.5";3";0"), so the BookID field won’t be displayed in the drop-down list

The original cboAuthorSearchList event procedure is:

 Private Sub cboAuthorSearchList_AfterUpdate() On Error GoTo ErrorHandler    strSearch = "[Title] = " & Chr$(34) &        Me![cboAuthorSearchList].Column(1) & Chr$(34)    ‘Find the record that matches the control.    Me.Requery    Me.RecordsetClone.FindFirst strSearch    Me.Bookmark = Me.RecordsetClone.Bookmark ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The only difference in the new event procedures is the search string:

    strSearch = "[BookID] = " & Me![cboAuthorSearchList].Column(2) 

Column numbers in VBA code are zero-based, so Column(2) is actually the third column of the row source.

Figure 9.34 shows a book being selected from the cboTitleSearchList combobox.

Figure 9.34

Reworking the frmTreeViewEBookNotes Form

frmTreeViewEBookNotes has a TreeView control, which is filled with data from code (this is necessary because the old Access 97 TreeView Wizard was dropped in Access 2000, and has not yet reappeared in successive versions of Access). Additionally, it has a subform that displays data for the book selected in the TreeView control.

The function that fills the TreeView control required modification to work with the new, normalized tables. The original code is listed below (stripped of Debug.Print statements):

 Function tvwBooks_Fill() ‘Modified from a procedure generated by the Treeview Control Wizard ‘Called from Form_Load event On Error GoTo ErrorHandler    Dim strMessage As String    Dim dbs As DAO.Database    Dim rst As DAO.Recordset    Dim intVBMsg As Integer    Dim strQuery1 As String    Dim strQuery2 As String    Dim nod As Object    Dim strNode1Text As String    Dim strNode2Text As String    Dim strVisibleText As String        Set dbs = CurrentDb()    strQuery1 = "qryEBookAuthors"    strQuery2 = "qryEBooksByAuthor"        With Me![tvwBooks]       ‘Fill Level 1       Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)       Do Until rst.EOF          strNode1Text = StrConv("Level1" & rst![LastNameFirst],              vbLowerCase)          Set nod = .Nodes.Add(, , strNode1Text, rst![LastNameFirst])          nod.Expanded = True          rst.MoveNext       Loop       rst.Close              ‘Fill Level 2       Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)       Do Until rst.EOF          strNode1Text = StrConv("Level1" & rst![LastNameFirst], vbLowerCase)          strNode2Text = StrConv("Level2" & rst![Title], vbLowerCase)          strVisibleText = rst![Title] & rst![BeenRead]          .Nodes.Add strNode1Text, tvwChild, strNode2Text, strVisibleText          rst.MoveNext       Loop       rst.Close           End With    dbs.Close ErrorHandlerExit:    Exit Function ErrorHandler:    Select Case Err.Number       Case 35601          ‘Element not found          strMessage = "Possible Causes: You selected a table/query"              & " for a child level that does not correspond to a value"              & " from its parent level."          intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)       Case 35602          ‘Key is not unique in collection          strMessage = "Possible Causes: You selected a nonunique"              & " field to link levels."          intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)       Case Else          intVBMsg = MsgBox(Error$ & "@@", vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)    End Select    Resume ErrorHandlerExit End Function 

The original versions of the two queries used in this code (qryEBookAuthors and qryEBooksByAuthor) both pick up author names (concatenated into the LastNameFirst expression from the original, non-normalized tblEBooks. The LastNameFirst field is used as the Node 1 key expression, and the Title field is the key expression for Node 2. Since neither of these values is (necessarily) unique, this could cause problems when filling the TreeView control, when either an author name or a book title is not unique. See the TreeView Control section of Chapter 3, Selecting the Right Controls for Forms, for a more detailed explanation of TreeView controls.

After normalizing the EBookCompanion tables, and modifying the queries as needed, I rewrote the tvwBooks_Fill function to use AuthorID as the Node 1 key, with LastNameFirst as the visible text for that node. For the Node 2 key expression, I used AuthorID concatenated with Title, to ensure uniqueness of books by different authors with the same title, and Title plus a BeenRead symbol for the Node 2 visible text (BeenRead uses a division sign to indicate that the book is being read, and a small multiplication sign to indicate that it has been read). The modified function is:

 Function tvwBooks_Fill() ‘Modified from a procedure generated by the Treeview Control Wizard ‘Called from Form_Load event On Error GoTo ErrorHandler    Dim strMessage As String    Dim dbs As DAO.Database    Dim rst As DAO.Recordset    Dim intVBMsg As Integer    Dim strQuery1 As String    Dim strQuery2 As String    Dim nod As Object    Dim strNode1Text As String    Dim strNode2Text As String    Dim strVisibleText1 As String    Dim strVisibleText2 As String        Set dbs = CurrentDb()    strQuery1 = "qryEBookAuthors"    strQuery2 = "qryEBooksByAuthor"        With Me![tvwBooks]       ‘Fill Level 1       Set rst = dbs.OpenRecordset(strQuery1, dbOpenForwardOnly)       Do Until rst.EOF          strNode1Text = StrConv("Level1 - " & rst![AuthorID],              vbLowerCase)          strVisibleText1 = rst![LastNameFirst]          Set nod = .Nodes.Add(Key:=strNode1Text,              Text:=strVisibleText1)          nod.Expanded = True          rst.MoveNext       Loop       rst.Close              ‘Fill Level 2       Set rst = dbs.OpenRecordset(strQuery2, dbOpenForwardOnly)       Do Until rst.EOF          strNode1Text = StrConv("Level1 - " & rst![AuthorID], vbLowerCase)          strNode2Text = StrConv("Level2 - " & rst![AuthorID] & " - "              & rst![Title], vbLowerCase)          strVisibleText2 = rst![Title] & rst![BeenRead]          .Nodes.Add relative:=strNode1Text,              relationship:=tvwChild,              Key:=strNode2Text,              Text:=strVisibleText2          rst.MoveNext       Loop       rst.Close           End With    dbs.Close ErrorHandlerExit:    Exit Function ErrorHandler:    Select Case Err.Number       Case 35601          ‘Element not found          strMessage = "Possible Causes: You selected a table/query"              & " for a child level that does not correspond to a value"              & " from its parent level."          intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)       Case 35602          ‘Key is not unique in collection.          strMessage = "Possible Causes: You selected a nonunique"              & " field to link levels."          intVBMsg = MsgBox(Error$ & strMessage, vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)       Case Else          intVBMsg = MsgBox(Error$ & "@@", vbOKOnly +              vbExclamation, "Run-time Error: " & Err.Number)    End Select    Resume ErrorHandlerExit End Function 

There is another function related to the TreeView control: the Node_Click event procedure, which synchronizes the Books subform with the node (book title) clicked in the TreeView control. The original tvwBooks_NodeClick event procedure is:

 Private Sub tvwBooks_NodeClick(ByVal Node As Object) On Error GoTo ErrorHandler    Dim frm As Access.Form    Dim strNodeText As String        Set frm = Me![subBookInformation].Form        If Left(Node.Key, 6) = "level2" Then       If Right(Node.Text, 1) = Chr$(215) Or           Right(Node.Text, 1) = Chr$(247) Then          strNodeText = Left(Node.Text, Len(Node.Text) - 1)       Else          strNodeText = Node.Text       End If              Debug.Print "Node text: " & strNodeText       Me![txtSelectedBook].Value = strNodeText       frm.Requery       frm![txtSeriesNumber].Enabled = frm![chkSeries].Value       frm![cboSeriesName].Enabled = frm![chkSeries].Value       frm.Visible = True    Else       frm.Visible = False    End If        ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

I added a strTitlePlus variable to contain the Node 2 key expression (Author ID plus Title), and wrote this variable to the txtSelectedBook textbox on the main form (this textbox’s value is used to synchronize the subform with the selected book). The modified procedure is:

 Private Sub tvwBooks_NodeClick(ByVal Node As Object) On Error GoTo ErrorHandler    Dim frm As Access.Form    Dim strNodeText As String    Dim strTitlePlus As String        Set frm = Me![subBookInformation].Form        If Left(Node.Key, 6) = "level2" Then       If Right(Node.Text, 1) = Chr$(215) Or           Right(Node.Text, 1) = Chr$(247) Then          strNodeText = Left(Node.Text, Len(Node.Text) - 1)       Else          strNodeText = Node.Text       End If              strTitlePlus = Mid(Node.Key, 10)       Me![txtSelectedBook].Value = strTitlePlus       frm.Requery       frm![txtSeriesNumber].Enabled = frm![chkSeries].Value       frm![cboSeriesName].Enabled = frm![chkSeries].Value       frm.Visible = True    Else       frm.Visible = False    End If        ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit End Sub 

The subform on frmTreeViewEBookNotes (fsubBooks) is very similar to the main fpriEBookNotes form, without the header or footer, so the only modification needed was to replace the original txtAuthors textbox with a copy of the new subEBookAuthors subform from the main fpriEBookNotes form. (There is no problem with using the same subform on two main forms in this database because only one of them would be open at any given time.) I also added an invisible BookID control to the subform for debugging purposes. The modified frmTreeViewEBookNotes form is shown in Figure 9.35.

Figure 9.35

Creating the New frmAuthors Form

The original EBook Companion database didn’t need an Authors form because author names were entered directly into the non-normalized tblEBooks. Now that there is a separate tblAuthors, a form is needed for entering and editing author data. I made a simple form (frmAuthors) bound to tblAuthors for this purpose. This form is shown in Figure 9.36.

Figure 9.36

The final touch is to turn the cboAuthor combobox into an add-to combobox, by adding a NotInList event procedure that opens a New Author form. This event procedure allows entry of author data on the fly, in case the author of a newly entered book is not already in the tblAuthors table. The event procedure is:

 Private Sub cboAuthor_NotInList(strNewData As String, intResponse As Integer) On Error GoTo ErrorHandler        Dim strTitle As String    Dim intMsgDialog As Integer    Dim strMsg As String    Dim ctl As Control    Dim strEntry As String    Dim strFormName As String    Dim intReturn As Integer        strFormName = "fdlgNewAuthor"    strEntry = "author"    Set ctl = Me![cboAuthor]    ‘Display a message box asking if the user wants to add    ‘a new entry.    strTitle = strEntry & " not in list"    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1    strMsg = "Do you want to add a new " & strEntry & " entry?"    intReturn = MsgBox(strMsg, intMsgDialog, strTitle)    If intReturn = vbNo Then       intResponse = acDataErrContinue       ctl.Undo       GoTo ErrorHandlerExit    ElseIf intReturn = vbYes Then       ‘Open form for adding new record       ctl.Undo       intResponse = acDataErrContinue       DoCmd.OpenForm strFormName    End If     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " &        Err.Description    Resume ErrorHandlerExit End Sub 

For full details on creating an add-to combobox, see the “Add-to Combobox” section of Chapter 3, Selecting the Right Controls for Forms.