Microsoft Access Tips

Using Calculated Fields in Queries

2012-02-24T02:44:00.000-08:00

Queries play an important part (in relational database design) in pulling information from different tables together. Consider the Many to Many relationship at the centre of an Order Management System. Here we have three tables - Orders, Order Details and Products. When we come to create the record source for the order items section of the invoice report, it is necessary to get data regarding an order items quantity from the Order Details table, and that of product cost per unit from the Products Table. Then it is the OrderId from the Orders Table which groups all Order Details records into a single order.

Figure 1 (above): A simplified example of a Many to Many Relationship from an Order Management Database.

Figure 2: A sample Invoice Report taken from one of my Order Management Database designs.
The Order Items Section is located in the middle of the report. CostPerUnit is
displayed here as Unit Cost and Amount as Total. This section contains
details of each individual order item which, taken together,
makes up the whole order.

As well as bringing information together in this way, query's play another important role in as much as they are commonly used to calculate and process information from these tables. This may be done by means of a Calculated Field. This is basically a new query column, the values of which being derived from some calculation or expression. As such, we are effectively creating new data for our database. So why is this important? Well, consider the Order Management Database Invoice mentioned earlier. As already stated, the Order Details table contains data pertaining to an order items quantity, and the Products table to an items Cost Per Unit. There is nowhere in a well designed ('normalised') database where we store the total of quantity*cost per unit. It is considered good practice to calculate this information (so we avoid storing unnecessary data which uses extra memory, and makes the database less efficient and user friendly). The Calculated Field used in a query is a common way of doing this.

Creating a calculated field is really quite easy. Rather selecting a table field on the top row of the query design grid, we manually type the name (or alias), followed by a colon, then followed by the calculation or expression. So if we wanted to create a calculated field to process quantity * cost per unit, we would use this syntax:

Amount: [Quantity]*[CostPerUnit]

I should point out that the alias used here - ie Amount - is entirely arbitrary text chosen by the database developer. It is, however, good practice to make it something meaningful in order to make it easier for the developer to understand at a later point in time.

Let look at how this fits in with the rest of the query:

Figure 3: Query with Calculated Field on the right.

As you can see the query contains a mixture of fields from all three tables involved in the many to many relationship. The Calculated Field has been added to the column on the right. You may have noticed that the calculated field looks slightly more complicated than the syntax I wrote earlier in the post. This is because we are also specifying the table name as well as the field name. As such:

[tblOrderDetails].[Quantity]

... refers to the Quantity field of tblOrderDetails, and:

[tblProducts].[CostPerUnit]

... refers to the CostPerUnit field of tblProducts.

It is essential to write the full reference in this way if any tables or sub-queries involved in the query contain duplicated field names. Although this is not the case in this example, it does no harm to get into the habit of writing the full syntax anyway.

And this is the output from the query showing the result of the calculated field on the right:

Figure 4: The Query Output.

Since this query has an ID field corresponding to tblOrder.OrderId, it can now be used as the record source for the Order Items section of an Invoice Report or Orders Subform. To do this the LINK MASTER FIELDS property of the subform is set to ID and the LINK CHILD FIELDS is set to OrderId. There is then the matter of creating the record source for the main section of the invoice report or orders form (this also involves creating Calculated Fields to get the Order Totals). Unfortunately this goes beyond the scope of this particular post.

More on DoCmd OpenForm: Control Form Opening Part Two

2012-02-17T05:01:00.000-08:00

Last week I blogged about how to Control Form Opening using the DoCmd OpenForm method of VBA. I covered the DataMode and OpenArgs parameters in some detail. Since being able to control form opening is an important part of database customization, I shall continue by looking at two other parameters of this method - namely the VIEW and WHERE parameters.

If we recap briefly on what we learnt last week, you may remember that we used DoCmd OpenForm to control whether the form was opened ready to add a new record, or edit an existing record. A label caption was also set according the value passed to the form in the statement's OpenArgs parameter. This time we are going to try something slightly different. We are going to create a command button, which when pressed by the user, opens that same form in DATASHEET VIEW as well as displaying records matching a given criteria. The end result will be a datasheet list of all employees where the Position field contains the value of "Manager".

Let's remind ourselves of the syntax for DoCmd OpenForm:

DoCmd.OpenForm "FormName", View, "FilterName", "WhereCondition", DataMode, WindowMode, "OpenArgs"

As you may remember, only the FormName parameter is required, all others being optional. Just remember to include a comma if any of the parameters are not used (unless they comes after the last used parameter, in which case they are not needed). So let's look at the specific syntax we need in order to achieve the task of producing a list of Managers in Datasheet View:

DoCmd.OpenForm "frmEmployees", acFormDS, , "position = 'manager'"

As you can see, the VIEW parameter has been set to acFormDS. This value simply indicates that we want the form to open in Datasheet View. When you come to enter this yourself, you will see that intellisense opens a drop down list of valid parameter values:

Figure 1: Intellisense displaying a list of valid parameter values for VIEW.

The next used parameter is the WhereCondition. This is basically an SQL WHERE clause, but without the WHERE keyword (see my Introduction to Access SQL for more information about SQL WHERE). You may also think of this as being like the criteria you enter in the Criteria Row of a Query Design Grid. When used as a OpenForm parameter, just remember to enclose it within quotation marks, as it is a string value - ie:

"position = 'manager'"

Now that we have grasped the particular syntax for DoCmd OpenForm, we are going to add a command button to the Switchboard form we created last week, and add the following code to its OnClick Event:

Private Sub ctlManagers_Click()
DoCmd.OpenForm "frmEmployees", acFormDS, , "position = 'manager'"
DoCmd.Close acForm, Me.Name
End Sub

NB the DoCmd.Close statement simply closes the switchboard so it does not get in the way once our employees form opens.

Our Switchboard should now look like this:

Figure 2: The new updated switchboard from last week.
The code above it is written behind the ctlManagers button's (top) OnClick Event.

Here is the result when the user clicks the ctlManagers button (captioned "List Managers"):

Figure 3: frmEmployees displayed in Datasheet View with WhereCondition applied.

As you can see, rather than our employees form being displayed in standard Form View, passing the acFormDS as the View parameter has instead opened frmEmployees as a Datasheet similar to a table or query; and out of a list of 22 employees, passing "position = 'manager'" as the WhereCondition parameter has provided a list of the two employees who have "Manager" as their Position.

If you have not already created this database for yourself, you might like to download the completed solution and experiment by changing, amongst others, the WhereCondition, to see the versatility of DoCmd OpenForm for yourself.

Control Form Opening: DataMode, OpenArgs, and the DoCmd.OpenForm Method

2012-02-10T04:07:00.000-08:00

Lets imagine we have an employees form to add and edit our employee records. The form is accessed via the database switchboard which contains a couple of Command Buttons. The user clicks the NEW EMPLOYEE button and the form opens in DATA ENTRY MODE; that is, as a blank form ready for a new record to be entered. If, however, he or she clicks the EDIT EMPLOYEE button, the form opens in FORM EDIT MODE; at an actual employee record ready to be edited as required. What's more, the form contains a label displaying a caption inviting the user to add a new employee, or edit an existing employee record depending on which button was pressed on the switchboard.

Figure1 (above): The Switchboard with Command Buttons
to open frmEmployees.


Figure 2: Form Design for frmEmployees. Note the label containing the text "Instructions": this text is dynamically reset as the form opens.

Since we have learnt some basic VBA programming skills at the end of last year, lets look at how we can do this using the DoCmd.OpenForm method. As we shall soon see, this method passes a number of parameters to the opening form, determining various aspects of how we want it to function. DataMode and OpenArgs are the two parameters we shall be using to accomplish the task set out in the above mentioned scenario.

DoCmd.OpenForm"formName",View,"FilterName","WhereCondition",DataMode, WindowMode,"OpenArgs"

Figure 3: The IntelliSense feature of the VBA Editor will
help when you come to enter the DoCmd.OpenForm parameters.

Here is an outline of the tasks involved:

Create the Switchboard Form with two Command buttons - ctlNew and ctlEdit.
Add code to each of the command buttons which opens frmEmployees in the desired DataMode and with the corresponding label caption for adding or editing.
Create frmEmployees along with its underlying data source (ie an employees table). The form should have a label control (lblInstuctions), and text boxes/combo boxes for each of the fields. NB for the purposes of this exercise it does not particularly matter what the fields are.
Add the relevant code to the OnOpen Event of frmEmployees. This is going to read the value of the forms OpenArgs Property, which is set when the user clicks one of the two command buttons on the switchboard. Then, depending on what that value is, display the appropriate caption for lblInstructions.

(Download the complete solution here)

Let's go through stage two and stage four in more detail.

To begin with lets look at the code used behind each of the two command buttons on frmSwitchboard. These statements are executed behind each command button's OnClick Event:

Stage Two Code:

Private Sub ctlEdit_Click()

DoCmd.OpenForm "frmEmployees", , , , acFormEdit, , "Edit"

End Sub

Private Sub ctlNew_Click()

DoCmd.OpenForm "frmEmployees", , , , acFormAdd, , "Add"

End Sub

As you can see, the DoCmd.OpenForm method has a total of seven parameters, but only the first of these is required (as opposed to optional). Not suprisingly the required parameter is the name of the form to be opened, which in our case is "frmEmployees". In addition to the form name we also set the DataMode and OpenArgs parameters. For our scenario, DataMode determines whether the form is going to be opened in DataEntry (acFormAdd) or Edit (acFormEdit) mode. It is the fifth parameter in the list. The OpenArgs parameter, on the other hand, is seventh in the list. This is simply a string value which will set the opening form's OpenArgs property. Once the form has opened, we are going to read the value of this property to determine which message we are going to display in the label caption shown in figure two above. As such we are going to pass a string value which indicates whether we are adding or editing the employee record. Please note that the text of the string value is entirely arbitrary. We just need to choose a unique value in order to differentiate between adding and editing as the form opens.

Here is the code we need to enter behind the frmEmployees OnOpen Event:

Stage Four Code:

Private Sub Form_Open(Cancel As Integer)

If Me.OpenArgs = "Edit" Then

lblInstructions.Caption = "Please Edit Employee Record"

ElseIf Me.OpenArgs = "Add" Then

lblInstructions.Caption = "Please Add New Employee Record"

End If

End Sub

As you can see this code reads the value stored in the opening form's OpenArgs property and uses a If ... Then ... Else Statement to determine whether our instructions label should display information pertaining to Editing or Adding an Employee Record.

Figure 4: frmEmployees for Editing.

Figure 5: frmEmployees for Adding (DataEntry).

As you can see, the screenshot in Figure 4 shows what our form looks like when it opens for Editing, and that in Figure 5 shows what it looks like for Adding. So despite being exactly the same form, we have dynamically controlled how it looks and functions by setting the DataMode and OpenArgs parameters of the DoCmd.OpenForm method.

Creating a Form for a Many to Many Relationship

