Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Trapping form errors (2000)

    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?

    <pre>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
    </pre>


    Edited to eliminate horizontal scrolling--Charlotte

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping form errors (2000)

    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.
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Trapping form errors (2000)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Wendell

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trapping form errors (2000)

    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! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Trapping form errors (2000)

    I saw this post and thought that I could add my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>.

    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
    <font face="Georgia">
    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
    </font face=georgia>

    Hope that this gives you some more ideas on it <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Trapping form errors (2000)

    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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    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.
    Wendell

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Trapping form errors (2000)

    I know exactly how my users will behave - in whatever manner I don't want them to! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    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..... <img src=/S/devil.gif border=0 alt=devil width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping form errors (2000)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> 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. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Charlotte

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Trapping form errors (2000)

    There's a big problem that OnError occurs before BeforeUpdate. So the only ways I can trap a required field are:
    <UL><LI>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
    <LI>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
    <LI>Add BeforeUpdate to all the required fields. But as pointed out, this doesn't help if they never enter the field.
    <LI>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:
    <UL><LI>Have the field's required property true (to protect against updates outside of this form), and
    <LI>Have BeforeUpdate on every required field (to check if they delete the contents of an existing field), and
    <LI>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
    <LI>Have OnError trap the updates as well, to trap fields on a new record that is never entered, and
    <LI>Maybe I need to trap BeforeInsert as well?[/list]Sure sounds like a lot of work. Am I missing something terribly?

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Trapping form errors (2000)

    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.
    Charlotte

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Trapping form errors (2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •