Trapping form errors (2000)

Topic

New Reply

I have a form over a table. The table has a number of fields that are required, i.e. cannot be null. I want to log any failures, by trapping the Form_Error event. But I can’t figure out *which* field out of 20 or so were left null. I’ve tried using AccessError, but it doesn’t tell me the field name. I can’t test the fields in the form, as they are previous (before update) values.
Any ideas?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    'DataErr is the ADO (Jet Engine) error number. This is different 
   'to the Err object
    'and Err.Description, which is for Visual Basic errors.
    'The text for the DataErr error can be obtained via AccessError(DataErr)
    'Some common errors are:
        '2113 Invalid field entry
        '2169 Record can't be saved at this time
        '3022 Duplicate key (index already exists)
        '3200 Can't delete because of a related record
        '3314 Field cannot contain Null
    
   If DataErr = 3314 Then
        'Some field was left empty. Let's find out which one.
        'Could be: Status, Officer
        Dim sFieldName As String
        sFieldName = "(unknown)"
        If IsNull(Me!cboStatus) Then sFieldName = "Status"
        If IsNull(Me!cmbOfficer) Then sFieldName = "Officer"
        'The above tests don't work - it picks up the old values in the 
        'fields, not the changed or possibly made null values

        'AccessError(DataErr) reports "The field '|' cannot contain null ...
        'How do I decrypt the field name from that?
        'Or where else can I obtain the field name?
        LogEvent "E24", "Field " & sFieldName & " cannot be null", _
            "For Item Number [" & Me!ItemNumber & "]"
        'Leave Response as is - access will display its own message box
    End If
End Sub

Edited to eliminate horizontal scrolling–Charlotte

Reply | Quote

Replies

You’re trying to do the wrong thing at the wrong time. Forget the OnError event of the form, that isn’t what you want.

Use the BeforeUpdate event of the form and create code to loop through each of the controls you want to check. You can use For Each to loop through the form’s controls collection and examine each control to see if it is one of those you want to require filled in.

For each control, store the name of the control to a string variable and its value to a variant. If the value is null, you immediately know which control has a problem, you can cancel the form’s update event (which leaves all the data in place but doesn’t save any changes yet), and you can set the focus to the control you want them to fill in. Each time before the record can be saved, the beforeupdate event will test the values of all the fields you specified and if any of them is null, it will stop the update and land on that field.

Reply | Quote

Another suggestion – it’s generally better to prompt a user when they leave a combo box that something is wrong (i.e. no data) than to wait until they have entered everything and then say OOPS! In your situation I would look at putting vallidation rules on the controls where data must be entered. That way your user gets immediate feedback when they make an error. And it avoids writing a bunch of code – I come from the school that says if you can avoid doing code that’s a good thing!

Reply | Quote

Hi Wendell,
Do you not still need the form validation code for those instances where users never actually enter a data control? On the rare occasions that I let other people use my databases, they seem to have this habit of clicking on the controls they want to fill in rather than tabbing through in the order I’d like them to!

Reply | Quote

Good point – I have observed the same behavior. It seems one mouse click is more attractive than hitting the tab key ‘n’ number of times. The discipline of form design is part science, part black magic, and lots of intuition about how your user is going to behave.

One other thought is that you probably want the table design to have allowing nulls turned off, which will also give you an error message when they try to save the results from the form. But looping through the controls in code is a more informative way to give the user an error message.

Reply | Quote

I know exactly how my users will behave – in whatever manner I don’t want them to!
At my last company I was asked to add some validation routines to a database they were using. I included a counter within some of the routines to monitor how many times they attempted to enter incorrect data into the same control. The error messages became increasingly, um, rude. I was quite impressed that it took almost two weeks before someone rang back to the main office to ask why the database was swearing at him…..

Reply | Quote

I’ll have to try that sometime. In my experience, you can’t count on users to do anything the way you want them to or that seems logical. That’s why I usually build a validation routine that works in several ways and from different places.