2012-02-03T05:54:00.000-08:00

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 the LIKE Operator and Wildcards to Match Patterns Between Strings

2012-01-27T01:50:00.000-08:00

When we enter a criteria for a text field in an Access Query, the results can be a little limiting when we use the equality operator (ie = ). So, for example, if we have a list of employees and we want to search for all Sales Representatives, we could use ="Sales Representative" as the criteria. This would correctly produce a list of employees containing this exact value in the Position field. This is all fine and good. However, what if we wanted a list of all employees that have the word sales appearing at the start of this field? Take a look at this screen shot of the table in question:

Figure 1: The Employees Table

As you can see, there are three different job titles beginning with the word Sales - there is Sales Manager, Sales Representative, and Sales Assistant. Despite this fact, if we were to enter = "sales" as the criteria, no records would be returned in the query results. This is because it is not an exact match for any job title.

This is where the LIKE operator comes in handy. The LIKE operator is used in conjunction with one of the Access Wildcard symbols to compare a pattern between two strings. So instead of entering the criteria = "sales" we could enter LIKE "sales*".

Figure 2: Query Criteria using the LIKE operator.

This latter criteria would return all records beginning with the word "sales". So any word letter or character coming after "sales" is ignored. As such, our query now returns records containing any of the three above mentioned job titles in the Position field.

Figure 3:Query results returning all three job titles
beginning with the word "sales"...

The type and position of Wildcard is crucial in all this. We used the * wildcard symbol indicating that it is representing any number of characters (including zero). This is in contrast to the ? wildcard symbol which just represents a single character (eg LIKE "Sales Representativ?" to return "Sales Representative"), or the # wildcard symbol representing a single digit from 0 to 9 (eg LIKE "Person#" to return "Person1". We could also specify a range of characters between square brackets - for example LIKE "Person[1-5]" would return the string "Person1", or "Person2"etc, but not "Person6" or above.

These wildcard symbols can appear anywhere in the search string. We placed ours after at the end of the search string to return any string beginning with "Sales ...". Had we wanted to return any job title ending in "... Assistant", we would have placed the wildcard at the beginning of the search string ie LIKE "*Assistant" . There are even scenarios where a wildcard may be used in a specific place in the middle of a string too. Try experimenting with all this - it's the best way to learn!

Append Queries: Automatically Append Data from One Table to Another

2012-01-20T02:44:00.000-08:00

Append Queries are really useful when you acquire a new table full of data, and want to add it to an existing table in your database. This scenario may arise, for example, if your company purchases a mailing list, and you are asked to add the new names to those you already have stored. Rather than having to retype the new names by hand, an append query can be set up to copy the data from the new table into that which is already in use.

I have created an exercise to demonstrate how this process works. Lets begin by looking at the two tables we are going to be working with:

Figure 1: tblContacts - the existing table consisting
of ID (autonumber), FirstName (text) and Surname (text).

Figure 2: tblMoreNames - new data to be appended, consisting of
CustomerId (autonumber), Title (text), Initials (text), and LastName (text).
Notice the fields names are somwhat different and there is also a
risk of duplicating the ID fields from both tables.
More about this soon.

As you can see, figure 1 shows tblContacts, our existing table containing 15 records. We are going to create an append query to add the 10 new records from tblMoreNames to the data already stored in this table. You may have noticed that the field names contained in both tables are slightly different, and tblMoreNames has a Title field which tblContacts does not. You may also have spotted that there is a risk of us attempting to append duplicate data from the CustomerID field of tblMoreNames. Access would prevent this because the ID field in tblContacts is a Primary Key, so needs to be a unique value. The simple solution to this, is for us not to include the customerId field (or anyother incompatible field) in the append query. As such, we shall just append data from the Initials and LastName fields of tblMoreNames to the FirstName and Surname fields of tblContacts. Although the field names are slightly different, the data they contain is compatible, and for the purposes of this exercise, it is ok for us to append an Initial in place of a FirstName - the idea here is to demonstrate that the field names do not have to match exactly.

Creating an Append Query

Here is the procedure for creating the append query:

Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon). The QUERY DESIGN window then opens along with the SHOW TABLE dialog form.
The next step is to add tblMoreNames to the QUERY DESIGN window. Do this by clicking ADD in the SHOW TABLE dialog form. Notice it is the table containing the data to be appended that we have selected.
Click the APPEND icon from the QUERY TYPE group of the DESIGN ribbon. As you do this, you will see the APPEND dialog box open.
You are now asked to select the name of the original table to which the new data is to be appended. So select tblContacts from the drop down list.
You are also asked whether this table is stored in the current database or in an external database. In this exercise both tables are stored in the current database. This is the default button displayed in the option group, so there should not be any need to change it.
Click OK to close the dialog box.
Next we are going to select the fields from tblMoreNames to be appended. To do this drag and drop the Initials and LastName fields from the table (in the top half of the window) down onto the design grid.
Next we are going to tell Access which fields the data from Initials and Lastname will be appended to. To do this go down to the APPEND TO row of the design grid (see figure 3 below), and select FirstName in the Initials column, and Surname in the LastName Column.
Figure 3: The Query Design Grid.
We could add query criteria at this stage, but this particular exercise does not require any. If we did, however, this is added in the CRITERIA row just like it is with a select query.
If you want to view the data that is going to be appended, click the VIEW icon from the RESULTS group of the DESIGN ribbon. It is especially important to do this if any if any criteria is applied in step 9 above.
Once you are satisfied the correct data is going to be appended, click the RUN icon, again from the RESULTS group of the DESIGN ribbon.
A dialog box opens informing us that 10 rows are going to be appended, and asking us to confirm that we want to go ahead with this operation. Click YES to complete.

To see the result of our Append Query, re-open tblContacts.

Figure 4: tblContacts after the
Append Query has been run.

We can now see the new records appended to the end of our original data. Notice each of the newly appended records has been automatically allocated a unique ID number. This, of course, is because the ID field in the original table had been set to the AUTONUMBER datatype.

Using VBA to Filter Report Results

2012-01-13T01:37:00.000-08:00

Earlier this week I was asked how to filter the results of a report using a criteria selected from three Combo Boxes on a search form. You can see Marwa's question posted on my Access Tutorial Facebook Page (Tuesday 10th January 2012). The solution I suggested involved creating a Parameter Query with three separate criteria referencing the values contained in the Combo Boxes on the Search Form. You can see the full response I gave in my comment below her question.

By co-incidence I have also been working on a similar task in my work as an Access Developer. One of the projects I am currently working on is a Journal Database which uses descriptive tags to categorise journal entries. So, for example, if users made an entry about their progress learning Microsoft Access, they might use tags such as "Database Development", "Reports", and "Filters" to categorise their entry.

I wanted to create an quick way for users to search their journal by tag name, so that all records categorised by a given tag may be extracted and displayed in a report. To do this I created a really basic search form that consisted of just one unbound Combo Box.

The ROW SOURCE for the Combo Box is a table containing all of the tag names used in the database. The idea is that the user selects a tag from the drop down list, thereby triggering a block of VBA code in order to open the report filtered by the selected tag name. The screen shot below shows the report filtered by the tag name "ADO.Net":

The underlying table structure for this report is based on a query with
three tables from a many to many relationship. There is a table for the Entries, and a
separate table for Tags. There is also a junction table to store each instance of
a tag used in any particular Entry. As such a journal entry can be related to
many tags records, and any tag record can be related to many Entries.

So how does this work? When the user selects a tag name from the drop down list, Access fires the Combo Box AFTER UPDATE event. This in turn executes the followingVBA code that I wrote for this event:

Private Sub comTag_AfterUpdate()

Dim varSQLWhere As String
varSQLWhere = "tagId = '" & Me!comTag & "'"
DoCmd.Close acForm, Me.Name
DoCmd.OpenReport "rptTagSearch", acViewReport, , varSQLWhere

End Sub
NB for sake of clarity, I have removed all code related to error handling.

The code begins by defining a string type variable called varSQLWhere - this is going to contain the code for an SQL WHERE clause (minus the WHERE keyword). Next I set the value of this variable to "tagId = '" & Me!comTag & "'". TagId is the name of the primary key of the table containing all of the tag names. In fact, tblTags only contains this one field. Me!comTag is a reference to the name of the Combo box used on the search form. Note the manner in which it has been concatenated into the string variable. Due to fact that the value contained in the combo box is itself a string, I have had to build two single quotation makes into the varSQLWhere string. So for example, if the user had selected the tag name 'Visual Studio' in the Combo Box, the contents of the string variable would be "tagId = 'Visual Studio'" .

The next line of code closes the search form so it does not get in the way when the form opens.

The penultimate line of code is the DoCmd.OpenReport method. This not only opens our report, but also passes our varSQLWhere variable as one of the method's parameters (the WhereCondition). When this is passed, only records matching our WhereCondition are displayed in the report results. As such, our report effectively filters our report results by the tag name selected by the user in the search form combo box.

It's not too difficult to apply this type of solution to the application that Marwa is building. The only real difference is that her Database search form consists of three combo boxes. This means that she would also need to use a Command Button ONCLICK event to start the search rather than relying on one of the Combo Box AfterUpdate Events. Then she would need to construct the varSQLWhere variable from all three combo box values. This would look something like:

varSQLWhere = "fld1 = '" & Me!Combo1 & "' AND fld2 = '" & Me!Combo2 & "' AND fld3 = '" & Me!Combo3 & "'"

So its slightly more complicated, but the same principles apply.

Finding out Maximum and Minimum Value's - The DMax and DMin Functions

2012-01-06T06:07:00.000-08:00

So we have a table of sales figures for 2011. Suppose we want to find out the largest and smallest sales value for the month of May. Access has two functions ideal for this task - these are the DMax and DMin functions.

DMax and DMin basically examine a domain of values, returning the largest and smallest value respectively. The syntax for using these functions follow the same format as the other domain related functions such as DLookUp and DSum which I blogged about last year. As such, we pass parameters for field name, the domain from which the field belongs (the table or query, for example), and an optional criteria if we are just interested in particular records (such as May sales for example).

Lets take a look at the syntax for DMax and DMin respectively:

DMax("fieldName", "tableName", "criteria")

DMin("fieldName", "tableName", "criteria")

This is the table that we are going to use in our particular scenario:

If you remember, we are interested in the largest and smallest sale during the month of May. As such, we are going to pass the field name "Total" for the first parameter, the table name "tblOrders" for the second, and the criteria "OrderDate >= #05/01/2011# and OrderDate <= #05/31/2011#" for the third. Note the third criteria is basically a SQL WHERE Statement (without the WHERE keyword) and uses the American Date Format (ie Month/Day/Year), rather than the International Date Format. This is the full syntax:

DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")

In previous posts I have shown you how to use Access Functions in Queries and Calculated Controls (see posts on Calculating Date Difference and DLookUp). For this example I would like to use the function within a VBA sub procedure.

