Creating a Form for a Many to Many Relationship

I was recently asked how to create a form to input order management data where there is an underlying Many to Many Relationship in place.  There are, of course, many ways of dealing with this common scenario, but the particular solution I suggested involved creating an Order Form, with an Order Details subform.  When the user enters the Order Details data on the subform, he or she is able to select a Product Item from a Combo Box list. The Row Source used for the Combo Box list is data drawn from the Products table.  Hence we have all sides of the Many to Many Relationship represented on a single form.

Figure 1 (Above): The Many to Many Form; the solution I suggested
in answer to dcodding's question.

 Figure 2: The underlying Many to Many
Relationship Structure, upon which the form is based.

It is interesting to note where each of the fields from the Order Form is located in the underlying Many to Many Relationship structure.  So let me briefly take you through this.

The main section of the order form has three fields derived from tblOrder, the left hand table in figure 2 above.  These are the OrderId and OrderDate fields.  In addition to this we also have the CustomerId field which is the foreign key from tblCustomer (a table which is not part of the Many to Many Relationship, therefore not shown in Figure 2).

The relationship between the main section of the Order Form and it's subform is modelled on the One to Many Relationship between tblOrder and tblOrderDetails. However, because we have an underlying Many to Many Relationship, I have created a query for the Subforms' Record Source which has fields from both tblOrderDetails and tblProducts.  Moreover, the second ProductId field is actually a combo box which uses data from tblProducts as its Row Source.

Lets take a closer look at the subform's design and underlying query:

Figure 3 (Above): The Order Details Subform design grid.

Figure 4: The Query used for the Subform's Record Source.

As you can see, ProductId, ProductId, and Quantity are derived from tblOrderDetails, and CostPerUnit is derived from tblProducts.  ProductId is, of course the foreign key from tblProducts. There is also a Calculated Field (Total) which multiplies the CostPerUnit by Quantity to provide the actual purchase price.

So why then do we have two ProductId's?  The reason for this is that the second ProductId does not actually display the Id number per se. Since this field is represented by a Combo Box control on the subform, the data which is displayed can be different to that which it is bound to.  So in order to make the subform more user friendly, the control is set up to display the product's ItemName from tblProducts whilst being bound to the tblOrderDetails.ProductId field. This is done by setting the combo box Control Source property to ProductId,  and its Row Source property to tblProducts. As such, using the Combo Box in this way has enabled the subform to obtain information from the products table on the right hand side of the Many to Many Relationship. For detailed information on how to do work with Combo Boxes in this way, you might like to see my post on Customizing an Access Combo Box.

So what happens when the user comes to enter data in the subform? If  the user knows the productId number for the Order Item in question, he or she will enter that number in the first column of the subform.  Since this is the foreign key for the Products table, doing so will 'bring' any other subform field derived from tblProducts along with it.  In other words once the user enters a productId, data in the CostPerUnit field for that product record is displayed in the appropriate field of the subform.  In addition to this, the ItemName for the product is displayed in the Combo Box control, since its Row Source is bound to ItemName in tblProducts.  If, on the other hand, the user does not know the ProductId, he/she may leave the first field blank, and select a value from the Combo Box drop down list.  Since the Combo Box Control Source is bound to ProductId of tblOrderDetails, and its Row Source, as we know, is bound to ItemName of tblProducts, this is effectively the same as entering the ProductId number directly.  What's more, data from tblProducts is 'brought' over to the subform as before.  Hence, the first productId field of the subform is filled in automatically.

We see, therefore, that this relatively simple and user friendly form not only represents all tables in this Many to Many Relationship, but also provides a practical example of how the Many to Many Relationship works in action.

Using Conditional Formatting on an Exam Results Report

Conditional Formatting may be applied to Text Box and Combo Box controls on Access Forms and Reports.   It allows us to format the data contained in a particular Text or Combo Box, according to value of that same data.  For example we can create Conditional Formatting for a text box to display a currency value, which when less than 0, will display that value in a red font, instead of the normal black.

