Drag and Drop in Access

Doug Steele shows you how to add drag-and-drop to your Access application using combinations of multi-value and single-value controls.

The ability to drag and drop is easy to implement in Visual Basic, but the Access form model is different, so it’s not nearly as easy to implement it in Access. However, it’s possible to do, although you need to control it all manually. In Figure 1, I show you drag and drop of multiple items in a List box to a Text box.

Continue reading

Posted in Access Controls | Leave a comment

Custom Sort Orders

Presenting data to your users in an order that makes sense to them is essential. Both Jet and SQL Server support sorting one or more fields in a table alphabetically, numerically, and chronologically. That’s great, but sometimes you need more options. Rebecca Riordan shows you how to handle user-defined sort orders.

There are a number of situations in which the built-in sorting capability of your database engine just won’t do the job. A common case is data that’s truly alpha and numeric. The sorting rules are different when you combine numbers and text in the same field, and you’ll get anomalous results, as shown in Figure 1. The digit 3 might come before the digits 10 numerically, but when it’s alphabetized, 3 comes after 10 because the letter “3” comes after the letter “1”–probably not what you or your user would want.

Continue reading

Posted in Access Controls | Leave a comment

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.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

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.

 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?

Posted in Access Controls | Leave a comment

Paired Listbox Controls, Part 2

This month, Rebecca Riordan continues her examination of paired Listbox controls by adding two additional functions: deferring data updates until users explicitly commit their changes, and restoring the contents of the paired Listbox controls to their initial state.

Last month, I showed how to implement paired Listbox controls like the ones shown in Figure 1. If you can afford the space, a pair of Listbox controls is an excellent mechanism for allowing a user to select multiple items from a list.

Continue reading

Posted in Access Controls | Leave a comment

Creating Paired Listbox Controls

Presenting a pair of Listbox controls to the user and allowing the user to move an item from one to the other is a popular and effective user interface technique. Microsoft Access doesn’t provide any intrinsic controls for performing this, but as Rebecca Riordan demonstrates this month, it’s easy to create linked Listbox controls and base them on a single table, two tables, or even data created at runtime.

Paired Listbox controls (such as those shown in Figure 1, from the sample database in the accompanying Download) are a useful technique for allowing users to select multiple items from a list. A single multi-select Listbox control performs the same function, of course, but selecting multiple items from a single Listbox is more difficult for the user to manipulate. With a single Listbox it’s entirely too easy, for example, for a single accidental click to eliminate all of the user’s previous selections. Of course, paired Listbox controls require far more space than a single multi-select Listbox, and this limits their use, but when you can afford the screen real estate, multiple Listboxes are well worth considering.

Continue reading

Posted in Access Controls | Leave a comment