Using List Regions with Many-to-Many Relationships

Many-to-many relationships are a fact of life in real-world applications. They are, however, difficult to display in a typical Access form. The answer: list regions. Rebecca Riordan shows you how to implement this powerful tool in Access.

You probably know all about lookup fields—replacing a state or province code with the full state/province name, replacing a sales order’s status code with a descriptive label, and so on. These kinds of one-to-many relationships (one state or province has many customers) are second nature to most Access developers. But what about many-to-many relationships? These more sophisticated relationships are a very real part of most applications and need special handling in both your database and your user interface to let your users take advantage of them.

I encountered an example of when many-to-many relationships are critical in my own life when I joined a CD club. As part of joining the club, I had to select a music preference: rock, jazz, easy listening, and so on. The problem was that once I selected a preference, I could only buy from that music category. Unfortunately, my tastes are more wide-ranging than that. Whoever had designed the club’s data model and user interfaces either hadn’t recognized that they had a many-to-many relationship or hadn’t implemented it in the application’s user interface. In this article, I’ll show you how to create a fully functional interface that supports many-to-many relationships. Along the way, you’ll also see how to dynamically add new events to controls.

The list region solution

Most forms that represent records from tables that have a many-to-many relationship treat the many-to-many relationship as if it were a one-to-many relationship: The form displays only a single record from one side of the relationship and all—and only—the related records from the other side (usually with a subform or a list control). A typical example is shown in Figure 1, which shows a single book and the keywords selected for that book. The problem with this approach is that the user can’t immediately see all of the options available on the “many side.” If users want to assign new keywords to this item, how will they do it? Or an even more basic question: How will they know what keyword options are available?


 Figure 1

This is where a list region comes in—it displays all of the records on the many side of the relationship, not just the related records. Users can see which options are available and select the ones that they want. Figure 2 shows a typical list region—in this case, one that uses checkbox controls. The form displays all of the keywords in the Keywords table so that the user can see which ones are available, which are selected, and check additional options.


Figure 2

List regions aren’t seen very often. I suspect this is because they don’t match modern database design models. The structure appears to violate relational theory, which focuses on one-to-many relationships. Also, when faced with a design that supports assigning multiple values to multiple items, most people’s first thought is that the values will have to be hard-coded into their application. Hard-coded values are treated like GOTO statements: Yuck.

Wrong. In fact, it’s a shame that list regions aren’t used more often. Users love them because it relieves them of the burden of remembering which values have already been assigned.

As for the objection that the form is implementing a many-to-many relationship that doesn’t exist in the relational model—who cares? The user interface isn’t the database schema, and it’s your job to ensure that the user interface matches the client’s way of thinking, not the database designer’s way of doing things. In a relational database, it’s perfectly possible to implement the underlying data structure using the classic junction table schema shown in Figure 3.


Figure 3

Dynamically building list regions

As you’ll see, it isn’t necessary to hard-code the values in most situations (the exception is if you’re using the runtime version of Access), so you needn’t violate any of the modern “rules” for developing and maintaining the system.

However, it’s true that you can’t use the standard Microsoft Access data binding mechanisms with a list region. Because you’re displaying data from multiple records in a single set of controls, you can’t simply set the data source for the controls and let Access handle all the messy details. But setting the values yourself isn’t as difficult as you might expect.

Listing 1 shows my updateDisplay procedure, which I use to set the values in Figure 2, based on the existence of a record in the BookKeywords junction table. I called this procedure from the OnCurrent event of the form.

Listing 1. The updateDisplay procedure.

Private Sub updateDisplay()

Dim rsBookKeys As DAO.Recordset

Dim qdef As QueryDef

Dim rsKeys As DAO.Recordset

Dim ctrl As Control

Dim keyName As String

    Set rsKeys = CurrentDb.OpenRecordset("Keywords")

    Set qdef = CurrentDb.QueryDefs("GetBookKeywords")

    qdef.Parameters("BkID") = Me!BookID

    Set rsBookKeys = qdef.OpenRecordset()

    For Each ctrl In Me.Controls

        If ctrl.ControlType = acCheckBox Then

            ctrl = False

        End If

    Next

    Do While Not rsBookKeys.EOF

        keyName = Trim(rsBookKeys!Keyword)

        For Each ctrl In Me.Controls

            If ctrl.Name = keyName Then

                ctrl = True

            End If

        Next

        rsBookKeys.MoveNext

    Loop

