Using the LIKE Operator and Wildcards to Match Patterns Between Strings

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

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:

1. 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.   
2. 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.
3. 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.
4. 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.  
5. 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.
6. Click OK to close the dialog box.
7. 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.
8. 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.

9. 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. 
10. 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.
11. 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.
12. 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

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

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

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

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

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

1. 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.
2. 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.
   
3. 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. 
4. 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

1. Select the HOME ribbon, and then pick DESIGN VIEW from the VIEWS group.
2. 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.
3. Click ADD A GROUP from the GROUP, SORT AND TOTALS pane (see figure 3 below).
4. Then select the Company field from the list which appears.  This creates a Company Header (see figure 4 below).
5. Highlight the Company text box and make sure no other controls are highlighted.
6. Cut and past the Company text box so that it is positioned close to the left margin within the Company Header.
7. Reposition the labels and text boxes so that they are aligned in a neat logical fashion (see figure 4 below).
8. Next click ADD A SORT in the GROUP, SORT AND TOTALS pane.  
9. 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.
10. Click ADD A SORT again.
11. 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

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

1. Create an unbound switchboard form called frmSwitchBoard.
2. Add three Command Buttons called ctlAdd, ctlEdit, and ctlRead.
3. 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

1. Create a new form called frmContacts
2. Set the forms RECORD SOURCE property to tblContacts.
3. Add the four fields of tblContacts to the form
4. 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

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.

1. Create a new unbound form in Design View.
2. Add a Command Button.
3. 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):
4. 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.

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:

1. Create a new unbound Access Form.
2. Add two text boxes called txtFirstNumber and txtSecondNumber.
3. Add a Command Button called ctlCompare.
4. Select the Command Button and open the PROPERTIES SHEET.
5. Click the Events tab of the PROPERTY SHEET.
6. Click the three dots symbol  (...) on the right of the ON CLICK property cell.
7. Open the VBA Editor by clicking CODE BUILDER from the CHOOSE BUILDER menu.
8. Copy and paste the code listed below (excluding the first and last line which should have been added to the VBA editor automatically).
9. 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.