For instance, I can pass it an optional control name and use a select case to only validate that control when the routine is called from the BeforeUpdate event of the control. Or I can call it in the BeforeUpdate event of the form with no arguments to tell it to evaluate all the controls to see if any required controls are not filled are filled in incorrectly. You could even use static variables in the routine to hold flags for specific controls so that you skipped those if they’d already been validated. I can get endlessly creative just to save myself having to write multiple routines to handle the same kind of problem.

Reply | Quote

There’s a big problem that OnError occurs before BeforeUpdate. So the only ways I can trap a required field are:

• Have the field not required, but force requirement via code in the BeforeUpdate event. This is patently poor, as if the record is updated outside of this form, it may not be updated correctly, or if they never enter the field it’s BeforeUpdate is not fired. Or
• Have the field required, and trap omissions in the OnError. Problem with this is that all the fields properties show as their old value, so if someone deletes the contents of a field in an existing record, you can’t tell which field is in error. Or
• Add BeforeUpdate to all the required fields. But as pointed out, this doesn’t help if they never enter the field.
• A combination of step 2 and 3 above. But for a new record, I still can’t tell which field was omitted.
  [/list]Seems to me there are a variety of issues here. To correctly validate and give the most helpful messages, I need:
  • Have the field’s required property true (to protect against updates outside of this form), and
  • Have BeforeUpdate on every required field (to check if they delete the contents of an existing field), and
  • Have BeforeUpdate on the form (to check on changes to existing data when any field on the form is updated). This should not be necessary if the existing data is already valid, and
  • Have OnError trap the updates as well, to trap fields on a new record that is never entered, and
  • Maybe I need to trap BeforeInsert as well?
    [/list]Sure sounds like a lot of work. Am I missing something terribly?

Reply | Quote

Why would your table be updated outside the form? If you’re letting your users see and work with the tables, then you have built your own gibbet and will assuredly swing from it sooner or later.

If you call a routine that tests all the fields for content, you can, indeed, tell which ones were not filled in, even in a new record and even if they were never visited. You didn’t make it clear where the OnError event is being triggered or by what, but I assume it’s because of a required field not being filled in. I rarely use fields that are required at the table level because I prefer to require them in the interface where I can actually control what goes on, but there’s nothing to stop you from using the Form’s OnError event to call a validation routine if you feel you must have required fields in the table.

Reply | Quote

I agree, Access needs to be setup so that users cannot go anywhere near a table.
But in Codd’s 10 rules for relational databases, one of the rules is that a table’s integrity is the responsibility of the table, not of the interface. Otherwise, even a programmer or DBA could make a mistake, update the table outside of the interface (form) and leave the data invalid.
I guess it’s a situation where Access does things it’s own way. Even if it’s not the best way!
Thanks for your help and enlightened discussion.
Pete

Reply | Quote

I saw this post and thought that I could add my .

I always like to use a slightly different background colour for controls that are ‘required’ (ie. a pale blue or yellow). This indicates to the user that the field is required, and they know this instinctively from the colour of the field (not by trial and error).

Second, I accept Wendell’s point about checking each field, but with lots of ‘required’ fields this can become tiresome and easy to miss (just call me lazy).

I like to use the ‘tag’ property of controls to indicate those controls that have ‘required’ data. I usually set the tag property for controls to 1 for required and 0 for optional (or sometimes leave it blank).

During the beforeupdate event of the form, I just run code that loops through all controls, checks to see if the first character of the tag control is 1, if it is, it then checks to make sure that the field has a value. If the field does not have a value then the code sets a boolean (yes/no) variable to TRUE and changes the background colour of the control (so that the user can easily see the field/s that they were were meant to populate.

An example of the code would look like this

Dim ctl as Control
Dim blnFieldBlank as Boolean

blnFieldBlank = FALSE
For Each ctl In Me.Controls
if left(ctl.tag,1) = “1” then
if isnull(ctl.value) then
blnFieldBlank = TRUE
ctl.backcolor = your highlight colour code
Else
ctl.backcolor = your required colour code
End If
End If
Next ctl

if blnFieldBlank then
msgbox “You have not entered data into required fields. ” & vbcrlf & _
“These fields are highlighted.”, vbInformation, “Required Data Not Entered”
Cancel = TRUE
End If

Hope that this gives you some more ideas on it

Reply | Quote