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.

Using Access Queries to Produce Summary Data

In this post we shall be looking at how to use Access Queries to produce summary data on groups of records which share something in common.  The exercise which we are going to use to illustrate this involves working on a table of Order Details.  The query we are going to create will group together all the individual records which share a common value in the OrderId field, and then produce a SUM of the Amount's field for each group.  What we are effectively doing here is sorting all the records into groups of Orders and then producing a Total Amount for each individual order.  This is the table data we are going to be working with:

Figure 1: The Order Details Table.

To do this exercise we are going to use GROUP BY and SUM on the TOTALS row of the QUERY DESIGN GRID.


Summary Query Data Exercise


Before creating the Query, you will first need to create the table or download the Summary_Data.accdb database.  This consists of one Order Details table with three fields - OrderId (Number), ProductId (Number), and Amount (Currency).  The primary key is a composite of OrderId and ProductId.  If this was a full Access Database Application the tblOrderDetails would be a junction table between an Orders table and a Products table. However, for our purposes of this exercise we only need to use the Order Details table.  Once this is in place you can begin the exercise.

1. Click the QUERY DESIGN icon.  This is located in the OTHER group of the CREATE RIBBON.
2. Add tblOrderDetails to the Query Design Window from the SHOW TABLE dialogue box.
3. Add the OrderId and Amount fields to the Query Design Grid.  The quickest way to do this is to double click the two field names from the Table Diagram in the top section of the Query Design Window.
4. We now need to show the TOTAL's row in the Query Design Grid.  This is not shown by default so click the TOTAL's icon in the SHOW/HIDE group of the DESIGN ribbon.  The TOTAL's row should now appear in the grid.
   
   
5. We want our query to group all the records into individual orders, and calculate the total amount for each one.  This is where the TOTALS row comes in.  We are going to enter the GROUP BY clause in the TOTAL's row of the OrderId column.  (NB This is actually the default setting, so you should not need to change it). All records sharing the same OrderId will now become a seperate and distinct group (ie row) in our query results. Then move over to the TOTAL's row of the Amount column.  We are going to enter the SUM function into this cell.  You can type this in directly or select it from the drop down list.  This will give us the Sum of Amount's for each individual group in the query results.
   

Figure 2: GROUP BY and SUM 
entered into the TOTAL  row.

We can now run our query by clicking the RUN query or DATASHEET icon. Figure 3 below shows what our query results should look like:

Figure 3: Query results showing a summary
of our original data.

As you can see, our original records have been grouped into individual rows by their OrderId, and a sum of amount has been produced for each group/order.

Importing and Exporting Data between Access and Excel

When you work with any sort of external data in relation to Microsoft Access, you have three options.  You can Import, Export or Link.  The difference between Import and Export is simple: you are either importing  data into Access, or exporting data out of Access.  The data you are working with, in this case, is a snapshot. In the case of Importing, once data has entered your Access application from an external data source, any changes made to the original data source, are not reflected in your imported data.  The same principle applies when you export data to an external application - any changes you subsequently make in Access is not reflected in your exported data.  This is in contrast to the option we covered in the last blog post on Linking Access to an External Data Source, where the connection made is 'live'.  Any changes made in Access is reflected in the external application , and any changes made in the external application is reflected in Access.

In this blog post we are specifically going to be looking at Exporting and Importing snapshot data to and from Microsoft Excel.  NB you can also partially link an Access Database to an Excel spreadsheet, but the connection in this case in not completely live - you cannot change data from within the Access Database (which is then reflected in Excel), although data changed within Excel is reflected within Access - in other words, the connection is read-only.

Exporting Access Data to Excel

Let's begin by going through the procedure of Exporting Access data to Excel.  This is a procedure you might go through to export an Access Query or Table to Excel in order to perform further analysis.  We shall be using the Access database from my last blog post which you can download from here.

1. Open the database containing the table to be exported.  
2. Click the name of the table in the NAVIGATION PANE so that it is highlighted in orange.  Alternatively you could actually open the table.
3. Click the EXCEL Icon located in the EXPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
   
   
4. When the EXPORT dialogue box opens, click the BROWSE button and select a location and filename for the newly exported excel spreadsheet.  Then click the EXPORT DATA WITH FORMATTING AND LAYOUT and the OPEN THE DESTINATION FILE AFTER THE EXPORT OPERATION IS COMPLETE check box's.
   
   

   

   Figure 1:  The EXPORT dialogue box for exporting to Microsoft Excel.

   
   
5. Then click the OK button below.  This completes the export process.  The finished result can be seen in the screen shot below:

Figure 2: Table data exported from Access into Excel.

Importing Data into Access from Excel


Now that we have exported our Access Table into Excel, lets have a go at Importing data back into a new Access Table from an Excel spreadsheet.  This is a procedure Access Developers often make when they are convert an Excel spreadsheet to Access.  For sake of convenience, we are going to Import data from the same Excel Spreadsheet which was created when we did our original Export Procedure.

1. Open the Access database into which we are going to Import the data.  You can use the same database as before, because we are going to import the data into a new table.
2. Click the IMPORT EXCEL SPREADSHEET Icon located in the IMPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
3. This opens the GET EXTERNAL DATA dialogue box for Microsoft Excel.  Click the BROWSE button and search for the spreadsheet file to Import.
4. You then have three options.  You can Import the data into a new table, append the data into an existing table, or create a link.  We are going to Import the data into a new table, so click the top check box which says IMPORT THE SOURCE DATA INTO A NEW TABLE IN THE CURRENT DATABASE.  Then click OK.
   
   

   

   Figure 3:  The GET EXTERNAL DATA dialogue box for Importing to Excel.

   
   
5. This opens the first page of the IMPORT SPREADSHEET WIZARD (see screenshot below).  Our spreadsheet contains column headings which can be used as field names, so click the Check Box which says FIRST ROW CONTAINS COLUMN HEADINGS.
   
   
   
   

   

   Figure 4: The first page of the IMPORT SPREADSHEET WIZARD.

   
   
6. Click the NEXT button for the second page of the Import Spreadsheet Wizard.  This is where we are able to specify information about each of the fields we are importing - information such as DATA TYPE, INDEX, and FIELD NAME.  Click the ID column heading so that it is highlighted (if it is not already).  Then change the INDEX to YES(NO DUPLICATES) and the DATA TYPE to LONG INTEGER.  This is so we can use the ID field as the table's primary key.
   
   

   

   Figure 4: Setting the  FIELD OPTIONS in the IMPORT SPREADSHEET WIZARD.

   
   
7. Click NEXT to move to the third page of the Import Spreadsheet Wizard.  This is where we explicitly define the Primary Key for the table.  We already have a suitable column to use as the Primary Key, so we do not need Access to do this for us (the default option).  As such, click the middle Option Box where it says CHOOSE MY OWN PRIMARY KEY.  Then select the ID field from the drop down list (if it is not already selected).
   
   

   

   Figure 5: Defining the Primary Key.

   
   
8. Click NEXT to bring up the final page of the Import Spreadsheet Wizard.  This asks you to enter the name of the table.  Change the default name to tblExcelImport (a new table name that does not already exist) and then click the FINISH button.
   
   

   

   Figure 6: Selecting the new Table Name for the Imported Data.

The table has now been imported and appears in the NAVIGATION PANE of your Access Database. You are now free to open the form and view the records, and may also edit the new table in DESIGN VIEW if you wish.

Linking Access to an External Data Source

There are many reasons an Access Developer needs or chooses to link an Access database to an external Data Source.  One common reason is to increase efficiency when a team of users need simultaneous access to a database across a Local Area Network. A common practice is to store an Access file containing the database tables in a shared folder.  This is referred to the Server or Back End.  Individual Users then have a local Access database stored on their own PC's which is linked to this Server.  Each one of these local database files contains all the Access Forms, Queries, and Reports, and are referred to as Client's or Front End's.  The logic behind this set up is that once data is downloaded from the Server, any processing that is required can then be done locally, thereby freeing the Server to deliver information to other Client's on the network.

In this exercise we are going to have a go at linking an Access Database to an External Data Source.   You don't need to be on a network to try this.  The general principle of Linking works exactly the same when the Client and Server database files are stored on the same machine, and even in the same folder.

Before you begin, you will need to create a new database containing a table. Call the database LinkTestServer.accdb. This will be the data source that we will be linking to.  A simple table of made up names will do fine for this.  Alternatively, you can download this example Link Test Server Database to use for the purpose.  Once you have done this we can begin by creating the Client database, and then link it to the Server file.