I have created a form with an unbound text box called txtResult and two command buttons, cmdMax and cmdMin. When the user clicks one of the command buttons - lets say it is the cmdMax button - the VBA procedure containing the DMax function runs, calculating the maximum sale for the month of May, and displaying the result in txtResult.

Here is the VBA code that I used:

Private Sub cmdMax_Click()
Dim varMax As Currency
varMax = DMax("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")
Me!txtResult = varMax
End Sub

Private Sub cmdMin_Click()
Dim varMin As Currency
varMin = DMin("Total", "tblOrders", "OrderDate >= #5/1/2011# and OrderDate <= #5/31/2011#")
Me!txtResult = varMin
End Sub

As you can see each sub runs when it's respective command button's ONCLICK event is triggered. The result returned by the function is stored in a currency type variable called varMax or varMin. The value of txtResult is then set to that of the variable in order to display the result.

You can download this DMax/Min example database by clicking the link. Please feel free to experiment using different criteria and adding new data to the table etc.

Enable and Disable a Form Control using VBA

2011-12-15T12:34:00.000-08:00

Earlier this week I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form's Command Buttons' using VBA. This gave me the idea for the present Access tip.

The command button was located on a Customer Details form which had an Orders Subform in the lower section. The reason I wanted to Enable or Disable the Command Button (located on the parent form), was that it was used to Delete the selected record highlighted in the Orders Subform's datasheet. I needed to get the Delete Button to enforce the business rule whereby once an order has been confirmed, it should not be deleted. As such, if the user highlighted a confirmed order in the subform, the Delete button is dynamically disabled, and vice versa.

To do this I made use of the Order Subforms ON CURRENT event. This event is triggered whenever the focus moves from one record to another, or when the first record receives the focus as the form opens. As such, if a user selects a record in the subform datasheet by clicking on one of the rows, the subform's ON CURRENT event fires. This is the code I wrote to determine whether the Delete button should be Enabled or Disabled.

If IsNull(DLookup("OrderConfirmed", "tblOrders", "OrderId = " & Me!OrderId)) = False Then

Forms!frmcustomer!ctlDeleteOrder.Enabled = False

Else

Forms!frmcustomer!ctlDeleteOrder.Enabled = True

End If

As you can see, I have used an If ... Then ... Else Statement to determine whether or not the Order has been confirmed. In order to create the conditional expression, I used the IsNull and DLookUp functions together to see if the OrderConfirmed field of tblOrders contained a date. The IsNull function returns a boolean value, True or False, to indicate whether its parameter (in this case the result of a DLookUp function) is or is not null; and the DLookUp function, looks up the value contained in the OrderConfirmed field of tblOrders where OrderId matches that of the current record displayed on the Orders subform.

Since the presence of a date in the OrderConfirmed field indicates that the order has been confirmed, the IsNull function returning the value of FALSE (remember this is a double negative!), tells us the order has indeed been confirmed, and vice versa. As such, when the condition of the first line of the If Statement is False, the Delete Command Button on the main form should be disabled. This is done by referencing the Delete Command Button's ENABLED property, and setting it to FALSE:

Forms!frmcustomer!ctlDeleteOrder.Enabled = False

And if the result of the If ... Then ... Else Statement had returned TRUE, the Delete Command Button is Enabled by setting it's ENABLED property to TRUE:

Forms!frmcustomer!ctlDeleteOrder.Enabled = True

An Expression to Obtain a Full Name from Three Separate Fields

2011-12-09T02:39:00.000-08:00

There is a very good reason why we separate name fields when we create an Access Table. If we store the Title, First Name and Surname in a single field, we limit the capability of our application to interrogate this part of our data. For example, if we store a full name in a single field, we would not be able to sort a list of names into alphabetical order (because Access cannot differentiate between a title, first name and surname). Moreover, we would also not be able to extract just the title and surname in order to address a letter. This is because the individuals first name comes in between, thereby preventing us from using the name in a mail merge operation.

As such, there is a general database design convention of storing all the elements of a name in separate fields. This gives us maximum flexibility and control when we come to process this data. What's more, we still have the capability of joining (concatenating) the full name back together again through use of an expression in a query's calculated field, for example. So how is this done exactly?

Imagine we have a list of names stored in a table. We have separate fields for Title, FirstName, and Surname.

Figure 1: An Access Table containing a list of names
stored in separate fields.

The expression we are going to use to concatenate the name into a single whole is as follows:

[Title] &" " & [FirstName] & " " & [Surname]

The three fields are separated by two ampersands (&), and a string containing a single space in between. The ampersand concatenates the various elements of the name, and the empty space between the quotation marks simply creates a space between the three fields when joined together. So we have a total of five separate elements concatenated by the ampersand operator - ie Title & space & FirstName & space & Surname.

As mentioned above, this expression can be used in a calculated field of an Access Query. To do so, just enter an alias (ie the name we are going to call the calculated field) with a colon in front of the expression. For example:

FullName: [Title] & " " & [FirstName] & " " & [Surname]

This is entered into the FIELD row of the query design grid as follows:

Figure 2: Expression to concatenate a full name
entered into the Query Design Grid.

Then, when we run the query we get a list of full names, each one appearing as a single field:

Figure 3: Concatenated Names appearing in
Query Result.

Adding a Group and Sort to an Access Report

2011-12-01T12:22:00.000-08:00

Adding a Group and Sort to an Access Report has the potential to make our data much easier to read. This is because our information may appear in a clear and more logical format. Take a list of Contacts for example. We may have a number of contacts working for the same organisation: wouldn't it be convenient for our contact list to be grouped by organisation? Then, depending on how many contacts we have within each organization, it may also make sense to sort each organizations' contact's into alphabetical order.

So instead of having a list like this:

Figure 1: Contact list without Group or Sort.

We have a list like this:

Figure 2: Contact list grouped by Company and sorted by surname and first name.

Both contain exactly the same data, but the list in figure 2 has been grouped by company and sorted by surname and first name (within each group). You can download these reports by clicking this link: Group and Sort Sample Database.

Lets take a look at how these reports were created.

I began by creating the report from figure 1. I then opened it in DESIGN VIEW and added the Group and Sorts before re-aligning the columns. Here are the step by step instructions:

Stage One - Create a Basic Report

Highlight the Contacts Table in the NAVIGATION PANE. The table in the sample database is called tblContacts, and it is the RECORD SOURCE for both Reports.
Click the REPORT icon (located in the REPORTS group of the CREATE ribbon). This is the quickest way to create a report based on a particular record source.
When the report opens in LAYOUT VIEW, click this symbol: (it should be located at the top left corner of the ID column). This highlights all the cells which are currently joined together.
Then click the REMOVE icon to separate them. This icon is located in the CONTROL LAYOUT group of the ARRANGE ribbon. It will now be possible to move individual text boxes and labels independently when we go to DESIGN VIEW. However, before we do that, we shall first add the Group and Sorts.

Stage Two - Adding the Group and Sorts

Select the HOME ribbon, and then pick DESIGN VIEW from the VIEWS group.
Make sure the GROUP, SORT AND TOTAL icon is highlighted (it is located in the GROUPING AND TOTALS group of the DESIGN ribbon). You should see the GROUP, SORT AND TOTALS pane open below the DESIGN GRID.
Click ADD A GROUP from the GROUP, SORT AND TOTALS pane (see figure 3 below).
Then select the Company field from the list which appears. This creates a Company Header (see figure 4 below).
Highlight the Company text box and make sure no other controls are highlighted.
Cut and past the Company text box so that it is positioned close to the left margin within the Company Header.
Reposition the labels and text boxes so that they are aligned in a neat logical fashion (see figure 4 below).
Next click ADD A SORT in the GROUP, SORT AND TOTALS pane.
Then select Surname from the list. Notice how a new level has been created below the Company Group. This is because we want Access to apply the group first and then sort the surnames within the group.
Click ADD A SORT again.
Then select Firstname from the list. This creates another sort, but this time on Firstname. Notice how this sort is on a level below the first sort. This is because we want Access to begin by sorting the Surnames, and then if there are duplicates, to sort on FirstName. This follows the general convention of placing the whole name in alphabetical order.

Figure 3: The Group, Sort and Total pane.

Figure 4: Report Design View showing a Group Header for the Company field.

You can now open the report in REPORT VIEW to see the grouped and sorted results.

Using Events and Manipulating Property Settings - Learning Access VBA - Tutorial 4

2011-11-24T10:55:00.001-08:00

This is the last post in the series of introductory tutorials on Learning Access VBA. So far we have covered a wide range of areas - the VBA Development Environment, variables, referencing form controls, the conditional IF ... THEN... ELSE statement, and Loops. There is still much more we could have covered, even at an introductory level (arrays for example). However, I wanted to end the series by focusing on how we use VBA to automate the access database applications that we create. To do this we are going to examine how to trigger our VBA code through tapping into events and manipulating form property values.

Using Events

If you have been following the Learning Access VBA series, you will already be familiar with the Command Button's ON CLICK event. The user clicks the command button at run time thereby firing the ON CLICK event, which in turn triggers any code that we have written in that event's sub routine. There are many more events which we can also use to trigger our code. A text box control has, for example, an ENTER event and an EXIT event. The ENTER event fires when the user moves the cursor into the text box, and the EXIT event fires when it moves out again. The code that you may attach to such events depends entirely on the particular needs of the application. The point is, they are there to use if we have written any code to trigger in response to the particular event in question.

So far I have only mentioned events for individual controls. Some of the more important events, however, occur at Form or Report level. These include the Forms ON CURRENT, BEFORE UPDATE and ON LOAD events. The ON CURRENT event fires just before a form displays a new record; BEFORE UPDATE occurs just before the record is saved; and the ON LOAD event occurs as a form loads data contained in a table, or derived from a query.

You can see the whole range of form or reports events by opening the PROPERTIES pane (whilst in design view) and clicking the EVENTS tab. In today's exercise, however, we are just going to focus on the form ON OPEN event. This fires as the form opens, but before any data is loaded from the forms RECORD SOURCE. As such it occurs before the ON LOAD even which was mentioned above. Consequently, this is a good event to use for triggering code for tasks like prompting the user for parameters, applying filters, or even changing the form's RECORD SOURCE property. In the exercise we shall be doing, we are going to use the ON LOAD event to determine whether a form opens in DATA ENTRY, EDIT or read only mode. Before we begin, lets first take a look at how we can manipulate form properties using VBA code.

Figure 1: Some of the Events listed in the
EVENTS tab of the PROPERTY SHEET.

Manipulating Properties

You may already have experience of setting properties in Design View using the PROPERTIES sheet . With VBA we can also read, test and dynamically change property setting during runtime. The key to doing this is understanding how to correctly reference the property of the control, form or report. This task is easier when our code is contained within the VBA Module of the form or report being referenced. Suppose we want to reference the DATA ENTRY property of a form called frmCustomers. If we were referencing the property from the VBA module attached to frmCustomers we can use the ME keyword. This acts as a short cut when referencing the attached form. This is how it works:

