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?
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.
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.
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.
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.