1. Open Access and Create a New Database.  You can save it in the same folder as the Server file.
2. Select the EXTERNAL DATA tab on the Access Ribbon.
3. In the Ribbon's IMPORT group, click the IMPORT ACCESS DATABASE icon.
   
   
   
   
4. This opens the GET EXTERNAL DATA dialogue box.  Here we need to browse and select the name of the Access Database that we are going to use as our Server or Data Source. You will also need to click the lower option box where it says LINK TO THE DATA SOURCE BY CREATING A LINKED TABLE.
   
   
   
   
   
5. Click OK.
6. The LINK TABLES dialogue box now opens. This lists all the tables in the Server Database.  In our example there is just one: tblCustomer.  Click the name so it is highlighted in blue.
7. Click OK to complete the linking process.

You should now see the linked table represented in the NAVIGATION PANE of your Client database.

As you can see from the screen shot above, it looks similar to an ordinary 'native' table, except there is a blue arrow to its left, indicating it is a linked table.  You can now open it from within the Client database and add additional names to it as if it was a native table.  You may also base Forms on it, Query it and Create Reports from it too.  It is just the same as working with a native table except you cannot modify the table design (eg add or change fields ... etc).  The table is still located externally, but any additions, deletions or edits you make to the table's data from the Client database is reflected in the data stored in the Server database. You may like to experiment with this and see for yourself.

Using an Unbound Form to Obtain Query Parameters

In this post we are going to use an unbound form to obtain the parameters for a query.  It follows on from my last post on How to Create a Parameter Query, but this time we are going to create the form which collects the query criteria.  This is particularly useful when the query has multiple parameters, and we want make the process of running our query more user friendly.

Create an Unbound Form

This object of this exercise is to query a simple customer table, returning a particular customer record in response to entering the customers name in an unbound form. Lets begin by creating the form which prompts the user to enter a firstname and lastname in two separate text boxes.  There is also going to be a Control Button which, when clicked, runs the query that references the two text boxes on our form.

Figure 1: The form to collect our Query Parameters.

1. Click the FORM DESIGN icon.  This is located in the FORMS group on the CREATE tab of the Access Ribbon.
2. Add the first text box to the form by clicking the TEXTBOX icon from the CONTROLS group and clicking on the desired position on the design grid.  
3. Select the textbox by clicking it and then click the PROPERTIES icon to bring up the PROPERTIES sheet.  Select the OTHER tab from the sheet.
4. We are going to refer to this text box as txtFirstName.  To do this we need to type FIRSTNAME into the NAME property of the PROPERTIES SHEET.
5. Next add a second text box to the form.  
6. Select the 2nd Textbox and enter txtSurname as the NAME property on the PROPERTIES SHEET.
7. Now we need to select the whole form by clicking the small square at the top left hand corner of the design grid. We are going to change some of the form properties to make the form look like a Dialogue box.
   
8. Select the FORMAT tab of the PROPERTIES SHEET.
9. Change the RECORD SELECTORS property to NO.
10. Change the NAVIGATION BUTTONS property to NO.
11. Change the BORDER STYLE property to DIALOG.
12. Select the OTHER tab of the PROPERTIES SHEET.
13. Change the POP UP property to YES.
14. Finish by saving your form as frmEnterParameter.

Before we add the command button, we will first create the Query to be run, and the table that it is based upon.

Create Parameter Query

This follows on from my last blog post on How to Create a Parameter Query .  Before you start creating the query you will first need to set up the table which is going to be queried.  This is a simple customer table with an ID, FirstName, and Surname fields. Add around ten random names that we can use as test data later on. Once you have done this you can create the actual parameter query following the procedure below.

1. Click the QUERY DESIGN icon to create a new query.
2. Select the customer table you just created from the SHOW TABLE dialogue box.
3. Select the fields to be used in the query.  The quickest way of doing this is to double click each field name  from the table box located above the design grid.  The fields to be used are  ID, FirstName, and Surname.
4. We now need to enter the query criteria.  To do this we are going to reference the parameter text boxes on the form we created earlier.  On the Query Design Grid, enter the following syntax in the criteria row of the FirstName and Surname columns respectively.

[forms]![frmEnterParameter]![txtFirstName]

[forms]![frmEnterParameter]![txtSurname]