Me.DataEntry

If, on the other hand, we were attempting to make the same reference from a module outside of frmCustomer we would need to write the full reference like this:

Forms![frmCustomers].DataEntry

As you can see, we not only need to write the name of the form in question but we also need to specify the FORMS collection object to which it belongs. It is also worth pointing out that in VBA we use the exclamation mark (!) to separate two objects when the preceding object belongs to the one coming after, but we use the full stop (.) to separate an object from one of its properties. Any form, report or control name in the reference also need to be contained within square brackets if the name contains a space or a reserved word (but this is not essential if the name is constructed without these 'problematic' elements).

Now we know how to create property references, we can use them in our code to read, test or dynamically change property values. For example:

To store a property setting in a variable called varDataEntry:
varDataEntry = Me.DataEntry

To test the value of a property setting:
If Me.DataEntry = true Then

To change the value of a property setting:
Me.DataEntry = False

Exercise

In the following exercise we are going to create a switchboard form with three command buttons - these are ctlAdd, ctlEdit and ctlRead. All three buttons open the same form, frmContacts, but the first button opens the form in Data Entry mode, the second in Edit mode and the third as Read Only. So how does it do this?

Figure 2: frmSwitchboard

When the user clicks one of the command buttons, it fires the command button's ON CLICK event where there is some VBA code to open the form with the DoCmd.OpenForm method. One of the parameters of the OpenForm method passes an OpenArgs parameter to frmContact. The value of this parameter (which is a string) is then stored by Access in frmContacts OPENARGS (Open Argument's) property.

As frmContacts opens, the ON OPEN event fires, triggering another block of VBA code. This code tests the value of the forms OPENARGS property using the IF ... THEN ... ELSE statement. This is to ascertain whether the value contained in the OPENARGS property is "Add", "Edit" or "Read". These were passed by the OpenForm method as mentioned above. If the OPENARGS property value is "Add" our code will set the form's DATA ENTRY property to TRUE; if the value is "Edit", DATA ENTRY is set to FALSE; if "Read", the DATA ENTRY, ALLOW ADDITIONS, ALLOW DELETIONS, and ALLOW EDITS properties are all set to FALSE.

You can download the completed solution by clicking this link: Events and Properties Exercise. The instruction for creating the exercise follow below:

Stage One - Creating the Switchboard

Create an unbound switchboard form called frmSwitchBoard.
Add three Command Buttons called ctlAdd, ctlEdit, and ctlRead.
Attach the following code to the Command Button's ON CLICK event - see the first tutorial in the Learning AccessVBA series if you need help doing this.

Private Sub ctlAdd_Click()
DoCmd.OpenForm "frmContacts", , , , , , "add"
End Sub

Private Sub ctlEdit_Click()
DoCmd.OpenForm "frmContacts", , , , , , "edit"
End Sub

Private Sub ctlExit_Click()
Application.Quit
End Sub

Private Sub ctlRead_Click()
DoCmd.OpenForm "frmContacts", , , , , , "read"
End Sub

NB You may have noticed there are a number of blank parameters passed in our use of the OpenForm method - hence the comma's . That is because many of the parameters are optional. We are just passing two parameters - the name of the form to be opened, and the OpenArgs parameter as discussed above. Incidentally we could have used the DataMode parameter to accomplish the object of this exercise more directly, but I wanted to demonstrate the working of the form ON LOAD event.

Stage Two - Creating frmContacts

Before you create frmContacts you will first need to create the table - tblContacts - which is going to be its record source. It's not particularly import which fields are used, but the example database I created has four fields - ID, FirstName, Surname and Company, and the following sample data:

Figure 3: tblContacts with sample data.

Figure 4: frmContacts

Create a new form called frmContacts
Set the forms RECORD SOURCE property to tblContacts.
Add the four fields of tblContacts to the form
Add the following code to the forms ON OPEN event:

Private Sub Form_Open(Cancel As Integer)

If Me.OpenArgs = "add" Then

Me.DataEntry = True

ElseIf Me.OpenArgs = "edit" Then

Me.DataEntry = False

ElseIf Me.OpenArgs = "read" Then

Me.DataEntry = False

Me.AllowAdditions = False

Me.AllowDeletions = False

Me.AllowEdits = False

End If

End Sub

When you have done this, save your work, and open the switchboard to try out the different buttons.

This concludes the Learning Access VBA series of tutorials. I hope you have found them helpful, and that they have given you a basic introduction to Access programming . I intend to cover more advanced VBA topics next year.

Introduction to VBA Loops - Learning Access VBA Tutorial 3

2011-11-18T02:22:00.001-08:00

This is the third tutorial in the Learning Access VBA series. In the first tutorial we learnt how to create a simple VBA Sub Procedure and set up up Variables; and in the second, we looked at Evaluating Conditions with the IF THEN ELSE statement. Today we are going to move on to VBA Loops.

Loops are are common feature of most programming languages, and VBA is no exception. Loops enable lines within in a defined section of code to be repeated as many times as is necessary, until a specific point is reached when the program flow may move on to the next section of code. There are different kinds of loops in VBA. The FOR ... NEXT loop, for example, repeats the loop a set number of times. We would use this if we wanted the loop to repeat, say, 10 times, and then move on. With the DO ... UNTIL loop, on the other hand, the defined section of code would repeat indefinitely, until a specified condition arises. This may be something to use if you want to repeat the loop until a user enters a specific value into an input box.

The FOR ... NEXT Loop

Let take a look at how the FOR ... NEXT loop is constructed.

For I = 1 To 10
lines of code to be repeated
Next I

The FOR NEXT loop has a counter which is referred to above as I. This is basically a variable name - we could have called it a different name if we had wanted. The first line of the statement (For I = 1 to 10) sets the counter at 1, and tells the procedure to continue repeating the lines of code until the counter reaches 10. Each time the program flow executes the NEXT keyword at the end of the statement, the counter is increased by 1. As it does so, it also checks whether the counter has reached 10 (or whatever level it had been set). If it has not, the lines of code repeat another time until the Next keyword executes again, increasing the count by another 1. Once it reaches the level set, the program flow continues past the NEXT keyword into the next section of code.

So let's try an exercise to see how the FOR ... NEXT loop works in practice.

Create a new unbound form in Design View.
Add a Command Button.
Add the VBA code (listed below) to the button's OnClick Event (please see the first Learning VBA Tutorial if you need help with this, or any of the previous steps):
Click the SAVE icon.

Dim I As Integer

Dim x As Integer

Stop

For I = 1 To 10

Randomize

x = (9 * Rnd() + 1)

Debug.Print "Counter = " & I & "; Random Number = " & x

Next I

Before we try out the code, we are going to open the VBA Editor's Immediate Window. To do this, click the VBA VIEW menu, and then click IMMEDIATE WINDOW from the menu list. When we run the code, the STOP statement will re-open the VBA editor during runtime, to allow us to Step Into each

line of code as it executes. Then when the program flow reaches the Debug.Print line, the Counter value and the value of variable x (a random number generated by the code) is displayed in the immediate window.

When you are ready, open the Access form and click the command button we created earlier. The VBA editor will then open. The current line being executed is highlighted in yellow. To advance to the next line we click Step Into from the DEBUG menu (alternative use the F8 key which is far more convenient). Then watch what happens as the program flow progresses through the loop. Also take note of the counter value which is displayed before the random number in the immediate window.

Figure 1: The VBA Editor with Immediate Window. The editor was opened during
run time due to the STOP command which acted as a breakpoint.
Another way of adding a break point is to click on a line
and then select TOGGLE BREAKPOINT from the DEBUG menu.

The DO ... UNTIL Loop

Let's take a quick look at the how the DO ... UNTIL loop is constructed.

Do Until variableName = thisValue

lines of code to be repeated

Loop

The first line of the DO ... UNTIL loop is testing whether the specified condition is true or not. If it is not true, the lines of code within the loop are executed until the LOOP keyword is reached at the end of the statement. Then the program flow returns to the first line of the statement where the condition is tested again. If the lines of code within the loop had changed the value of the variable being tested so the condition becomes true, the program flow exits the loop at this top line.

Here is an example of a DO ... UNTIL Loop that you may like to try for yourself. Just change the code from the first exercise so that this runs in its place.

Dim varNumber As Integer
Dim varResponse As Integer

Randomize
varNumber = 9 * Rnd() + 1

Do Until varResponse = varNumber

varResponse = InputBox("Enter a Number Between 1 and 10", "Guess A Number")

If varResponse > varNumber Then
MsgBox "Your Guess is too High"
ElseIf varResponse < varNumber Then
MsgBox "Your Guess is too Low"
Else
MsgBox "Congratulations: Your Guess is Correct."
End If

Loop

MsgBox "Game Over!!!"

(NB for the sake of clarity, this code does not deal with the possibility that the user may click cancel or enter an empty value in the input box. If this happens an error results, and the procedure crashes!)

This code is for a simple Guess the Number Game. The user clicks the command button to begin. The code then generate a random number and stores it in a variable called varNumber. Once the program flow gets to the loop, the variable varResponse is tested to determine whether it is equal to the random number stored in varNumber. If it is not, the lines of code within the loop are executed. The user is asked to guess the number in a pop up input box. The code then enters an IF ... THEN ... ELSE statement where the user is informed whether his guess is too high, too low, or is indeed correct. When the LOOP keyword is reached, the program flow returns to the DO UNTIL keyword where the value of the guess is tested against the random number to determine whether the lines of code should be repeated, or whether the program flow can continue past the LOOP keyword.

When you try this code, please feel free to enter a BREAKPOINT so you can follow the program flow for yourself. You can do this by entering the STOP statement as we did in the first exercise. Alternatively, click the line where you want to insert the breakpoint, and then click TOOGLE BREAKPOINT from the DEBUG menu. The line is then highlighted in red., but the action is the same.

Evaluating Conditions with the If...Then...Else Statement - Learning Access VBA. Tutorial 2.

2011-11-11T01:37:00.001-08:00

This the second post in the Learning Access VBA series. Last week I introduced you to the VBA Development Environment where we we also learnt a little about variables. We completed a simple exercise which referenced the values entered by the user into two text boxes, stored the values in variables, and added the values contained therein together. The result was then displayed in a third text box. This week we are going to move on, and look at how VBA deals with Conditions using the If ... Then ... Else Statement.

So what do we mean by the term conditions or the Conditional? Basically as our VBA code is executed (that is to say, as the code runs, line by line, processing each statement in turn) the program flow may well reach various points where it needs to branch according to whether a given condition has or has not been met. For example, we may want our code to run a short block of nested code if the value of a specified variable is greater than a given number. If the condition is met, the program flow runs the statement contained in the nested code below; whereas, if the condition is not met, the program flow may branch to the next statement after the nested code - the nested code is, in effect, skipped.

Lets take a look at how the If ... Then ... Else Statement is constructed:

IF conditon is met THEN

nested code1

ELSE

nested code2

END IF

