Jeremy Burns Bronze Collection
If you have controls that should have properties set depending upon data held within other controls on the form, it's tempting to use the form's Current event. However, the Current event can be triggered more than once for a record and, if the rules for changing the properties are complex, can take a long to time to execute. In addition, the AfterUpdate event of the form or controls on the form has to be coordinated to react to data changes. This can be involved and difficult to manage.
One solution is to place a hidden control on the form whose control source is a function. The function contains the rules to set control properties and to validate data entry. The function must accept as arguments values in all the fields that might trigger a validation or property change. The result is that, whenever one of the passed fields changes, the function is triggered. The function is also triggered when a record becomes current.
As an example, consider a field called txt_DisplayFields set up with a ControlSource of blnDisplayFields([grp_Sale_Type], [grp_Tax_Exempt]). In this example, two option group values are passed to the function. They determine whether the current record is a sale and whether the transaction is tax exempt. If it's a sale, the delivery address field is enabled, and the forecolor of txt_Sale_Type is set to red. If it's not a sale, the delivery address field is disabled, and the forecolor of txt_Sale_Type is set to black. If it's tax exempt, the tax amount field is disabled; otherwise, the field is enabled. If the field doesn't contain a value, a message box asks the user to enter a value. The function looks like this:
Private Function blnDisplayFields _
(blnIsSale as Boolean, _
blnTaxExempt as Boolean) as Boolean
Me!txt_Delivery_Address.Enabled = blnIsSale
'Set forecolor of txt_Sale_Type to red if a sale
'True ; 255 * -1 = -255 : ABS(-255) = 255 [Red]
'False; 255 * 0 = 0 : ABS(0) = 0 [Black]
Me!txt_Sale_Type.ForeColor = ABS(blnIsSale * 255)
'Disable cur_Tax_Amount if transaction tax exempt
Me!cur_Tax_Amount.Enabled = Not blnTaxExempt
'Perform a simple validation
If IsNull(Me!cur_Tax_Amount) AND _
Not blnTaxExempt Then
MsgBox "Please enter a tax amount."