Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Posts
    36

    Angry Unanswered: Error Trapping and Handling

    I know this seems really simple, but I can't seem to code this right. I need to stop a database update to a table if the users haven't filled in all the required fields. In the BeforeUpdate event, I have tried the following code:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo Err_Form_BeforeUpdate

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer

    If Me.Event Is Null Or Me.Event_Date Is Null Or Me.Staff Is Null Then
    MsgBox "Event, Date and Staff fields must be Entered"
    Cancel = True
    End If

    Exit_Form_BeforeUpdate:
    Exit Sub

    Err_Form_BeforeUpdate:

    ' Me!ProductName.Undo
    Resume Exit_Form_BeforeUpdate

    End Sub


    Though I see haven't filled in the Staff field, I don't get the message box or the cancel. The record is added to the table.

    I have also tried:

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    On Error GoTo Err_Form_BeforeUpdate

    Dim strMsg As String, strTitle As String
    Dim intStyle As Integer

    MsgBox ("Event " & Me.EventID & "Program " & Me.Program _
    & "Volunteer " & Me.Volunteer & "Date " & Me.Date)

    If Me.Match_Event Is Null Or Me.Program Is Null Or Me.Volunteer Is Null _
    Or Me.Date Is Null Then
    Err.Raise 0
    End If

    Exit_Form_BeforeUpdate:
    Exit Sub

    Err_Form_BeforeUpdate:
    MsgBox "Event, Program, Volunteer and Date fields must be Entered"
    Cancel = True
    ' Me!ProductName.Undo

    Resume Exit_Form_BeforeUpdate

    End Sub


    In which case I ALWAYS get the error message and Cancel, even when the fields are not null.

    HELP

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    try IsNull(Me.Staff) or Nz(Me.Staff) = ""

  3. #3
    Join Date
    Apr 2003
    Posts
    36
    Thanks, but what's the difference. I also have a hard time understanding when to use the 'Required' property of the field in the table editor, the 'Required' property of the field in the form, code checking for nulls on the OnExit event of the field or code checking for nulls on the BeforeUpdate of the record. What's the difference? Is one way better than another?

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Check out access vba help on the "Nz" function and the "IsNull" fucntion. I can't say it better than the help does. It does give give a comparison between the two. I like to use the Nz fucntion - this will trap Nulls and empty strings - but there is a time and place for each of these functions.

    >I also have a hard time understanding when to use the 'Required' property of the field in the table editor

    Depends on you needs - for example LastName, SS#, Address1 would most likely be required but Address2 would not since not everyone has a second address. If you must have the data for whatever reason then set it to required in the table. You will then trap for the missing data in the form that sits on top of the table to provide a user friendly warning message. You should always check on the form's BeforeUpdate - the user may not have never entered the control, so the OnExit Event would never fire. This is not to say that you should not use both. You may want to loop through the controls in the forms even and provide a generic warning and a control specific waning for the control's event.

    Hope this help - Matt

  5. #5
    Join Date
    Apr 2003
    Posts
    36
    Thank you Matt,

    I did set the required field on the table column as my first resort. But, when I entered just info into FIELD1, but not FIELD2 or FIELD3, and then went to the next record, Access just happily added the record to the table. I'm not sure if it's putting in zeros by default and that's allowing the records to pass the 'required' test. So, I eventually put the code into the BeforeUpdate event as you suggested. That seemed to work fine when I used the Nz function. Though I still don't understand why saying

    If Me.field1 IS NULL OR Me.field1 = 0 Then
    MsgBox("blah")

    didn't work. If I separated them into two separte if statements that worked. Or if I used the NZ (which I guess combines the two statements) that worked. But why not the original syntax.

    I'm still befuddled, though at least the form is semi-functional.

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Is operator in VBA compares two object reference variable.

    result = object1 Is object2

    When you say [ControlX] Is Null - Access throws an "object required" error since Null is not an object.

    I think that you're confusing the use of 'Is Null' in SQL with 'IsNull' in VBA.

  7. #7
    Join Date
    Apr 2003
    Posts
    36
    Hi all,

    So still having a problem with this. The code for the message box now works, but the data is saved anyway. I don't know how to prevent an update to the data here. I've looked at the CancelEvent, CancelUpdate, Cancel=True, etc. and can't get anywhere.

    It seems that it should be so simple, but in the BeforeUpdate event, if I vet the data and find a problem, what's the best way to cancel an update to the data?

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Event is a keyword in VBA. Try using Me![Event] instead
    of Me.Event.

Posting Permissions

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