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.
