Question: What's the best event(s) for trapping nonsense data?

[Monterey_Manzer]

HI Everyone,

This is more of an informal poll than a specific problem. The primary dBase I manage is a system to track a series of administrative tasks and I am trying to prevent the users from being able to enter data that does not fit with the current administrative policies of the office. Since this is based on fairly continuously shifting policies, data that would now be considered "nonsense" is perfectly legitimate in historical records. This means I can't simply use restrictions on the data tables themselves to prevent bad data entry. Also, what constitutes nonsense data for one field is dependent on other fields that may not even be part of a particular form. While I have code in many a BeforeUpdate event, I am looking for a good event or set of events for my forms that will prevent the user from leaving that record (either by navigating to a new one or closing the form) without correcting the bad data.

I am also considering moving to entirely unbound forms which use custom class objects to manipulate records. This would allow me to build in more comprehensive bad data screening but I am still learning about building class modules so I'm not sure how that would be applied. If anyone has experience taking that approach and can tell me whether they think that or a simple Snapshot recordset makes more sense, I would appreciate some opinions on that as well.

Thanks!

[ItsMe]

I use the following snippit to validate data. It depends on a UDF in a Standard Module to check for Nulls, 0, "", and False. I use the "Tag" property of controls to group them for specific types of validation. You can get creative and cause the control forecolor to go red like a web form might, etc.

'Constant                Control
'acBoundObjectFrame      Bound object frame
'acCheckBox              Check box
'acComboBox              Combo box
'acCommandButton         Command button
'acCustomControl         ActiveX (custom) control
'acImage Image
'acLabel Label
'acLine Line
'acListBox               List box
'acObjectFrame           Unbound object frame or Chart
'acOptionButton          Option button
'acOptionGroup           Option group
'acPage Page
'acPageBreak             Page break
'acRectangle             Rectangle
'acSubform               SubForm / SubReport
'acTabCtl                Tab
'acTextBox               Text box
'acToggleButton          Toggle button Toggle button
'
'

'Make sure the required fields have a value
Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.Tag = "Required" Then
    
            If IsNothing(ctl.Value) Then
    
                MsgBox "Please complete the form before submitting.", vbInformation, "Incomplete!"
                ctl.SetFocus
                
                    If ctl.ControlType = acComboBox Then
                        ctl.Dropdown
                    End If
                
            Exit Sub
            End If
    
    End If
Next

[Monterey_Manzer]

Hi ItsMe,

Thanks for the insightful code. However, I already understand how to do the validation necessary and in my case it's much more of a pain in the backside than simple zero length strings and Null values. What I am asking about is if people have favorite Form events in which to place their error checking. Since my bad data issues are not necessarily with any specific data value but with combinations of values in different text and combo boxes, I will need something more all encompassing that simply a BeforeUpdate or AfterUpdate. I would like to alert the user to the nonsensical data when they attempt to navigate away from the record they are editing. It would be great if this event would also prevent the user from closing the Access application entirely. Any suggestions?

[ItsMe]

It seems I do not share the same frustrations validating data as you do.

[ipisors]

I would like to alert the user to the nonsensical data when they attempt to navigate away from the record they are editing. It would be great if this event would also prevent the user from closing the Access application entirely

No, you would still use the Form's BeforeUpdate event. There is no mystery to it. It fires prior to a record being updated. Which is all you need to validate data. No matter what the type of validation.

If you'd prefer to alert the user sooner, you can use the Exit event of controls and/or the AfterUpdate evenf of controls.

Judging from your description that your validation depends on a comparison of many controls & their values, it would seem that no one but you would know at what point those comparisons become possible.

Either way, write a private boolean function and store it in the form's module to return False or True based on all your business rules. Put it in the form's BeforeUpdate event. If you code it correctly, you can cancel the update whether the update is firing because they are navigating away from the record, closing Access, closing your application, closing the form, or anything else.