Before Update Performing Data Validation

The BeforeUpdate property is triggered before a change or entry to a form or control is committed. This step allows for intervention between the time the user sets the value and when the value is actually saved.

In the following example, a sample of performing data validation is demonstrated. Data validation is the process of ensuring that the value entered by the user is logical and/or does not validate other business rule logic.

Private Sub txtEndDate_BeforeUpdate(Cancel As Integer) 'Len returns the length of the values entered in the control 'i.e. Len("Abcdef") = 6

'If one of them is zero, then the product will be zero If Len(txtEndDate) * Len(txtStartDate) > 0 Then

'Verify that the Start Date is before the End Date

If txtEndDate > txtStartDate Then

'Alert the user and Cancel the update

Cancel = MsgBox("Start Date must be before the End Date",

vbInformation, "Data Validation Failure")

End If

End If

This less than orthodox example starts with verification to ensure that both the Start Date and the End Date have been entered. (Unless you enforce the order in which fields are entered, the user may enter information in ways you've never imagined.) So, you would not want the error message to appear if the user had not entered the Start Date. Thus taking the length of both fields and multiplying them together will either result in 0 or a value greater than 0. When greater than 0, it means that both fields have a value.

It is assumed for this example that the format of the date entry is handled somewhere else. One method would be to use the InputMask. Assuming that if there is an entry, it will be a date, then all that remains to validate is whether or not the Start Date value is less than the End Date value.

Finally, if the validation fails, then the error message is displayed using the MsgBox function, and the Cancel parameter is set at the same time. The trick utilized here plays on the fact that the MsgBox function always returns a value greater than 0. In Access, 0 is the same as False, but any other value is considered to be True. So, combining the setting of the Cancel parameter and the execution of the MsgBox makes for clean, concise VBA code.

If the Cancel parameter does get set to True, then the BeforeUpdate event property is not finished, which prevents the save of the End Date.

0 0


  • Leda
    How to set validation access form before update?
    7 years ago

Post a comment