Moreover, as well as formatting the Text or Combo Box according to the value contained in that same control, we can also format the Text or Combo box according to the value contained in another field of the same record.  So, for example, we can display a Text Box containing a CompanyName in bold font if the value of its AnnualTurnover field is greater than or equal to, say,  £100,000.  The great thing is, the data contained in the AnnualTurnover field does not necessarily have to be displayed on the form or report, as long as it is stored in the form or reports underlying Record Source.  In order to do this, we would enter an expression for the condition.

So let's take a look at how we go about applying Conditional Formatting using the example of a Student Exam Results Report.  The report applies conditional formatting based on the students exam result.  Students records with a result above 74 % are displayed in green, and those below 40 %, in red. The formatting for those in between is set to blue by default. To enhance clarity, I have also applied a sort on the Results field, thereby ordering the records from highest result to lowest. If you wish, you can download the complete solution by clicking the link below:

Student Exam Results

Our example database contains a table, tblExamResults, and report, rptExamResults- the table being the Record Source for the report.  Our table contains a list of student names and their corresponding exam results:

Figure 1: tblExamResults - The Record Source for our Report.

The rptExamResults report is based on this table.  I created it by clicking the REPORT icon (located in the REPORTS group of the CREATE ribbon) whilst tblExamResults was highlighted in the NAVIGATION PANE.  I then just needed to tidy it up in DESIGN VIEW, and add the Conditional Formatting (on each text box on the report) in addition to a SORT on the Results field.  This is the finished result:

Applying Conditional Formatting.

To begin with, lets look at how we apply Conditional Formatting to the Result field of rptExamResults.

1. Open the report in DESIGN VIEW
2. Right click the Result Text Box.
3. Click CONDITIONAL FORMATTING from the drop down menu.
4. When the CONDITIONAL FORMATTING dialog form opens, set the conditions as described in the next stage of the process below.

Figure 2: The Conditional Formatting dialog form.  It is possible to have a maximum of
three different conditions plus the default formatting.  

Setting Conditions based on FIELD VALUE IS

The next stage is to set the actual conditions for the Result field of the report (see Figure 2 above). NB: when FIELD VALUE IS is selected, it refers to the value contained in the text box being formatted:

1. First set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the  FIELD VALUE IS GREATER THAN 74.  If necessary click the drop down list to select FIELD VALUE IS, and then do the same in the next combo box along to select GREATER THAN.  Then just enter the value 74 in the third box along, and select the BOLD and RED icons.  You should see a preview in the box below when done.
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when FIELD VALUE IS LESS THAN 40.
5. Click OK to close.

Setting Conditions based on EXPRESSION IS

The next stage is to set the conditions for the StudentId field.  Since we are not basing the condition on the value contained in the StudentId field itself, we need to construct an expression which refers to the value contained in the Results field. As such, we shall be selecting EXPRESSION IS from the appropriate drop down lists, rather than FIELD VALUE IS.

1. Again, set the DEFAULT FORMATTING section so it displays a BLUE FONT.  Do this by selecting BLUE from the FONT/FORE COLOR icon
2. Next set CONDITION 1 so that a BOLD GREEN FONT displays when the EXPRESSION IS [Results]>=75.  Do this by clicking the drop down list to select EXPRESSION IS, and then enter the expression in the elongated box (see Figure 3 below).  Then select the BOLD and RED icons as before.  
3. Click the ADD >> button to show CONDITION 2.
4. Repeat the process of stage 2 so that a BOLD RED FONT displays when EXPRESSION IS [Results]<40
5. Click OK to close.

Figure 3: Condition based on EXPRESSION IS.  Note the elongated box where the expression is entered.  This appears when EXPRESSION IS is selected from the drop down list.

Finish the report by repeating this last stage for the FirstName and Surname fields using the EXPRESSION IS selection when creating the conditions.

Dealing With a Combo Box Entry that is "Not In List"

Following on from my last post on Customizing an Access Combo Box, this tip is about dealing with a Combo Box Entry that is Not In List.  Basically this occurs when instead of selecting an item from the Combo Box list, the user manually types in an entry that is not one of the items from the drop down list. It works by running a small section of VBA code when the Combo Box NOT IN LIST Event fires (more about this later).  The code then creates a new record containing the user's entry in the table upon which the combo box list is based.

Let's use the example of a Combo Box on a Products Form to illustrate this.  The Combo Box is used in this example to enter the Category field of the Product record.  The Record Source of the form is tblProducts, and the Row Source of the Combo Box is tblCategory.  If you want to run a copy of this example, you can download the Not In List Example Database by clicking the link (you will need to Enable the Content if you save it a location that is not trusted).

Figure 1: The Products form with a Combo Box on the Category field.

As you can see from Figure 1 above, the Category field uses a Combo Box with a list of potential categories.  These are stored in a separate table called tblCategory, which is the value of the Combo Box ROW SOURCE property.  The screen shot shows that there are three categories - Office Equipment, Office Furniture, and Stationery.  So what happens if the user wants to create a new product record, for say, a software package? There is no existing category for Software, so once the user enters the product name - lets say it is MS Office Access 2010 - he or she is unable to select a suitable category from the drop down list.

Now might be good time to mention the Combo Box LIMIT TO LIST property (located in the DATA TAB of the PROPERTY SHEET).  When this is set to YES (which is the case in our example), Access would normally display an error message saying THE TEXT YOU ENTERED IS NOT AN ITEM IN THE LIST; it then asks the user to select an item which is, or type in text that matches one of the listed items. This message is show in Figure 2 below:

Figure 2: The default message shown when an item is not in list.

However, when LIMIT TO LIST is set to yes, Access first fires the NOT IN LIST event. This enables us to pre-empt the standard message by writing code to display a custom message of our own.  This code also gives the user the opportunity to add the new Category to the table which is the row source for the combo box list.

Let's take a look at the code used in our example database.

---

Private Sub ctlCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo myError
    
    Dim rst As DAO.Recordset
        
    Set rst = CurrentDb.OpenRecordset("tblCategory", dbOpenDynaset)
    
        If vbYes = MsgBox("This Entry is not in list. Do you wish to add " _
                & NewData & " as a new category?", _
                vbYesNoCancel + vbDefaultButton2, _
                "New Category") Then
                
            rst.AddNew
                rst!categoryName = NewData
            rst.Update
            
            Response = acDataErrAdded
            
        Else
        
            Response = acDataErrContinue
            
        End If
       
leave:

    If Not rst Is Nothing Then
        rst.Close: Set rst = Nothing
    End If
    
    Exit Sub
    
myError:
   
    MsgBox "Error " & Err.Number & ": " & Error$
    Resume leave
    
End Sub

---

We enter this code into the Visual Basic Editor by clicking the three dot symbol on the far right of  ON NOT IN LIST (located on the EVENTS Tab of the Combo Box's PROPERTY SHEET).  The CODE BUILDER option was then selected from the CHOOSE BUILDER Dialogue Box.

Figure 3: The EVENTS Tab of the PROPERTY SHEET.

So how does the code work?

The first line of code was created automatically by Access when the VBA editor was opened via the ON NOT IN LIST line of the property sheet.  There are two arguments enclosed within the brackets - NewData and Response.  The first of these contains the new category value just entered by the user as a string variable.  The later relates to how access is going to handle the Not In List Event.  It's default value is 0 which represents the standard way of doing so ie displaying the error message and preventing the user from adding the new data.  Needless to say, we are going to alter this value later on in the code!

The third and fourth line of code relates to the object variable rst which represents a DAO recordset based on tblCategory, the Row Source of our Combo Box list.  Object variable rst is first declared in the Dim Statement, and then Set to tblCategory via the openrecordset method of the database object.

The fifth line of code relates to the custom message displayed to the user when he or she enters an item that is not in list.  The msgbox function displays our message as well as determining which buttons are offered, which button is the default, and the msgbox title.  The user response, ie the button the user clicks, is then processed by the If statement - so if the user clicks YES, the code branches to the code below where a new category record will be added.

This new category record is added using the addNew method of the Recordset Object.  The categoryName field is then set to the value of the NewData variable- ie the value of the new category entered by the user which was passed by Access as a parameter to the sub.  The new record is then saved to tblCategory using the Update method of the Recordset Object.

The next line of code changes the value held in the response variable (passed by Access as a parameter in the first line of the sub) via the acDataErrAdded constant.  Doing so tells access that new data can be added to the Combo Box list, and not to display the default Not In List error message.  It also Requeries the Combo Box list so that the new data appears immediately.

However, if the user had clicked the NO or CANCEL Command button mentioned above, the program flow branches to the Else Statement where it goes on to run the line of code changing the value held in the Response variable via the acDataErrContinue constant.  This tells Access not to add the new data to the Combo Box list, but continue without displaying the default Not In List message.

After the End If statement the program flow converges once again.  The last section of code (before the error handling section) deals with closing the rst Recordset Object and re-setting its value to nothing.  The sub's program flow then exits the sub via the Exit Sub statement.

Customizing an Access Combo Box

Combo Box's are a familiar control used in most modern software packages and the web.  They offer the user a  choice of values from a drop down list, thereby improving the user friendliness of the application in question.  Access has a great Combo Box Wizard which easily allows us to create a combo box for our forms.  There are, however, some restrictions on how far we are able to customize the Combo Box using the Wizard alone.  In this post we are going to look at how we can create a Combo Box manually (from scratch), thereby gaining complete control over how our Combo Box looks, acts and what information it can store and display.

The process of creating a combo box will be illustrated by going through the procedure in relation to an Order Details form.  We shall do this by creating an empty combo box control (which will be used to enter the product item ordered), and then customize it by modifying the relevant control properties involved.  As we shall see, this allows us to determine things like how many list columns the combo box is going to have, where the data comes from to fill the list, and which field (if any) will store the selected value from the list.

Figure 1: A Customized Combo Box Control.

We will go through this process in four stages.

Stage One: Creating an Empty Combo Box Control
The first stage will involve the creation of an empty Combo Box Control.  We could well do this using the Combo Box wizard, but we are going to do this manually for learning purposes.  You can download the Combo Box Exercise Database we are going to use by clicking the link.

1. Open the Combo Box Exercise database you downloaded.
2. Open the frmOrderDetails form in DESIGN VIEW.  The easiest way of doing this is to right click the form name and select DESIGN VIEW from the menu.  There is more information about Design View and Form customization here.  When the form opens in Design View you will see there is just one text box for the OrderDetailsId field.
3. Make sure the USE CONTROL WIZARDS icon (in the CONTROLS GROUP of the DESIGN RIBBON) is not highlighted.  If it is, just click it once.  This prevents the Combo Box Wizard from starting when you do step 4 below.
   
4. Click the COMBO BOX CONTROL icon (from the CONTROLS GROUP of the DESIGN RIBBON).  When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.  You should now see an unbound Combo Box Control on the Form Design Grid. 

Figure 2: An empty Combo Box Control.

Stage Two: Setting the Combo Box's CONTROL SOURCE PROPERTY

We are now going to set the CONTROL SOURCE property so the control becomes bound to the ItemId field (NB The forms RECORD SOURCE property is already set to tblOrderDetails). This means that any item selected from the Combo Box will be stored in the ItemId field.

1. Highlight the Combo Box Control and then click the PROPERTY SHEET icon in the TOOLS GROUP of the DESIGN RIBBON.
2. Select the DATA TAB  of the PROPERTY SHEETwhen it opens.
3. The CONTROL SOURCE property is located on the top row.   Click the drop down list and select the ItemId field.

Figure 3: The DATA TAB of the PROPERTY SHEET.

Stage Three: Setting the ROW SOURCE property

The ROW SOURCE property is located just below the CONTROL SOURCE.  As we have seen the latter property relates to the Combo Box's binding to a particular field.  By contrast the ROW SOURCE property relates to the data contained in the list itself - that is to say the source of the data contained in the list.  This ROW SOURCE can be from another table or query.  In this exercise, we are going to create a new query based on tblProducts  and tblCategory using the QUERY BUILDER.  This is opened from within the ROW SOURCE property cell of the PROPERTY SHEET.  The purpose of the query is to produce a list of all products in category 1 (Stationery). Here is the procedure:

1. Click in the ROW SOURCE cell of the PROPERTY SHEET.  It is located below the CONTROL SOURCE property of the DATA TAB.  There is a Three Dots symbol at the right edge of the cell.  Click this to open the Query Builder.
   
   
2. Select tblProducts and tblCategory from the SHOW TABLE dialogue box.  You can do this by double clicking each name in the dialogue box.  
3. Click CLOSE on the SHOW TABLE dialogue box. 
4. Select itemId and itemName from tblProducts.  The quickest way of doing this is to double click each of those field names in the tblProducts Table Diagram.
5. Then select categoryId and categoryName from tblProducts.
6. Enter =1 in the CRITERIA row of the categoryId column.
7. Then click the CLOSE icon in the CLOSE GROUP of the DESIGN RIBBON.  
8. You are then prompted to save the query as an SQL Statement in the ROW SOURCE property.  Click YES to the message DO YOU WANT TO SAVE THE CHANGES MADE TO THE SQL STATMENT AND UPDATE THE PROPERTY?

Figure 4: The Query Builder.

It is also worth explaining at this point that you are able to select which query column (ie field) is bound to the the Combo Box control. This is the mechanism whereby, after being selected, the value from the Combo Box list is stored in the relevant field of the Forms underlying RECORD SOURCE. In our exercise this is going to be the first column (ie the itemId field).  We do this by setting the BOUND COLUMN property to 1.  Since this is the default value for this property we do not need to change it. It is, however, useful to understand how this binding process works.

Stage Four: Formatting the Combo Box List
This is the stage where we work on the list which is displayed by the Combo Box.  We are going to set the properties which determine how many columns the combo box is going to have, whether each column has a heading,  how wide the columns are going to be, and the overall width of the list (which can be wider than the actual Combo Box Control itself).

1. Select the FORMAT TAB of the PROPERTY SHEET.
2. Set the COLUMN COUNT property to 4.  This tells Access that there is going to be four columns involved in the list.  However, we shall see next that not all of these columns need to be displayed.
3. Set the COLUMN WIDTH property as follows: 0cm;3cm;0cm;3cm.  Each number represents each column's width.  As you can see, columns 1 and 3 (ItemId and CategoryId) have been set to 0, thereby hiding them from the list.  This leaves the ItemName and CategoryName width set at 3cm's each. It is interesting to note that because ItemName is the first visible column, it is the value of this field which is ultimately displayed in the text section of the control (even though the Combo Box is actually bound to the ItemId field). This means the displayed data is more meaningful to the user whilst ensuring the control is bound to a unique field value. 
4. If you want the list to display column headings, set the COLUMN HEAD property to YES.  If you do, you may want to adjust the underlying query so that the column headings are displayed as Item Name and Category Name rather than ItemName and CategoryName.  You do this by altering the field row on the Query Builder as follows: Item Name: ItemName.  This substitutes the actual field name (appearing after the colon) for an alias (appearing before the colon).  You will of course have to go back and re-open the Query Builder from the ROW SOURCE property cell to do this.
5. Change the LIST WIDTH property to 6cm.  This is the total of the width of the Item Name (3cm and Category Name (3cm) that we set in step 3.  Our list will now be wider than the width of the actual Combo Box Control itself.
6. Now let's give our combo box a more meaningful name.  Select the OTHER TAB of the PROPERTIES SHEET and set the name property to ctlItem.
7. Finally let's set the CAPTION property of the Combo Box label.  Click the label in the FORM DESIGN GRID to select it.  Then click the FORMAT TAB of the PROPERTY SHEET, and change the CAPTION property to Item.

Our Customized Combo Box is now complete.  If you open your form it should look like the Combo Box in Figure 1 above.  

There is more to Combo Boxes than what we have covered in this exercise.  You can, for example, use an unbound Combo Box to Search for a Record, something I have covered in a previous post.  There are also different ways of dealing with values not already stored in the combo box list.  This is something I hope to cover in a future post.

Using a Combo Box to Search for a Record

In this tip we are going to look at how we can use a Combo Box control on a form to search for a particular record.  It works by clicking an item from the Combo Box's drop down list.  This activates a Visual Basic for Applications (VBA) Procedure using the forms ON CHANGE Event. Once the code executes, the form seamlessly displays the selected record.

Figure 1: Products Form with a Combo Box Search facility.  Selecting an item
searches for the relevant records and displays in on the form.

Lets begin by creating our Combo Box from scratch.

Create Combo Box from Scratch

1. Open your form in Design View.
2. Ensure the Wizard icon is deactivated.  If it is, just click the Wizard Icon so that it is no longer highlighted.
3. Click the Combo Box Icon.
4. When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.

You now have a empty Combo Box Control on your form.  If you wish, you can resize the box and add some text to the label.  The next task is for us to enter the ROW SOURCE property.  This is the source of the data which will appear in the Combo Box's drop down list (take care not to confuse this property with the combo box CONTROL SOURCE, as we are going to keep our control unbound).

The Row Source Property

1. Select the Combo Box Control by clicking it with the mouse.
2. Click the PROPERTY SHEET icon. This brings up the Combo Box's PROPERTY SHEET.
   
3. Select the DATA tab.
4. Enter the ROW SOURCE property.

When you  enter the Row Source property, you can select a Query/Table from the Drop down list, or write an SQL Statement directly onto the property grid. Another option is to click the three dots symbol at the end of the row to bring up the QUERY DESIGN Window. Then it is just a case of creating your query.

Figure 2: The Combo Box PROPERTY sheet for
the form in figure 1.

In our example shown in figure 1, I used tblProducts as the ROW SOURCE for the Combo Box. This, of course, is the same table that is used as the RECORD SOURCE for the main form.  I set the COLUMN COUNT PROPERTY (from the FORMAT tab) to 2, so that we get two columns in the drop down list - that is, the ID field, and the ItemName field.  I also set the BOUND COLUMN property (from the DATA TAB) to 1, so our Combo Box stores the value from the first column (ie the ID field) when the user makes a selection from the drop down list.

The next stage is to enter the VBA code.  We are going to use the Combo Box's ON CHANGE event.  This

event triggers as soon as the user selects an item from the drop down list.

Enter VBA Code

1. Select the Combo Box Control by clicking it with the mouse.
2. Open the PROPERTIES window.
3. Select the EVENT tab.
4. Select the ON CHANGE event by clicking its row in the grid.
5. Click the three dots symbol on the far right of the row. This opens the CHOOSE BUILDER dialogue box.  
6. Select CODE BUILDER and click the OK button.  This opens the VISUAL BASIC editor.
7. Copy and Paste the code (listed below) between the PRIVATE SUB and END SUB statements.   You may need to edit the FINDFIRST Statement on line 4 (replace ID with the field you are searching for).

On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!ctlSearch
Me.Bookmark = rst.Bookmark
leave:
Me!ctlSearch = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave

Figure 4: The VBA Editor

The main section of code works by cloning the forms's record set, which is stored in an object variable called rst. The FINDFIRST method is then used to search the cloned record set for the item selected in the Combo Box by the user.  Once found, the forms Bookmark property is then set to that of the cloned recordset.  This results in the Products Form seamlessly displaying the record selected from the drop down list.