The IF keyword tests whether the condition is true or not. If it is true, the THEN keyword directs the program flow to the code contained in nested code1. ELSE is optional. If we use it, ELSE directs the program flow to that contained in nested code2 if the condition after the IF keyword had not been met. Had we not used the ELSE keyword (with nested code2), the program flow would have skipped nested code1 and gone straight to the END IF keyword.

So, for example, our code may read:

IF varFirstNumber > varSecondNumber THEN

MsgBox("First Number is Greater than Second Number")

ELSE

MsgBox("First Number is not Greater than Second Number")

END IF

This tests whether the number contained in variable varFirstNumber is greater than that contained in varSecondNumber. If the condition was met, the nested code below the IF keyword would run. This displays a message box saying "First Number is Greater than Second Number". If the condition was not met, the nested code below the ELSE statement would have run instead. This would have displayed a message box saying "First Number is Not Greater than Second Number". The END IF keyword closes the If ... Then ... Else Statement.

There is one other optional keyword which is well worth mentioning - that is ELSEIF. When we only use IF/THEN and ELSE, we have just two possible branches in the program code. However, suppose there are three or more possible conditions? For example, suppose we want to test whether the variable varFirstNumber is (a) greater than, (b) less than, or (c) equal to, the second variable? ELSEIF is a good way to do this. Lets see how we would code this scenario:

IF varFirstNumber > varSecondNumber THEN

msgbox("First Number is Greater than Second Number")

ELSEIF varFirstNumber < varSecondNumber THEN

msgbox("First Number is Less than Second Number")
ELSE
msgbox("First Number is Equal to Second Number")

END IF

As we can see, the ELSEIF keyword has allowed us to enter a second specified condition to test, in the event of the initial condition proving false.

As such, when the program flow reaches the If ... Then ... Else Statement, the initial condition is tested by the first IF keyword; if this is true, the nested code in the line directly below is executed, and then the program flow branches to the END IF keyword at the bottom of the statement. However, if the first condition had been false, the first block of nested code is skipped, and the program flow goes to the ELSEIF keyword and the second conditional expression is then tested; if this proves true, the second block of nested code is executed. However, if the 2nd expression had also proven false, then the 2nd nested code would also be skipped, and the program flow would move down to the ELSE keyword. There is no condition to test here, so the 3rd block of nested code is executed unconditionally.

(NB In the above scenario there were three possible outcomes. However, it is also worth mentioning that by adding additional ELSEIF keywords into the statement, the number of possible conditions to test is potentially endless.)

Compare Numbers Exercise

Let's end this tutorial by applying what we have learnt above to the following exercise.

We are going to create a form with two text boxes named txtFirstNumber and txtSecondNumber. Then we will add a command button, ctlCompare, with some code attached to it's ON CLICK event. We learnt how to do this in last weeks Learning Access VBA tutorial. However this time our code will compare the numbers entered into the two text boxes by the user at runtime, and display a message box informing the him/her whether the First Number is (a) Greater than, (b) Less than, or (c) Equal to, the Second.

This is a screenshot of our form in action:

Here the user has entered 10 in txtFirstNumber and 20 in txtSecondNumber.
After the user clicks the Compare command button, Access runs the VBA code
that compares the two numbers and displays the appropriate message.

Instructions

I won't go into great detail regarding instructions - we should have leant all the required skills from last weeks post on Learning Access VBA. There is also more information about creating an unbound Access Form in the post about Creating an Access Form from Scratch. I advise you to check out these posts if you need a reminder:

Create a new unbound Access Form.
Add two text boxes called txtFirstNumber and txtSecondNumber.
Add a Command Button called ctlCompare.
Select the Command Button and open the PROPERTIES SHEET.
Click the Events tab of the PROPERTY SHEET.
Click the three dots symbol (...) on the right of the ON CLICK property cell.
Open the VBA Editor by clicking CODE BUILDER from the CHOOSE BUILDER menu.
Copy and paste the code listed below (excluding the first and last line which should have been added to the VBA editor automatically).
Click the save Icon and close the VBA Editor.

Private Sub ctlQuestion_Click()

Dim varFirstNumber As Integer

Dim varSecondNumber As Integer

varFirstNumber = Me!txtFirstNumber

varSecondNumber = Me!txtSecondNumber

If varFirstNumber > varSecondNumber Then

MsgBox "First Number is Greater than Second Number"

ElseIf varFirstNumber < varSecondNumber Then

MsgBox "First Number is Less than Second Number"

Else

MsgBox "First Number is Equal to Second Number"

End If

End Sub

To test out the code, open your form in FORM VIEW, enter a number in each of the text boxes, and click the COMPARE button. NB for clarity there is no code to validate whether two numbers have been entered in the text boxes. An error message will result if one or both of the text boxes is empty when the compare button is clicked.

The code begins by declaring two variables, and then assigns values to each one by referencing the two respective text boxes (see Learning Access VBA for more information about this process). The If ... Then ... Else statement then tests whether the first number is greater than, less than or equal to, the second number. A message box opens containing the result.

Learning Access VBA - A Beginners Guide

2011-11-04T12:33:00.000-07:00

This post is all about getting started with the Access VBA programming language - a first tutorial starting right at the beginning. As you may already know, the Access Database comes with its own programming language called Visual Basic for Applications, or VBA. It offers a powerful way to automate and generally control how an access database application operates. However, whilst relatively easy to use, getting started can seem a little daunting to the newcomer. As such, this tutorial is intended to help the beginner feel at home in the programming environment.

Figure 1: The Visual Basic Icon

The first problem faced by the beginner is finding out how to get into this thing called VBA. This is because, technically, VBA is a separate software package which works alongside Access. That said, we can only open the VBA Editor (see figure 2 below) from within the Access database itself. The most direct way of doing this is by clicking the VISUAL BASIC icon (located in the MACRO's group of the DATABASE TOOLS ribbon - see figure one above). You might like to download the Learning Visual Basic Sample Database, and give this a try.

Learning Visual Basic Sample Database

Figure 2: The VBA Editor

The sample database consists of an unbound form which acts as a simple calculator. There are three Text Boxes and two Command Buttons. The user enters two numbers in the top two text boxes and clicks the equals (=) buton. This runs a block of VBA code (called a sub procedure) which reads the numbers from the two text boxes, adds them together, and displays the result in the third text box. The user can then click the Clear button (C). This runs a different block of code to clear all the existing numbers from the text boxes so new numbers can be entered and calculated.

Figure 4: The Simple Calculator form
described in the paragraph above.

So lets take a look at how this is created. We shall do so in two stages: first we shall create the form, then open the VBA Editor and enter the two blocks of code.

Creating the Form

Click the FORM DESIGN icon (located in the FORMS group of the CREATE ribbon).
Drag and drop three text boxes on the the form (if you need help, you might like to check out my tutorial on how to Create an Access Form from Scratch).
Next drag and drop the two Command Buttons onto the design grid. Make sure the USE COMMAND WIZARDS icon is not highlighted when you do this - otherwise just click the CANCEL button if the Command Button Wizard opens.
Click on the label for the top text box and change the text to read "First Number".
Repeat step 4 for the second and third labels, entering "Second Number" and "Result" respectively.
Then click on the top text button and change the text to "C". This will be the Clear Button.
Do the same for the lower text button, changing the text to "=". This will be the Equals Button.
Next click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).
Set the NAME property (located at the top of the OTHER TAB of the PROPERTY SHEET) for the first text box to txtFirstNumber.
Repeat step 9, calling the lower two text boxes txtSecondNumber and txtResult, and calling the Command Buttons ctlClear and ctlAdd.

NB It is important to enter the NAME properties correctly. Although we could have called them anything we wanted, our VBA code refers to these form controls via the NAME property. As such we need to ensure that the names used on our form are going to match those to be used in the code. This is an important principle to grasp as we use VBA alongside Access.

Entering the VBA Code

Next click on the ctlAdd (=) Command Button whilst our form is still open in DESIGN VIEW.
Open the PROPERTIES SHEET (if it is not already open) and click the EVENTS tab ctlAdd.
Click the cell for the ON CLICK property. Then click the three dots button (...) at the right of the cell. This opens the CHOOSE BUILDER dialog box.
Select CODE BUILDER from the menu items, and click OK. This opens the VBA Editor in the exact location where we are going to enter the code which is triggered when the user clicks the button at runtime (the ON CLICK Event). It even writes the first and last line of code which makes this a self contained block of code (a Sub Procedure).
Enter the code (listed below) between the Private Sub and End Sub lines:

Private Sub ctlAdd_Click()

Dim varFirstNumber As Double

Dim varSecondNumber As Double

Dim varResult As Double

If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

Exit Sub

End If

varFirstNumber = Me!txtFirstNumber

varSecondNumber = Me!txtSecondNumber

varResult = varFirstNumber + varSecondNumber

Me!txtResult = varResult

End Sub

Now repeat all of the last stage from 1 to 5, but this time for the ctlClear Command Button. Enter the following code in the same manner as above:

Private Sub ctlClear_Click()

Me!txtFirstNumber = Null

Me!txtSecondNumber = Null

Me!txtResult = Null

End Sub

When this is complete, you may save your code by clicking the SAVE icon, and close the VBA Editor. To test the code simply open the form in FORM VIEW, enter the numbers to calculate, and click the ctlAdd button. The correct result should display in the third text box once this is done.

How the Code Works

Please don't worry about being able to create this code yourself at this stage in the learning process. The main purpose of this tutorial was to get you familiar with the programming environment, rather than how to construct the code. I intend to do this in future tutorials. However, we will go through each line of the first sub procedure just to give you a general idea of how the programming code works.

The code is triggered by the Command Button's ON CLICK event. The user clicks the command button, Access 'raises' the ON CLICK event, and the block of code runs one line at a time.

Private Sub ctlAdd_Click()
The first line, the SUB Statement, was created automatically by Access when we opened the VBA Editor from the property sheet. It's purpose is to mark the beginning of this block of code or Sub Procedure. The PRIVATE part of the statement relates to the concept of Scope. We won't worry too much about what this means at this stage, except to say it is concerned with where the sub can be 'called' from. Private means just that - it is private to this particular form module, and can not be called from a module elsewhere in the application. The part which says ctlAdd_Click is the name of the sub procedure.

Dim varFirstNumber As Double

Dim varSecondNumber As Double

Dim varResult As Double
The next three lines of code declare three separate variables. The purpose of variables are to enable values to be temporarily stored, processed, and retrieved. More about this soon. At this stage we are just going to note that we have used the DIM Statement to define three variables called varFirstNumber, varSecondNumber and varResult. We have also included AS DOUBLE to tell Access that the variables are going to store values of the DOUBLE data type (so we can calculate decimal numbers).

If IsNull(Me!txtFirstNumber) = True Or IsNull(Me!txtSecondNumber) = True Then

MsgBox "Please Enter Numbers in Both Textboxes", vbInformation, "Missing Number(s)"

Exit Sub