End Sub

The first section of the procedure is straightforward. The first two lines retrieve all of the rows from the Keywords table (the rsKeys recordset variable). The next set of lines references a stored query, GetBookKeywords, which retrieves the records in the Keywords table that are related to the BookID passed as a parameter to this routine. Here’s the SQL for the GetBookKeywords query:

PARAMETERS BkID Short;

SELECT BookKeywords.BookID, Keywords.*

FROM Keywords INNER JOIN BookKeywords ON

     Keywords.KeywordID = BookKeywords.KeywordID

WHERE (((BookKeywords.BookID)=[BkID]));

The next block of code sets the value of all the checkboxes on the control to False. For the sake of simplicity, the loop assumes that all of the checkboxes on the form are part of the list region. If you have checkboxes on the form that are data-bound, this code will generate a runtime error when the code attempts to set the value of a data-bound checkbox. To avoid that, use a naming convention on your checkboxes to flag the controls that you want to set. In this example, I’ve named all the list region checkboxes clr<Keyword> and the code checks the name of the checkbox before trying to set it:

Dim ctrl As Control

For Each ctrl In Me.Controls

    If ctrl.ControlType = acCheckBox Then

        If Left(ctrl.Name) = "clr" Then

            ctrl = False

        End If

    End If

Next

The final block of code uses two loops to do the actual work of updating the display. The Do While loop iterates through the rsBookKeys recordset, which, as you’ll remember, contains the records from the Keywords table that match the BookID of the current record. The For Each loop iterates through the form’s Controls collection and sets the value of the checkbox to True if the name of the checkbox matches the trimmed Keyword field.

Adding code dynamically

The technique for updating the underlying data when the user changes the selected checkboxes is similar. The updateKeywords procedure is called from the click event of each of the controls. It adds a new record to the BookKeywords junction table if a checkbox is selected, or deletes the corresponding record if the checkbox has been deselected:

Private Sub updateKeywords()

Dim rsJunc As DAO.Recordset

Dim rsBookKeys As DAO.Recordset

Dim qdef As QueryDef

Dim keyID As Integer

Dim ctrlSet As Boolean

  Set rsJunc = CurrentDb.OpenRecordset("BookKeywords")

  Set qdef = CurrentDb.QueryDefs("GetBookKeywords")

  qdef.Parameters("BkID") = Me!BookID

  Set rsBookKeys = qdef.OpenRecordset()

  keyID = Screen.ActiveControl.Tag

  ctrlSet = Screen.ActiveControl

  rsBookKeys.FindFirst "KeywordID = " & keyID

  If ctrlSet = True Then

      If rsBookKeys.NoMatch Then

          rsJunc.AddNew

          rsJunc!BookID = Me!BookID

          rsJunc!KeywordID = keyID

          rsJunc.Update

      End If

  ElseIf ctrlSet = False Then

      If not rsBookKeys.NoMatch Then

          rsBookKeys.Delete

      End If

  End If

End Sub

The first two blocks of code should look familiar. The first block sets a recordset variable to the BookKeywords table. The second one opens the GetBookKeywords query using the same block of code that I used earlier.

The next block retrieves the values from the checkbox that’s been clicked into a couple of variables, KeyID and ctrlSet, using the Screen.ActiveControl property. The code assumes that the KeywordID value of the keyword that corresponds to this checkbox has been stored in the Tag property of the control. I’ll show you how it got there in a minute.

The final block of code searches for the KeyID in the junction table. If the checkbox has been selected, there needs to be a record in the junction table that contains the current BookID and KeywordID. If the checkbox has been deselected, there shouldn’t be a matching record in the junction table. Since the rsBookKeys recordset only contains records for the current BookID, I only need to search for the KeywordID. The code uses the NoMatch property to determine whether the record exists.

