Results 1 to 4 of 4
  1. #1
    huv123 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    6

    Validation BeforeUpdate Errors

    I have a fairly simple form, with a number of field in it including a combo box called "Status" which has two options (Resolved and Active) and a text field called "Resolved Date".
    Resolved Date only needs to be filled in when Status = "Resolved".

    I also have a cancel button (which allows the user to leave without saving changes) and a close button (which saves all changes and closes the form). The original code for the close button was:

    CODE
    Private Sub cmdSaveRecord_Click()
    On Error GoTo Err_cmdSaveRecord_Click

    If Me.Dirty Then Me.Dirty = False

    DoCmd.Close

    Exit_cmdSaveRecord_Click:
    Exit Sub

    Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click

    End Sub


    I wanted to put in place validatin rule so that a person could not leave the form after changing the status to resolved without entering a resolved date.

    So I put in a Before Update event which looked like:

    CODE
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.DateResolved) And Me.Status = "Resolved" Then
    MsgBox "You must enter a resolved date"


    Cancel = True
    End If
    End Sub




    However when I was testing the validation and clicked the close button when Status = Resolved, but Resolved Date was empty, it would throw an error message "The setting you entered for this property isnt valid".

    I googled around a bit and found someone with the same problem. He identified the me.dirty command as causing the issue. So I changed the close button code to:

    CODE
    Private Sub cmdSaveRecord_Click()
    On Error GoTo Err_cmdSaveRecord_Click

    If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close
    Exit_cmdSaveRecord_Click:
    Exit Sub

    Err_cmdSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveRecord_Click

    End Sub


    Now when I test the validation rule, the error message about not being valid is now hone, but now immediately after the error dialogue box pops up telling me to fill out the resolved date, I get a second dialogue box telling me the Run command was cancelled (as per the instructions cancel = false).

    Is there any way I can stop this second dialogue box popping up? I freely admit to muddling through this - its been years since I have used VBA and cant remember much. I was hoping maybe someone had an idea of what I can do.

  2. #2
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    I'm not too well versed in the "me.dirty" function but what if you took this approach?

    This would be on the cmdSaveRecord_Click()
    Code:
    if cboResolved = "Resolved" and IsNull(txtResolvedDate) then
         msgbox "You must enter resolved date!", vbCritical
         txtResolvedDate.SetFocus
    else
         DoCmd.Close
    end if
    Not sure if this is what you're looking for but this should do what you are trying to do.

  3. #3
    huv123 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Posts
    6
    Thank you very much. I was able to work with this

  4. #4
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    Great! Please mark thread as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 07-25-2011, 12:07 PM
  2. #Name? errors with textboxes
    By jasonbarnes in forum Forms
    Replies: 3
    Last Post: 12-16-2010, 09:51 AM
  3. Accessing Table via asp Errors
    By KLynch0803 in forum Programming
    Replies: 1
    Last Post: 01-17-2010, 09:59 AM
  4. .ocx Files Errors
    By darshita in forum Import/Export Data
    Replies: 9
    Last Post: 12-07-2009, 07:36 AM
  5. BeforeUpdate and AfterUpdate Question
    By gsurfdude in forum Programming
    Replies: 0
    Last Post: 04-03-2008, 07:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums