Using the DLookUp function

Imagine the following scenario: we have created a form to display information contained in an order details table.  Having selected tblOrderDetails as the form's Record Source, we realise this table (part of a Many to Many Relationship) does not include the name of the product item as one of its fields; it instead uses the item name Id as a foreign key from tblProducts.  Obviously, this is going to be a problem from the perspective of user friendliness.  That is to say, somebody using the form is not necessarily going to know which product ID relates to which product item name.  This is where the DLookUp function comes in handy.

The DLookUp function allows the Access Developer to look up the value of a field from a table other than the form's actual Record Source. It is often used as a function in a Calculated Text Box Control (see previous post for more information about Calculated Controls).  So applied to our scenario, we can use a Calculated Control containing the DLookUp function to obtain the item name from tblProducts (based on our knowledge of the product item's ID).

When we use the DLookUp function we need to provide it with three pieces of information (called parameters).  These are:

1. The Field Name
2. The Table or Query Name
3. The Criteria to find the particular record.

The syntax for the DLookUp function used in a Calculated Control is as follows:

=DLookUp("FieldName", "TableName", "Criteria")

In our scenario we would enter the parameters which we need to pass into the text box's Control Source as follows:

=DLookUp("itemName", "tblProducts", "ID = " & forms![frmOrderDetails]![ProductId])

So here we are looking up the value of the itemName field, in the tblProducts table, where the ID for the product record matches the ProductId displayed on our active Order Details form.

It might be worth elaborating on the criteria parameter that we have used.  All parameters used in the DLookUp function (including the criteria parameter), are of the String Data Type.  The criteria parameter is a string containing information similar to an SQL WHERE Clause - except the "WHERE" part of the statement is omitted.  For example "ProductId = 1" instead of "WHERE ProductId =1".  In our example the ProductId used in this expression is going to be different for each current record displayed on the form.  Therefore our criteria needs to refer to the value of the ProductId displayed for the current record on our active form.  This is why our criteria is written:

 "ID = " & forms![frmOrderDetails]![productId]

Notice that only the "ID = " is contained within the quotation marks used to identify a string. The & symbol that appears immediately afterwards indicates that the information following it is intended to be part of that same string - a concatenation.  The criteria ends with the reference to the value contained in the productID of the active Order Details form that we have been working with - the syntax for the reference being forms![frmOrderDetails]![productId].

Calculated Controls

As well as being easy to use, Calculated Controls can be a really useful tool for the Access Developer. They provide a flexible way to display data on form's, without being restricted to information directly derived from a  field in a table or query.  We are all familiar with the simple Text Box Control.  Ordinarily these are bound to a particular field defined in the Text Box's Control Source - ie the control's property that links the Text Box with the particular field that supplies its data.  Calculated Control's, however, are slightly different.  Rather than using a field from a table or query to supply the information displayed in the control, we instead enter an expression into the text box control source.

Figure 1 (above): This is the property sheet for a Calculated Control.
The CONTROL SOURCE is located on the top line of the DATA TAB.

Figure 2: This is how the Calculate Text Box Control appears in FORM DESIGN VIEW.
Notice how the expression is displayed in the text box itself.  All expressions
begin the the = sign, and may consist of operators, identifiers, constants and functions.

The expression I have entered in Figure 1  is used to perform a mathematical calculation.  It works by multiplying the values contained in two bound text box's on the same form in order to produce a Total Amount in the calculated control. In this example I have been able to work out the total value of an order item based on the Unit Cost and Quantity Ordered.  Here is the finished result:

Figure 3: The Calculated Text Box Control multiplies
the UnitCost by Quantity to produce a Total.

The procedure for setting up a Calculated Control such as this is really quite easy.  It is useful, however, to have some knowledge of creating and modifying forms in DESIGN VIEW.  This is how I created the Calculated Text Box Control:

1. I began with an existing form called frmOrderDetails.  The form was bound to a table called tblOrderDetails.  The form began with 4 text box's displaying the ProductId, OrderId, UnitCost, and Quantity fields.
2. The form was opened in DESIGN VIEW.  This was done by right-clicking the frmOrderDetails form, and selecting the Design View Icon from the drop down menu which opened.
3. An unbound Text box Control was added by clicking the TEXT BOX icon and positioning the control on the form design grid.  The TEXT BOX Icon is located on the CONTROLS group of the DESIGN ribbon.  I had to make sure the USE CONTROL WIZARDS icon was not highlighted before doing so.
4. I then highlighted the new unbound Text Box Control and clicked the PROPERTY SHEET icon on the TOOLS group of the DESIGN RIBBON.
5. I needed to select the DATA tab on the newly opened PROPERTY SHEET.
6. I then entered the expression =[unitcost]*[quantity] into the CONTROL SOURCE property.  Unitcost was a reference to the bound UnitCost  text box, and Quantity was a reference to the bound Quanty text box.  These were the expression's Identifiers, and the * symbol was it's multiplication operator. NB I could have typed this expression directly into the text box on the DESIGN GRID - it would have set the CONTROL SOURCE property without having to open the PROPERTY SHEET.
7. Then when I opened the form and entered values in the two bound fields of UnitCost and Quantity, the Total Amount appeared automatically in the calculated text box control.

Object Dependencies

One thing that I find fascinating about Access Database Development is working with multiple database objects. Take an Access Form Object, for example.  Most forms have a Table or Query Object as a Record Source.  This is what is meant by the term Object Dependency: in this example, the Form Object is dependent on the Table or Query which supplies it's data. The same is true when a Query depends on a table or tables, or a form depends on another Form used as a Subform.  An Access Database is full of these  interrelated object dependencies which, in larger systems, can soon become quite complex.  This is where the Object Dependencies Pane come in quite useful.

The Object Dependency Pane works by the developer selecting or highlighting an object from the Navigation Pane, then clicking the OBJECT DEPENDENCIES icon located in the SHOW/HIDE group of the DATABASE TOOLS ribbon.  When the pane opens you have the option of displaying all Objects which are dependent on the selected Object, or all Objects upon which the selected Object Depends.   

Figure 1: The Object Dependencies Pane.

The screen shot above shows the dependencies for a form object called frmCustomer (which is a simple form displaying the details of a customer, with a subform displaying all orders the customer has made).  The pane shows all the Tables, Queries, Forms and Reports that frmCustomer is dependent upon.  As you can see, the form uses two tables - tblCustomer, and tblOrders.  It also uses another form frmOrders.  Although you cannot tell from the Object Dependencies Pane that tblCustomer is the Record Source for the main form and tblOrders for the subform, you do get a good idea that this is the case from the names allocated to each.  

If you want to open one of the Objects listed in the Object Dependencies Pane (in Design View), just click the Object name. It is also possible expand the list of Objects.  So, for example, if you were interested in finding out what tblCustomer depends on, just click the small + sign to the left of the object name and a new tree level opens out. As such, this tool can be used to trace some fairly complex object hierarchies where multiple levels exit.  

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.