Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Unanswered: validation question

    Hi Folks,
    I've got a simple form, with 2 fields whose record source is a table.
    I wanted to learn about how validation is done, so I put in some checks in the before update event, so far so good - when I try and tab into a new record without completing the previous one I get my msgbox warnings.

    My problem starts when I put in a button to close the form (bringing me back to the switchboard) - this seems to do the validation, pop up the error message if a record is incomplete ...BUT still close the form.

    This is what I'm doing in the before update event:
    <snip>

    If (rate.Value <= 0) Or (Len(rate.Value) < 1) Or (IsNull(rate.Value)) Then
    MsgBox ("Enter a value great than 0 for the Rate")
    Cancel = True
    rate.SetFocus
    End If

    <snip>
    Shouldn't the Cancel = True part stop the form from closing if that condition is met? How can I stop the form from closing?

    The code for the close button is simply:
    DoCmd.Close

    thanks! (I'm using access 2000 and winxp)
    Robin.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Change your Cancel = True to DoCmd.CancelEvent and put it before your Msgbox

    If (rate.Value <= 0) Or (Len(rate.Value) < 1) Or (IsNull(rate.Value)) Then
    DoCmd.CancelEvent
    MsgBox ("Enter a value great than 0 for the Rate")
    rate.SetFocus
    End If

  3. #3
    Join Date
    Aug 2007
    Posts
    4
    Hi there,
    cheers for the quick reply!

    I've done as you suggested, but I'm getting the same result, the beforeupdate event is fired, the missing form fields are detected, the message box pops up, but when I click ok on it the form still closes.

    So these are the offending subs, maybe it's something to do with the close sub?:



    <snip>
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim errMsg As String

    errState = False
    errMsg = "Please enter a value for : " & vbCrLf

    If IsNull(description.Value) Or (Len(description.Value) < 1) Then
    DoCmd.CancelEvent
    MsgBox (errMsg & "Description")
    description.SetFocus
    errState = True
    End If

    If (rate.Value <= 0) Or (Len(rate.Value) < 1) Or (IsNull(rate.Value)) Then
    DoCmd.CancelEvent
    MsgBox ("Enter a value great than 0 for the Rate")
    rate.SetFocus
    errState = True
    End If

    End Sub

    Private Sub goToMain_Click()
    On Error GoTo Err_goToMain_Click

    DoCmd.Close

    Exit_goToMain_Click:
    Exit Sub

    Err_goToMain_Click:
    MsgBox Err.description
    Resume Exit_goToMain_Click

    End Sub

    <snip>

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Put the coding that is in Form_BeforeUpdate in a new event called goToMain_GotFocus then delete the Form_BeforeUpdate event.

    Personally I would put all the validation in the goToMain_click event

    Private Sub goToMain_Click()
    On Error GoTo Err_goToMain_Click

    dim error_flag as integer

    error_flag=0

    Dim errMsg As String

    errState = False
    errMsg = "Please enter a value for : " & vbCrLf

    If IsNull(description.Value) Or (Len(description.Value) < 1) Then
    error_flag =1
    End If

    If (rate.Value <= 0) Or (Len(rate.Value) < 1) Or (IsNull(rate.Value)) Then
    error_flag=2
    End If

    select case error_flag
    case 0
    DoCmd.Close

    case 1
    MsgBox (errMsg & "Description")
    description.SetFocus

    case 2
    MsgBox ("Enter a value great than 0 for the Rate")
    rate.SetFocus
    end select

    Exit_goToMain_Click:
    Exit Sub

    Err_goToMain_Click:
    MsgBox Err.description
    Resume Exit_goToMain_Click

    End Sub

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    When DoCmd.Close is used to close a form, Access gets lazy! This is particularly bad since the command Button wizard uses this code behind the form close buttons it produces! If you have a record that has failed a validation test, and you're using DoCmd.Close to close your form, Access basically ignores the fact that the validation test has failed, dumps the record without any further warning, and closes the form!

    The answer is to use this code just prior to DoCmd.Close.

    If Me.Dirty Then Me.Dirty = False

    This essentially makes Access defer to the validation rules.

    I understand that in ACC2007, the command button wizard has added this bit of code behind the close buttons it produces. The fact that this has been done is causing new problems now. If there is no Record Source associated with the form, i.e. the form is unbound, an error is thrown. Unbound forms have no Dirty property associated with them.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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