The parameters entered by the user at runtime will then be used by Access as the Query Criteria.

This is how the grid should look when you have finished.

Figure 2: The Query Design.

You can now save the query as qryNameSearch.

Create Command Button

Now that we have created our query, we can go back to the form we created earlier and add a command button which runs the query when clicked.   To do this we are going to use the COMMAND BUTTON WIZARD.  Here is the procedure.

1. Open the form we created earlier in DESIGN VIEW.
2. Ensure the USE CONTROL WIZARDS icon is selected.  It is located in the CONTROLS group of the DESIGN ribbon, and should be highlighted in orange.
3. Click the BUTTON CONTROL icon (from the CONTROLS group), and position it on the design grid.  When you click on the desired position, the COMMAND BUTTON WIZARD begins.
   

   

   Figure 2: The Command Button Wizard.

4. Click MISCELLANEOUS for the category, and RUN QUERY for the Action. Then click NEXT.
5. Highlight the name of the Query we want to run.  The one we created earlier was called qryNameSearch.  Click NEXT.
6. Choose whether you would like text or a picture to be displayed on the command button.  Click NEXT.
7. Enter a meaningful name for the Command Button.  Lets call ours ctlRunQuery.

We are now in a position to try out our form and query.  Open the form in FORM VIEW and enter a customer name in the two text boxes.  You will need to use a name that you entered as test data when you set up the table.  Then click the Query Command Button.  The query should then run returning the customer record you just selected.

How to Create a Parameter Query

Parameter Queries are a great way to add interactivity to your Access Database.  Instead of entering a fixed Query Criteria in the Query Design Grid, we enter a question which prompts the user to enter the criteria in a dialogue box when the query is run. In this exercise we are going to create a Parameter Query to produce a list of surnames based on a value entered by the user.

1. Create a table with three columns - ID, Firstname, and Surname.
2. Enter a some test data into the table. Between five and ten random names should do for this purpose.  You might like to give some of the records the same surname.
3. Create a new query by clicking the QUERY DESIGN icon.  This is located in the OTHERS group of the Access CREATE ribbon. 
4. Once the Query Design Window opens, select the Table you just created from the SHOW TABLE dialogue box.
5. Then select the three fields contained in this table. The quickest way of doing this is to double click each field name listed in the table box in the upper section of the screen.  These will then appear as field headings in the QUERY DESIGN GRID.
6. The last stage is to enter the user prompt in the criteria row of the Surname column.  As you may remember, this has to be enclosed within square brackets.  Our prompt is going to be [Enter Surname].

Figure 1: A Parameter Query created in the Query Design Grid.

Then when you run the query you get this message:

Figure 2: Enter Parameter Value Dialogue Box.

All you need to do now is enter one of the surnames you added to your test data in stage 2, and click OK.  Then any record with that surname is produced in your query results.

Another great thing about Parameter Queries that you might like to try, is using them as the record source for a form.  When the form is loaded the query runs asking you to enter a parameter value as before.  This time, however, once you click OK, the form opens displaying a set of records based on the value entered.

Using a Subform Link to Open a form at a Specific Record

Imagine a scenario where you are looking at a record displayed on a form. The form contains a subform displaying a number of related records summarized in datasheet view.  This tip shows how we can open a new form at a specific record when we click a particular link on the subform.  As we shall see, it is a convenient way to navigate between forms when there is an underlying Many to Many Relationship structure in place.

To do this we are going to use the example of a Customer Order form.  The main section of the form displays the Customer Order, and the Subform displays the Order Details stored in the junction table.  When a user clicks the product link in the Order Details subform, the Product Form opens at the record for that particular product.

Figure 1: The Orders Form.  Clicking the product link in the Order Details Subform
opens the Product Form (see Figure 2 below) at that particular record.

Figure 2: The Products Form displaying the record specified in the Subform in Figure 1 above.

When the user clicks the link, the textbox's On_Click Event fires, triggering a short VBA subroutine.  This is the section of code responsible for opening the Product Form at the relevant record:

Dim varWhereClause As String
varWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause

It begins by defining a string variable to hold an SQL Where Clause.  The next line sets the string variable.  Notice how the end of the string references the productId field of the subform field that has been clicked.  The final line uses the DoCmd OpenForm Statement to open the Products Form.  The varWhereClause string variable is used as the statement's WhereCondition, thereby opening the form at that particular product record.


