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.