End If
This next section of code is an IF Statement, and forms a self contained block of code within the sub procedure itself. It was something I added to ensure the user enters a number in each of the two text boxes. If it detects that there is a Null value it runs the nested code to display a message box, and then exits the sub.

varFirstNumber = Me!txtFirstNumber

varSecondNumber = Me!txtSecondNumber

In this section of code, we are assigning each of the two variables with a value. The equals sign assigns the variable name on the left of the sign with the value on the right. In this case the top line is setting the value of the varFirstNumber variable to the value entered by the user in the txtFirstNumber text box control. Note that the text box is referenced by means of the code: Me!txtFirstNumber. The Me! part of this is telling access that the text box belongs to the form to which this VBA module is attached ie frmCalculator; and txtFirstNumber is the name we gave to the top text box on the form we created earlier.

varResult = varFirstNumber + varSecondNumber
This line is assigning the variable varResult with a value. In this case the value being assigned is the sum of the values stored in varFirstNumber and varSecondNumber. Put another way, this line is performing the addition calculation and storing the result in the variable varResult.

Me!txtResult = varResult
This line is using the value stored in the varResult variable to set the value to be displayed in the lower text box of the form, txtResult. It references the text box in the manner already described above. This time, however, the text box reference is on the right hand side of the equals sign, and the variable on the left. This is because the value of the variable is being retrieved, and the value of the text box is being set.

End Sub
This line was generated automatically just like the first in this block of code. However, this time it is marking the end of the sub procedure.

Try this Yourself
If you are feeling adventurous, you might like to try creating more Command Buttons for the Calculator form. Try creating a button to multiply or subtract the values stored in the top two text boxes. You can follow the same procedure as before. However, when you copy and paste the code, change the addition operator (+) in the appropriate line for multiply (*) and subtract (-) respectively.

Using Conditional Formatting on an Exam Results Report

2011-10-28T08:43:00.000-07:00

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.

Open the report in DESIGN VIEW
Right click the Result Text Box.
Click CONDITIONAL FORMATTING from the drop down menu.
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:

First set the DEFAULT FORMATTING section so it displays a BLUE FONT. Do this by selecting BLUE from the FONT/FORE COLOR icon
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.
Click the ADD >> button to show CONDITION 2.
Repeat the process of stage 2 so that a BOLD RED FONT displays when FIELD VALUE IS LESS THAN 40.
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.

Again, set the DEFAULT FORMATTING section so it displays a BLUE FONT. Do this by selecting BLUE from the FONT/FORE COLOR icon
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.
Click the ADD >> button to show CONDITION 2.
Repeat the process of stage 2 so that a BOLD RED FONT displays when EXPRESSION IS [Results]<40
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.

Creating an Update Query to Update a Salaries and Wages Database

2011-10-21T09:01:00.000-07:00

Imagine you have responsibility for the management of a Company's Salaries and Wages database. The company employs 200 colleagues, and it is your job to process the annual pay increase. The problem you face is that different groups of employees receive a different percentage rate increase; and each employee within a group may have a different salary level to start with. So what is an efficient way to update each employee's salary without having to having to go through each record manually? A good solution would be to use an Update Query.

Update Queries are a type of Action Query, similar in nature to Delete Queries, which I blogged about last month. However, whereas a Delete Query will find a group of records matching a given criteria and delete them from the table, the Update Query will find a group of records matching a given criteria and update one or more fields in each of those records. So for example, we can use an Update Query to update the salaries of all staff with the job title, Telesales Administrator.

The great thing about using an update query is that we can update a field to an absolute value or a value based on a calculation. So in addition to being able to update all Telesales Administrator salaries to a fixed value such as, say, £15000, we could also update all existing Telesales Administrator salaries by, say, 5%. So in a scenario where each Telesales Administrator is on a different salary level to begin with, it makes sense to do the latter to update each unique salary figure.

Please feel free to download the completed solution to the above scenario by clicking this link: Example Update Query. I recommend you begin by opening tblEmployees and take a look at the 12 sample records and then see how the records for Telesales employees change after the qryUpdateTelesalesSalaries Update Query is run. You can also follow the instructions below to find out how to create this Update Query yourself.

Figure 1: tblEmployees. As you can see, there are 6 Telesales Employees.
Notice how their salaries change after the Update Query is run.

Creating an Update Query

So how do we create this Update Query? It is easy. We do it in two stages. First of all we create a standard Select Query which uses a criteria to find the group of employee records who have Telesales as their job title. Then we convert the query to an Update Query, and in the process, we enter the calculation which updates the existing salary level for each Telesales Administrator. Lets do this step by step:

Stage One - Testing the Criteria with a Select Query

Click the QUERY DESIGN icon (located on the QUERIES group of the CREATE ribbon).
Select the tlbEmployees table from the SHOW TABLES dialog form.
Double click the JobTitle and Salary fields from the employees table, so they appear as columns on the Query Design Grid.
Enter ="Telesales" in the criteria row of the JobTitle column.
Click the RUN icon to run the query.

The results of the query are shown in the screenshot below:

Figure 2: Results of the Select Query
in stage one.

As you can see, the query has found all six records where the employee's job title is Telesales. We could have included their names and employee id's in the results, but this is not necessary. Once we convert it to an Update Query and click run, we won't actually see the query results until we open the underlying table and note the updated fields in each of the records matching the criteria.

Stage Two - Converting to an Update Query

Re-open the Select Query in Design View. If the Select Query is already open in DATASHEET VIEW, simply click the DESIGN VIEW icon from the VIEWS group of the HOME ribbon. Otherwise you can highlight the Query name in the NAVIGATION PANE, right click the mouse, and then click the DESIGN VIEW icon from the drop down menu.
Once the Query opens in DESIGN VIEW, click the UPDATE QUERY icon from the QUERY TYPE group of the DESIGN ribbon. The Icon is then highlighted in orange.
You will also notice that the SHOW and SORT rows of the QUERY DESIGN GRID have disappeared, being replaced by a new row called UPDATE.
All we need to do now is enter the calculation into the UPDATE row of the Salary column. The calculation to enter is: [salary]*1.05

Figure 3: This is what the Query Design Grid should
now look like for the Update Query.
Click the RUN icon from the RESULTS group of the DESIGN ribbon.
Click YES when you receive a warning message saying "you are about to update 6 row(s)".

Once you have run the query and clicked YES to the warning message, you won't see the changes until you re-open (or refresh) the underlying tblEmployees table. When you do so, you will see that the Salaries for Telesales staff have been updated by 5% as expected (see figure 4 below).

Figure 4: The Updated tblEmployees Table.

Using The Tab Control for Tidy Form Presentation

2011-10-14T10:59:00.000-07:00

The Tab Control is a type of form control used to aid presentation and enhance user-friendliness. It works much the same way as the Access Ribbon - the user clicks a tab to reveal a new strip. However, instead of Ribbon icons, these strips contain our text boxes or other form control that we choose. As such, it allows us to separate the fields on our forms, displaying one group at a time. Here is a screen shot of one I prepared earlier:

Figure 1a: The Tab Control: Tab 1 "Personal".

Figure 1b: The Tab Control: Tab 2 "Address".

Figure 1c: The Tab Control: Tab 3 "Education".

As you can see, the Employee Record Form in figure 1 above contains a Tab Control which is comprised of three separate Tabs. The first tab shown in figure 1a has been given the caption, "Personal", and displays fields relating to the Employee's ID, Name and Date of Birth. The second tab in 1b relates to the employee's Address, and the third in 1c relates to Education. As such, we see how the Tab Control has enabled us to organize the information into logical categories, in addition to presenting that information in a tidy and easy to access format. In fact,we have condensed 11 fields to fit on a small Dialog form.

Lets take a look at how the form from figure 1 was created.

Adding a Tab Control

To begin with I created a new form by clicking the FORM DESIGN icon. This is located in the FORMS group of the CREATE ribbon.
I set the RECORD SOURCE property (via the DATA tab of the PROPERTY SHEET) to the appropriate table.
I clicked the TAB CONTROL icon (located in the CONTROLS group of the DESIGN ribbon), and clicked a position for it to go on the form design grid. A blank Tab Control with two pages appears on the grid.
Figure 2: A blank Tab Control added to the
Form Design Grid.

Customizing the Tab Control

The process of customizing the Tab Control simply involved adding an extra tab page and giving each tab its own individual name - ie Personal, Address and Education.

I began by selecting the Tab Control by clicking its outer edge so that it was highlighted.
Then I clicked the INSERT PAGE icon (located in the CONTROLS group of the DESIGN ribbon). This added a new blank page to the tab control, thereby giving us the three required tabs.
Next I clicked the part of the Tab Control which says PAGE1 to select this particular tab page.
I then changed the CAPTION property for PAGE1 to "'Personal". This property is located on the FORMAT TAB of the PROPERTY SHEET.
I repeated the process for the other two tab pages - so I had a tabs for "Personal", "Address" and "Education" respectively.

Adding Text boxes to the Tab Control

The final stage involved adding the text boxes to the tab control. The key thing to remember here is that it is important to select the required tab page before adding the text box to the tab control. It is also worth mentioning that if you are moving an existing form control to the tab control, you will need to cut and paste rather than simply drag and drop (otherwise it will sit on top of the tab control and not be a part of it).

I began by clicking the ADD EXISTING FIELDS icon (located in the TOOLS group of the DESIGN RIBBON). This opened the FIELD LIST pane.
I then selected the Personal Tab of the tab control (like I did in stage three of the customization process above).
I then dragged the first four fields - EmployeeId, Firstname, Surname and DOB - from the pane over to the tab control and positioned them in the required location. When you try this yourself, notice how the tab control goes black as you position the text box. This shows that it is being added to the Tab Control rather than the form Detail.
I repeated stages 2 and 3, adding the Address and Education related fields to the Address and Education tabs respectively.
All I had to do then was align and resize the text boxes for a professional finish. A good way of doing this is to click on the text box and manually enter the grid position on the FORMAT tab of the PROPERTY SHEET - the TOP property determines the vertical grid position, the LEFT property the horizontal, and the WIDTH is self explanatory. You can also position the text box label's separately using the same method.

Figure 3: Adding fields to the Personal Tab Page.

Filtering Report Results Using the Filter Property

2011-09-30T11:15:00.000-07:00

In this tip we are going to filter the results of a Report to display records with order amounts above a given value. In so doing we are going to pay special attention to two Report properties: these are FILTER, and FILTER ON LOAD.

Our solution works when the user opens a Dialog Form containing a text box and command button. The user enters an amount value in the text box, and clicks the command button. The command button then runs a small amount of VBA code to open the report. As the report loads, it applies a filter defined in the Report's Filter Property to produce the desired result.

Figure 1: Dialog Form to collect the Amount Parameter.

So how does this work?

When the Report opens, Access checks the report's FILTER ON LOAD property (which we have set to YES). When it detects the property is set to YES, this tells Access to apply the report's Filter (which we have entered into the report's FILTER property). Our filter then references the users parameter value from the Dialog Form text box, and filters all records with Order Amounts greater than or equal to that value.

You can download the completed solution from here: Filter Property Tip Database. You may also want to work your way through the instructions below.

The Table

The solution contains a Table (tblOrders), a Dialog Form (frmParameter) and a Report (rptOrders). In the screenshot, below you will see the data we shall be working with in tblOrders:

Figure 2: tblOrders.

As you can see the table is made up of four fields: OrderId (AutoNumber), OrderDate (Date/Time), Customer (Text), and Total (Currency).

The Dialog Form

We then create the dialog form shown in Figure 1 above (there is more information about Dialog Forms in my last post). I have referred to the textbox as txtAmount, the control button as cmdOpenReport, and the form itself as frmParameter.

As I have already mentioned, the command button runs a small amount of VBA Code when clicked by the user. To add this code:

Select the command button on the form design grid, and click the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon).
When the PROPERTY SHEET opens, click the EVENT TAB.
Then select the ON CLICK event by clicking into that particular cell of the grid.
A three dot symbol appears at the right edge of the cell. Click it and select CODE BUILDER from the CHOOSE BUILDER dialog box which has opened.
Then click the OK button and the VBA editor opens.

Figure 3: The On Click Event on
the PROPERTIES SHEET.

You can can copy the code below and paste it onto the editor. The top and bottom lines that begin Private Sub... and End Sub respectively should have appeared already, so you just need to copy the middle section in between:

Private Sub cmdOpenReport_Click()

On Error GoTo MyError

DoCmd.OpenReport "rptOrdersFiltered", acViewReport

DoCmd.Close acForm, Me.Name

Leave:

Exit Sub

MyError:

MsgBox "Error " & Err.Number & ": " & Error$

Resume Leave

End Sub

The two important lines in this section of code begin with DoCmd (meaning do command). The first of these is the command to open the form in Report View. The second closes the Dialog form once the report has opened. NB it is very important the report opens before the Dialog Form closes, because it needs to reference the value contained in the Dialog Form's Textbox as the it loads.

The Report

The quickest way to create the Report is to base it on our table.

Select tblOrders in the Navigation Pane. It will highlight in orange.
Then select the CREATE ribbon , and click the REPORT icon (located in the REPORTS GROUP).

This creates a basic report which we can then then modify in DESIGN VIEW as required.

Figure 4: Report Design.

Setting the Filter Properties

Next we turn our attention to setting the Report's Filter Properties accessed via its Property sheet.

Click the Square Box in the top right hand corner of the Report Design Grid. This ensures we are going to be working the properties of the actual Form (rather than one of its controls).
Click on the PROPERTY SHEET icon (located in the TOOLS group of the DESIGN ribbon) whilst the Report is open in Design View.
Click the DATA TAB of the pane.
Make sure the SELECTION TYPE is set to REPORT. This should be the case if you carried out step 1 successfully. Otherwise just change the selection to Form in the drop down list.
Enter the following into the FILTER property cell: Total >= forms![frmParameter]![txtAmount] . This has the effect of filtering out report records where the amount value in the Total field is greater or equal to that entered by the user in the Dialog Form's textbox.
Then set the FILTER ON LOAD PROPERTY to YES. This ensures the filter is applied when the forms loads.
Save the Report as rptOrdersFiltered.
Close the Report

Figure 5: The Form's Filter Properties. Note the reference to the textbox
on our Dialog Form in the FILTER property cell.

The solution should now be ready to run. Just click the frmParameter form in the Navigation Pane to begin. The screenshot below shows what the report results look like when £50,000 is entered by the user as the amount parameter:

Figure 6: Report Results filtered for amounts greater than or equal to £50,000.

Creating a Dialog Form

2011-09-23T11:37:00.000-07:00

Dialog Forms are an integral part of most customized Access Database Applications. As developers, we set them up to enable our applications to communicate and interact with users. The simplest sort of dialog box is one which displays a message. These may be created using VBA's MsgBox Function or the MsgBox Macro Action. Others involve a greater degree of customization, especially when the application requires the user to provide information such as parameters for a query. One way to do is to create a standard Access form and modify it's properties to make it into a Dialog Form.

Figure 1: This Dialog Form obtains date parameters used to restrict the
records displayed in a report. You may remember this was NorAzri's chosen
method for filtering report results mentioned in my last post.

To create a Dialog Form such as that displayed in figure one, we begin by creating an unbound form. This is done by clicking the FORM DESIGN icon from the FORMS group of the CREATE ribbon. The term unbound means that the form is not bound to a particular Table as with conventional forms. The information it is designed to collect is temporary, and does not need to be stored.