If the ctrlSet variable is True (the user has selected the checkbox), the code checks whether NoMatch is also equal to True. If it is, the required record isn’t present in the junction table, so it’s added. If, on the other hand, ctrlSet is False, the code checks for NoMatch being False (that is, the record exists) and, since the record exists, deletes it.

That’s all there is to updating the controls to reflect the many-to-many relationship between the two tables. But how did the checkboxes get there in the first place? It isn’t necessary to hard-code them, although I’ll be honest with you, it’s a lot easier if you do. Microsoft Access doesn’t make it easy to add controls to forms at runtime.

The first constraint is that you can’t add controls unless the form is in design view. This means you can’t create the list region controls at runtime using the runtime version of Access (the runtime engine prohibits opening a form in design view). Even if you’re working with the full version of Access, you’ll have to open the form indirectly. The best place to do this is in the AfterInsert event of the form that updates the many-side table but, for simplicity’s sake, the sample application does it from a switchboard. The OnClick event of the relative button is shown here:

Private Sub OpenChkListRegions_Click()

    DoCmd.OpenForm "CheckListRegions", acDesign

    createKeywords ("CheckListRegions")

    DoCmd.Close acForm, "CheckListRegions", acSaveYes

    DoCmd.OpenForm "CheckListRegions", acNormal

End Sub

The form is actually opened twice. First, the form is opened in design view and the checkboxes are added (this happens in the createKeywords procedure that I’ll show you in a moment). After the checkboxes are added, the form is closed and saved and then reopened in normal mode. I could have done this using the SendKeys statement, but SendKeys is notoriously unreliable, so this is a safer method.

The createKeywords procedure is shown in the next code block. All of the values that are used to size and lay out the controls are declared as constants at the beginning of the procedure. To simplify designing the form, the translation from inches to twips is handled here via the TPI (Twips Per Inch) constant. If you’re not designing your form using inches, you’ll need to change this value:

Public Sub CreateKeywords(frmName As String)

Const TPI = 1440

Const cols As Integer = 5

Const rows As Integer = 5

Const rowHeight As Single = 0.22 * TPI

Const colWidth As Single = 1.2917 * TPI

Const startingCol As Single = 0.25 * TPI

Const startingRow As Single = 1.5 * TPI

Const labelOffset As Single = 0.1597 * TPI

Const labelWidth As Single = 1.0521 * TPI

Const labelHeight As Single = 0.1667 * TPI

The cols and rows constants define the number of columns and rows of controls to display, respectively. The colHeight constant holds the column offset, while rowWidth holds the row offset. The startingRow and startingCol constants hold the position of the first control. The labelOffset, labelWidth, and labelHeight constants hold the size and position of the labels associated with each checkbox.

The next block of code in the procedure declares and initializes the variables that will be used for layout. Following that, I open a recordset against the SortedKeywordList query, which, as you’d expect, returns the list of keywords, sorted alphabetically. The only things even moderately tricky here are the three string variables—procStart, procName, and procFinish, which will contain the text of the Click event procedure that I’ll dynamically add to the form’s code-behind module:

Dim cb As Control

Dim lbl As Control

Dim idx As Integer

Dim currentCol As Integer

Dim currentRow As Integer

Dim currentX As Single

Dim currentY As Single

Dim mdl As Module

Dim procStart As String

Dim procFinish As String

Dim procName As String

Dim rs As Recordset

    Set rs = CurrentDb.OpenRecordset("SortedKeywordList")

    currentCol = 1

    currentRow = 1

    currentX = startingCol

    currentY = startingRow

    Set mdl = Forms(frmName).Module

    procStart = "Private Sub "

    procFinish = "_Click()" & vbCrLf & vbTab & _

        "updateKeywords" & vbCrLf & _

        "End Sub"

The next block deletes any existing checkboxes and their associated labels. The Click event that calls the createKeywords procedure saves the form when it returns, so I must delete any existing controls before re-adding them. Again, if you have other checkboxes on the form, you’ll need to use the checkbox name here so that you only delete the boxes that make up the list region:

For Each cb In Forms(frmName).Controls

     If cb.ControlType = acCheckBox Then

         DeleteControl frmName, cb.Name

     ElseIf cb.ControlType = acLabel Then

         If Left(cb.Name, 3) = "clr" Then

            DeleteControl frmName, cb.Name

         End If

     End If

