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

Using the DLookUp function

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

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

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

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

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

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

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

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

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

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

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

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