When a new form opens in design view, the screen will show a blank grid in the main window, and a variety of form controls in the CONTROLS group of the DESIGN ribbon. The Dialog Form in figure 1 uses four Combo Box Controls and a Command Button. The values displayed in the Combo Boxes are defined in the Row Source Property. I recently blogged about Customizing an Access Combo Box. If you are interested, I recommend you check it out by following the link above (just remember that the combo boxes in this example do not need a Control Source because we do not need to store it's data in a table).

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form. So how is this done? The answer to this lies in a number of key form properties. The two most important properties to understand are the POP UP and MODAL properties.

The POP UP property ensures that when the dialog form is opened, it appears as a pop up box rather than a full size form. Otherwise it would just appear as an ordinary form rather than dialog form. As such this property should always be set to YES. Setting the MODAL property to YES ensures the user cannot ignore the dialog form by clicking on a ribbon icon or form control, for example. The idea here is that your application is waiting for a user response. Access will not do anything until that response is given, and/or the dialog form is closed. These properties are located on the OTHER TAB of the PROPERTY SHEET.

Figure 2: Set the POP UP and MODAL properties to YES. This is done
from the OTHER TAB of the PROPERTY SHEET. The property
sheet is accessed by clicking the PROPERTY SHEET icon
on the TOOLS group of the DESIGN ribbon.

NB When you change these properties make sure the SELECTION TYPE of the PROPERTY SHEET is set to FORM. If it is not, just click the drop down list at the top of the sheet and change it. Alternatively click the square box at the top left hand corner of the DESIGN GRID.

If you look again at the dialog form in figure1 you will notice there are no navigation buttons or record selectors which forms normally have by default. To remove these just go to the FORMAT TAB of the PROPERTY SHEET and change the NAVIGATION BUTTONS and RECORD SELECTORS properties to NO.

You may also notice that our dialog form has a border style specific to dialog forms. One of its features is that the user cannot resize it, thereby diminishing its impact! This is attained by changing the BORDER STYLE property to DIALOG.

One of the more subtle properties that you might want to change is that of the SCROLL BARS property. I recommend you set this to NEITHER. Apart from the fact you should not need to scroll a dialog form, Access also leaves a thick white line at the bottom of the form if this is not changed. This looks particularly bad if you choose a different Back Color for your form.

One last property alteration that I recommend, is the addition of a form Caption. This appears in the top border of the dialog form. In figure 1 I have set this to "Please Enter a Date Range". The caption does not have to be a question, generally it is just a form title. Either way you will need to choose your own caption and enter it in the CAPTION property, the first property on the FORMAT TAB.

As far as the actual function of the dialog form is concerned, this particular example runs a VBA sub when the command button is clicked. The code creates an SQL WHERE statement based on information obtained from data entered by the user on the dialog form. This is part of the code that I used:

varYearFrom = Me!cboYearFrom
varYearTo = Me!cboYearTo
varMonthFrom = Me!cboMonthFrom
varMonthTo = Me!cboMonthTo

strSQL = "(Year between " & varYearFrom & " AND " & varYearTo & ") AND (Month between " & varMonthFrom & " AND " & varMonthTo & ")"

As you can see, each combo box value is stored in a variable in the first four lines of code. These variables are then integrated into the string containing the SQL Statement. Once we have the SQL Statement stored in the string strSQL, this can be passed as the WHERE parameter of the DoCmd.OpenReport Method like this:

DoCmd.OpenReport varForm, acViewReport, , strSQL

Then, when our report opens, only records falling within the criteria set in the SQL WHERE statement are displayed. In this case these are all the records between January and December 2011 (see figure 1 above).

Manipulating Dates Using the DatePart Function

2011-09-16T11:10:00.000-07:00

I was recently asked a question on my Access 2007 Tutorial Facebook Page which involved the manipulation of dates. In my answer to this question, I suggested a solution that made use of the DatePart Function. If you are interested, I was replying to NorAzri's comment on his post of the 5th Sep 2011. However, manipulating dates in this way is quite an interesting area, so I thought I would use this blog to write a bit more about the function.

So what does DatePart actually do? Put simply, the DatePart Function allows us to isolate part of a given date. In so doing we may then go on to group or retrieve records according to the date interval set . We use the function by passing an interval parameter telling Access which part of the date we are interested in, and a second parameter which is the date itself. The syntax for the function is constructed as follows:

DatePart("Interval", DateValue)

The interval parameter is comprised of the same values as that used in the related DateDiff Function - a function I blogged about in August. These intervals may pertain to the Year ("yyyy"), the Quarter ("q"), or the month ("m") to name but three. There are ten possible interval types in total (going right down to hours, minutes, and seconds).

However, NorAzri was interested in filtering his records by month. So in order to do this we passed "m" for the interval parameter, and fldDate (a field value from the table) for the date parameter. The syntax for this was written as follows:

DatePart("m", [fldDate])

The result of the function gave us a numeric month value based on the full date. For example, if the date in fldDate had been 16/09/2011, the function would have returned the value 9. This is useful when we have a list of dates, such as that in Figure 1 below, which we want to group by month value in a query or report.

Figure 1: List of Orders with dates.

So lets take a look at how we would use the DatePart Function in a query to group these order records by month:

Figure 2

As you can see in Figure 2 above, we have created a calculated field called TheMonth using the DatePart Function. The interval, "m", has been passed in the first parameter, and the second parameter references the OrderDate field from tblOrders. We have also clicked the TOTALS icon from the SHOW/HIDE group of the DESIGN ribbon, setting TheMonth and Total fields to GROUP BY and SUM respectively.

When we run the Query, any month containing an order will be represented in the results as its own individual row. The first column then displays the Month value derived from the DatePart function in TheMonth, and the second contains the sum of order totals for the particular month in question. Figure 3 below shows the query result for our sample data:

Figure 3: Results of Query with Calculated
Field using the DatePart Function. SumOfTotal
gives us the total for each group of order records
falling within each Month.

Another excellent use of DatePart would be to filter a group of records using a criteria based on this function. For example, if we wanted to show each individual record for all orders falling within the month of May, we would construct our Query as follows:

Figure 4

As you can see, we have kept our calculated field called TheMonth. However, instead of grouping all the records from tblOrders by month, we have added the month value =5 as a criteria for this column. When we run the query, all Orders from May will be displayed.

Figure 5: The result of the second Query filters
out all orders from May.

If we wanted, we could convert this to a parameter query, and use it as the data source for a report. This was something I recommended to NorAzri who wanted to select the Month from a Combo Box on an unbound form, and then produce a report which would filter out records falling within the month in question. If you are interested in seeing the solution I suggested, just follow the link at the top of this post and look down the stream.

How to Display a Form Automatically when your Application Opens

2011-09-09T11:07:00.000-07:00

This is a quick tip on how to automatically display a form when the user opens your Access Application. In addition to improving User Friendliness, your database design will also appear much more professional. The step by step instructions below will display the Switchboard form in figure 1 immediately upon the application opening. This will give them a good start point from which to navigate your system.

Figure 1: Switchboard Form opens Automatically
when the Application Opens.

Open the Database and create a Switchboard Form similar to that in figure 1 above. Call the form frmSwitchboard.
Click the MICROSOFT OFFICE button in the top right hand corner of the Access Screen.
Click the ACCESS OPTIONS button at the bottom of the open pane. This opens the ACCESS OPTIONS dialogue form.
Select CURRENT DATABASE from the menu on the left side of the dialogue form. This displays the Options for the Current Database (see figure 2 below).
Figure 2: Options for the Current Database.
The fourth option down from the top is DISPLAY FORM. Click the Combo Box arrow and select frmSwitchboard from its drop down list,
Click OK to close the ACCESS OPTIONS form.
Close the database.

The next time you open your application, the Swichboard is displayed automatically without the user needing to select it from the Navigation Pane.

Removing Multiple Records with a Delete Query

2011-09-02T11:14:00.000-07:00

Imagine you have a contact database containing a list of suppliers. One of those suppliers proved themselves unreliable once to often, so your company decides not to use them any more. It is your job to delete any names from this list that belong to this particular supplier. The only problem is, the contact database contains hundreds of names, a number of which are from the suppliers company. This is where an Access Delete Query is going to prove very useful.

Delete Queries are a type of Action Query ie a Query which performs some sort of action on our database. In the case of a Delete Query, Access will find a group of records matching a given criteria and delete them from the database table. As such, we can make good use of a Delete Query in the scenario outlined above. By means of a Delete Query, we can find all the records of staff who work for the ex-supplier and delete them from the contact list. Lets take a look at the contact list we are going to working with (shortened for the purpose of this exercise):

Figure 1: The Contact List upon which
we shall run our Delete Query.

Lets say the name of our ex-supplier is Company 5.

So how do we create a Delete Query?

It's quite simple really. The first stage is to create an initial select query that would give us a list of all the names of staff working for Company 5. This involves entering "Company 5" as the query criteria. Once we have run the query and checked its results, we just need to change it to a Delete Query and click RUN again.

How to Create a Delete Query

Stage 1 - Creating the initial Select Query

Select the CREATE TAB of the Access Ribbon.
Click the QUERY DESIGN icon. It is located in the OTHER group of the CREATE ribbon.
Select tblContacts from the SHOW TABLE dialogue box.
Drag the asterix (*) from tblContacts down to the first column of the DESIGN GRID. This is a way of getting the query results to display all fields from the table without having to select each one individually.
Then Drag the Company field from tblContacts down to the second column of the grid. We have added this field separately because we are going to enter a criteria in this column.
Click on the CRITERIA row of the Company column, and add the criteria: "Company 5"

The Select Query has now been created. It should look like this:

Figure 2: The Select Query created in the
first stage of the Delete Query.

It is advisable to run the query at this point and check the results are correct. They should look like this:

Figure 2: The results from the Select Query.

As you can see, our select query has found four records from tblContacts matching the criteria of Company 5". Since this is the correct result for the dataset we are working with, we can move onto the second stage of the process: converting the Select Query to a Delete Query.

Stage 2 - Converting the Initial Select Query to a Delete Query

If you look at the QUERY TYPE group of the DESIGN ribbon, you will notice that the SELECT QUERY icon is highlighted orange. We need to change this to DELETE QUERY. To do this just click the DELETE QUERY icon further along the group.
Figure 3: The QUERY TYPE group of the DESIGN ribbon.
The DELETE QUERY icon is highlighted orange.
After the clicking the DELETE icon, you will notice that the row of SHOW tick boxes disappears from the DESIGN GRID, along with the row for SORT. A new row entitled DELETE has taken their place. Access has filled in the values of FROM and WHERE in the first and second columns respectively. These are SQL Keywords: the FROM keyword indicates the first column contains fields from tblContacts, and WHERE indicates the Company column contains a criteria against the data stored in this field.
Figure 4: The QUERY DESIGN GRID for our
DELETE Query. Notice the new row for DELETE
containing the SQL FROM and WHERE
Keywords.
Click RUN from the QUERY RESULTS group.
Click YES when prompted whether we want to delete the number of rows matching our query criteria. This will be four rows for the dataset we have been working with.

We can now go back and open the tblContacts table. As you can see from Figure 5 below, all Company 5 contacts have been removed by our DELETE QUERY.

Figure 5: The tblContacts table after Company 5
contacts have been removed.

Using a Crosstab Query to Present Summary Data

2011-08-26T11:32:00.000-07:00

In this post we shall use the power of an Access Crosstab Query to summarize and restructure our data into a clear and concise format. We shall illustrate how to do this with sales made by employees of a Real Estate Company (who are referred to as Estate Agents in the UK).

So in what way does a Crosstab Query improve the presentation of summary data? Well lets imagine our Real Estate Company asks us to summarize a list of sales made by each of its employees, for each quarter, over the period of a year. This list has fields for Employee, Quarter and SaleValue.

Figure 1: This is the raw data that will form
the basis of our Crosstab Query.

As you can see in Figure 1 above, this raw data provides us with all the necessary information, but is difficult to digest in its present form. What we are interested in, is the total SalesValue, for each Employee, for each Quarter. With the help of a Crosstab Query, we can restructure this information so that each Employee Name becomes a single Row Heading on the left side of the table, and each Quarter Value is grouped together to form Column Heading's across the top of the table. Since there are three employees, and four quarters in our data, this will give us a table with three rows and four columns. The Sum of the Sales value then appears where each Employee Row intersects with each Quarter Column. This is how our Crosstab Query will be structured:

	\|Quarter 1\|	Quarter 2\|	Quarter 3\|	Quarter 4\|
Employee 1	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|
Employee 2	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|
Employee 3	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|

Creating a Crosstab Query

So how do we create a Crosstab Query?

Well, there are two main ways: the first with the Crosstab Query Wizard, and the second using the Query Design Grid. We are going to focus on the second method which involves creating the Crosstab Query from Scratch. However if you do wish to use the Crosstab Query Wizard, you can select the CREATE TAB, click the QUERY WIZARD ICON from the OTHER group, highlight CROSSTAB QUERY from the list in the dialogue box, and then click OK. When the Wizard starts, follow the instructions to select the Table/Query, the field to be used as Row Headings (SalesPerson), the field to be used as Column Headings (Quarter), and the aggregate function to be used to summarize the SaleValue (Sum).

However, here is the method to create a Crosstab Query from Scratch using the Query Design Grid:

Select the CREATE TAB of the Access Ribbon.
Click the QUERY DESIGN icon. It is located in the OTHER group.
Select the table or query to be used from the SHOW TABLE dialogue box. The one I have used is called qrySales.
Click the CROSSTAB icon. This is located in the QUERY TYPE group of the DESIGN ribbon. Notice how two new rows, Crosstab and Totals, appear in the query design grid.
Drag the three field names from qrySales down onto the grid.
Go to the Crosstab row of the SalesPerson column on the QUERY DESIGN GRID. Then select Row Heading from the drop down box in that cell.
Next go to the Crosstab row of the Quarter column. Then select Column Heading from the drop down list.
As you may recall, the sum of SaleValue is going to provide the summary data in our table. To do this, go to the Totals row of the SaleValue column. Then select Sum from the drop down list. Then move down to the Crosstab row of the SaleValue column, and then select Value from the drop down list.

The QUERY DESIGN GRID should now look like this:

Figure 2: The QUERY DESIGN GRID for our Crosstab Query.

When you run the Crosstab Query our results should appear like this:

Figure 3: Results of our Crosstab Sales Query.

Its worth pointing out that we can add another row heading containing a Total SalesValue for each SalesPerson across the four quarters. To do this just go back to the grid and add an additional column for SalesValue (you might want to give the column the alias of Total). Then select Row Heading from the Crosstab Row on the grid. When run, it should look like this:

Figure 4: Crosstab Query with additional Row Heading comprised of the row Total.

Handling the Conditional: Using the IIf Function

2011-08-19T05:46:00.000-07:00

Let's imagine we have an Access table containing a list of academic exam results. There is a field for Subject and a field for the percentage Result , but we do not have one telling us whether each percentage result is deemed a Pass or Fail. So what is the best way to 'store' this missing information?

Figure 1: An Access Table containing
Subject and Result fields.

We might be tempted create such a Pass/Fail field, but this is generally considered bad database design practice: since it is possible to calculate whether the student passed or failed on the basis of the percentage result, we would be storing redundant data. Creating a Query would be a much better option. However, we need to display whether the result is a Pass or a Fail, so simply entering a criteria to filter out all results above or below a certain percentage is not going to do the job: this would only provide us with a list of Passes or a separate list of Fails. We just want one column stating Pass or Fail.

A great way of doing this task would be to use the IIf function as a new calculated column within the Query. This will enable us to display a value indicating whether the exam has been passed or failed. The IIf function allows us to specify a conditional expression (in a similar way to a query criteria), but then to go on and specify a value to be displayed based on whether the result of the expression happens to be true or false. In our case we want the expression to determine whether the value contained in a percentage result field is, say, greater than 50%, and if it is, display "PASS", or else display "FAIL".

The IIf function is constructed as follows:

IIf(Conditional Expression,  True_Condition, False_Condition)

The first parameter we pass for this function is the conditional expression. In our case this would be Result > 50. The Second parameter we pass is the string value which appears if the condition is true (for us this would be "PASS"); and the third parameter is the string value which appears if the condition is false (for us this would be "FAIL"). As such we would construct our own particular IIf function like this:

IIf([Result] > 50,  "Pass", "Fail")

We shall enter the this expression into our query as follows:

Figure 2: The IIf Function has been entered into the third column using the alias Pass.

As you can see we have entered our IIf function in the last column on the right. We have used the alias "Pass" to describe the data to be displayed in this column. (For more information about alias's and using functions in Access Queries, please see this explanation in relation to the DateDiff Function). Just remember to separate the alias name from the function with a colon as shown in Figure 2 above.

Now lets run the query and see what we get:

Figure 3: the results of our query to calculate whether
a student has passed or failed an exam.

As you can see, the IIf function has correctly assigned a Pass or Fail based on the percentage Result attained.

	\|Quarter 1\|	Quarter 2\|	Quarter 3\|	Quarter 4\|
Employee 1	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|
Employee 2	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|
Employee 3	\|Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|	Sum of SaleVale\|