Next

Having removed the old controls, the procedure now creates the new controls, one for each row in the Keywords table. Each checkbox is created using the CreateControl method, which is passed the form name and a VBA constant representing the type of control to create. The CreateControl method returns a reference to the newly created control, which I use to set the control’s properties:

Do While Not rs.EOF

    Set cb = CreateControl(frmName, acCheckBox)

    cb.Left = currentX

    cb.Top = currentY

    cb.Name = Trim(rs!Keyword)

    cb.Tag = rs!KeywordID

The next block creates the Click event procedure that will be associated with the checkbox using the Access Module object’s AddFromString method. Because the keywords can contain characters that aren’t allowed in procedure names, the code first uses the Replace function to replace spaces and dashes in the keyword string with the underscore character. The code then uses the module’s Find method to determine whether the event already exists. If it doesn’t, the AddFromString procedure adds it:

    cb.OnClick = "[Event Procedure]"

    procName = Replace(cb.Name, " ", "_")

    procName = Replace(procName, "-", "_")

    If Not mdl.Find(procStart & procName & "_Click", _

        0, 0, mdl.CountOfLines, 144) Then

       mdl.AddFromString procStart & procName & _

             procFinish

    End If

Access’s Module object also supports a CreateEventProc procedure that you can use to add a procedure to a module. However, this method expects to be passed the control itself, rather than just the control’s name, and doesn’t handle substituting characters. It wouldn’t be safe for me to use CreateEventProc here unless I was absolutely certain that the keywords didn’t contain illegal characters.

The next block creates the label associated with the checkbox using the same technique, but this time the characters “clr” are prepended to the label name so that we can find it and delete it the next time the controls are re-created:

   Set lbl = CreateControl(frmName, acLabel)

   lbl.Left = currentX + labelOffset

   lbl.Top = currentY

   lbl.Height = labelHeight

   lbl.Width = labelWidth

   lbl.Name = "clr" & lbl.Name

   lbl.Caption = Trim(rs!Keyword)

The final block in the loop increments the variables that control the position of the next control to be added:

If currentRow < rows Then

     currentRow = currentRow + 1

     currentY = currentY + rowHeight

   Else

     If currentCol < cols Then

       currentCol = currentCol + 1

       currentRow = 1

       currentX = currentX + colWidth

       currentY = startingRow

   Else

     'Replace these lines with appropriate

     'error handling

      Exit Do

   End If

End If

rs.MoveNext

    Loop

End Sub

A bit of code is missing in the example. Currently, if there are more rows in the Keywords table than will fit on the form, this procedure simply exits. In a production application, you’ll have to decide how to deal with this situation. You could, for example, resize the form to allow room for more controls.

Alternatives

My example used a set of checkboxes to display the records on the many side, but, of course, the technique isn’t limited to these controls. Multi-select ListBox controls are a good choice when you have more options than can reasonably be displayed with a set of discrete controls. Multi-select ListBoxes are also slightly easier to implement because they can be data-bound—there’s no need to manually create a set of controls at runtime. The basic technique for selecting and deselecting the items is the same. However, most users aren’t familiar with multi-select ListBoxes and find them difficult to use.

In addition to using a different type of control, the list region can be enhanced in a number of ways. If you look back to Figure 2, for example, you’ll see that there’s an implicit hierarchy in some of the controls (Literary Fiction is a subtype of Fiction) but that relationship isn’t reflected in the display. You could add this relationship, using either a self-referencing Parent key or two separate tables to implement the hierarchy. Offsetting the position of the controls in your form would reflect their relationship.

List regions don’t make sense for every many-to-many relationship. If you’re displaying Books and Authors, for example, it wouldn’t be a good idea to display every author known to the system and let the user select the one who wrote the book he’s entering. But when you’re categorizing information, as in my example, they work well. Give them a try the next time you’re representing this kind of relationship. Your users will love you for it.

 Your download file is called   Riordan_ListRegions.accdb

 

Other Pages That You Might Like to read

Drilling with Combo Boxes

You Can Do That with Datasheets?

This entry was posted in Access Controls. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.