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 ThenForms!frmcustomer!ctlDeleteOrder.Enabled = FalseElseForms!frmcustomer!ctlDeleteOrder.Enabled = TrueEnd 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