Here is the full procedure for putting all this in place:

1. Create the main Customer Order Form (with the Order Details Subform).  
2. Create the Products Form.
3. Next you need to go back and edit the Order Details Subform.
   
   
4. Click on the ProductId field, and then open the PROPERTIES SHEET. 
5. Under the FORMAT tab, change the IS_HYPERLINK property to YES.  Then change the DISPLAY_AS_HYPERLINK property to SCREEN_ONLY.  This changes the appearance of the ProductId field to a hyperlink style.
6. Under the EVENT tab of the PROPERTIES SHEET, select the ON_CLICK cell in the grid.  Then click the three dot symbol on it's right to open the CHOOSE BUILDER dialogue box.  
7. Select CODE BUILDER from the list, and click OK to open the VBA Editor.
8. Past the code listed below in between the lines, "Private Sub ... " and "Exit Sub", in the VBA Editor.

On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "ID = " &  Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next

A Gentle Introduction to Access SQL

SQL is a language used by database applications such as Microsoft Office Access, SQL Server and MySQL.  Although people using Access at a basic level do not need to know much, if anything, about the SQL language, the deeper we go into database design, the more important it becomes.  This post is intended to be a 'gentle' introduction to the subject!

SQL stands for Structured Query Language.  As the name implies, it is used in the creation of queries.  Whenever we create a Query using the Access Query Design Grid, Access converts the information we provide into SQL Code. We can view and edit this code by selecting SQL VIEW from the RESULTS group of the QUERY DESIGN ribbon. But why go to the trouble of learning SQL when we can just use the Design Grid?  There are many reasons for this.  For advanced users there are things which can be done in SQL that are too complex for the Design Grid to handle. In addition to this, SQL is also used within the Access Visual Basic programming langage thereby allowing us to automate queries and use variables in query criteria. (See my post on Automating an Update Query)

However, SQL is also used in other areas of Access such as the properties windows where we can define Record Sources for forms, Row Sources for Combo Box controls, and criteria for filters to name just a few.  As such, even at a relatively basic level, it is good to have a general awareness of SQL, and maybe some knowledge about how to create and edit simple SQL Statements.

Lets take a look at a simple SQL Statement used to query a database table.  In this example, the table is called tblCustomer.  We are going to select three fields from this table - FIRSTNAME, SURNAME, and CITY. The criteria we are going to use ='bolton' under the CITY field.  The query is designed to show a three column list of records where the value contained in the CITY Column is 'Bolton'.  Here is the SQL Code:

SELECT FirstName, Surname, City
FROM tblCustomer
WHERE City="bolton"



NB: When you look at code using Access SQL View you find that the syntax is slightly different.  Extra brackets are put around the expression following the Where clause; and the field names after the Select clause are written with a table name   [full stop] field name, like this - tblCustomer.FirstName.  This is how Access codes it own version of SQL.  It is, however, perfectly capable of reading the simpler version printed above.  Just be aware that Access will code statements slightly differently for its own purposes.  

As you can see there are three line to this SQL Statement.  The capitalised words at the beginning of each line are SQL keywords (or 'Clauses') and the small case words following relate to database fields, tables and criteria.  The Statement begins with the SELECT clause.  This is saying we are going to select the following fields (ie FirstName, SurName, and City) in this Statement.  In the next line we have the FROM clause.  This is saying that the fields selected above are taken from the following table (ie tblCustomer).  The final line is the WHERE clause.  This is saying that we are only interested in records where the following expression is true (ie city=Bolton). If we had created this Query using the Query Design Grid, it would have looked like this:

Figure 1: The equivalent query created using the Query Design Grid.

One thing that may strike you when you compare the SQL Statement with the Query Design Grid, is how simple and brief the Code actually is. A simple application of SQL is to enter a statement like that above for a form's RECORD SOURCE, as opposed to using a standard query created using the Grid.  This saves us having to create a seperate query, and minimises the number of Objects appearing in our database window. Just enter the whole SQL statement, as a single line, directly into the RECORD SOURCE cell of the PROPERTIES WINDOW.

Figure 2: An SQL Statement used as a forms RECORD SOURCE.

This has been a brief and simple introduction to Access SQL.  There is, of course, so much more to the subject. I intend to post more articles some time in the future which go into more detail.

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.