Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    24

    Answered: Cancel Close form event based on data validation

    Hello,

    I have a database for associates to review files, it is pre-populated with available data and then the associate goes in and picks a file and fills out the questions. I have a ReviewerID field that I want to base my reporting queries off of, so I can run a report for example to get all files from July that have been reviewed. This part is easy, I run a with condition for ReviewerID field is not null. The problem I am having is some of the associate for some reason keep leaving reviewerID blank so the file don't show in the query, I thought of using a different field but not all question need to be answered for the file to be reviewed. On the Before Update event I built data validation that says if (any of the questions have been answered, and the ReviewerID field is null) then set focus back to ReviewerID and Msg box saying data is required. This works great for trying to switch to a new record, but not when closing the form. This is where my problem is, I have a Save and Close button with the following code

    Code:
    Private Sub Command42_Click()
    On Error GoTo Err_Handler
    
      DoCmd.Close
    
      Exit Sub
     
    Err_Handler:
      If Err.Number = 2001 Then
        'Don't do anything, since getting this error just means the form validation failed
     
       DoCmd.CancelEvent
    
      Else
        'Unexpected Error
        MsgBox "An unexpected error occured" & vbNewLine & Err.Number & " - " & Err.Description
      Exit Sub
      End If
    End Sub
    This codes seem to partly work, it keeps the error from showing and my data validation message shows, but then it closes the form anyway. I want them to get the data validation message and be redirected back to correct this, if they go through and fill out all the question and hit the Save and Close button, with the current code it shows the message and closes without saving, so they would lose all data previously entered. How can I stop it from closing? I also tried Cancel = True instead of DoCmd.CancelEvent and same result.

    Thanks in advance for anyhelp

  2. Best Answer
    Posted by Missinglinq

    "You just need to add one line of code! It has long been recommended that the code

    DoCmd.RunCommand acCmdSaveRecord

    or

    If Me.Dirty Then Me.Dirty = False

    be inserted before using

    DoCmd.Close

    to close a Form because of a quirk in Access. When DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field has been duplicated, a PK field or other Required Field has been left blank or validation rules have been violated!

    If one of these things occur, Access will simply dump the Record, Close the Form, and not tell the user that the Record has been dumped!

    The code If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord forces Access to attempt to Save the Record, and your validation code in the Form_BeforeUpdate event will kick in, allowing correction to be made before Closing the Form.

    You need to change your

    DoCmd.Close

    to

    If Me.Dirty Then Me.Dirty = False

    DoCmd.Close

    or

    DoCmd.RunCommand acCmdSaveRecord

    DoCmd.Close

    Linq ;0)>"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    dont cancel the event,
    validate the data then either, close or stay open.
    usage:

    Code:
    Private Sub Command42_Click()
    if IsValidForm() then   'do stuff
        docmd.close
    else
        'user must correct entries
    end if
    end sub
    
    
    '---------------
    public Function IsValidForm() as boolean
    '---------------
    dim vMsg
    
      select case true
           case txtName = "" 
              vMsg = "Client Name is missing"
    
           case isnull(cboState )
              vMsg = "State is missing"
    	  cboState.setfocus
    
      end select
      if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
      IsValidForm =vMsg =""
    end sub

  4. #3
    Join Date
    Apr 2011
    Posts
    24
    I appreciate the assistance, I think I am getting closer, but I am quite the vba novice, so I am sure I am missing somehting in the code you provided that I need to change to suit my fields. When I run your code exactly I get the "Client Name is missing" msg box not matter what I put, or don't put, in the ReviewerID field. Assuming that is because I have no field named txtName, I changed this to include a piece of my validation to say if ReviewerID = "" and APUDocd <> "", APUDocd is the name of one of the fields they can answer. If I have data in the ReviewerID field it saves and closes. But if I leave the ReviewerID field blank and have data to the APUDocd field or If I make no changes to the APUDocd field and no changes to the ReviewerID field it , a desired effect I get

    Run-rime error '94'
    Invalid use of Null

    Any suggestions as to what I am doing wrong. Below is the code with my change

    Code:
    Private Sub Command42_Click()
    If IsValidForm() Then   'do stuff
        DoCmd.Close
    Else
        'user must correct entries
    End If
    End Sub
    
    
    '---------------
    Public Function IsValidForm() As Boolean
    '---------------
    Dim vMsg
    
      Select Case True
           Case ReviewerID = "" And APUDocd <> ""
           vMsg = "Client Name is missing"
    
           Case IsNull(cboState)
              vMsg = "State is missing"
          cboState.SetFocus
    
      End Select
      If vMsg <> "" Then MsgBox vMsg, vbCritical, "Required"
      IsValidForm = vMsg = ""
    End Function

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You just need to add one line of code! It has long been recommended that the code

    DoCmd.RunCommand acCmdSaveRecord

    or

    If Me.Dirty Then Me.Dirty = False

    be inserted before using

    DoCmd.Close

    to close a Form because of a quirk in Access. When DoCmd.Close is used, Access closes the Form regardless of whether or not a PK field has been duplicated, a PK field or other Required Field has been left blank or validation rules have been violated!

    If one of these things occur, Access will simply dump the Record, Close the Form, and not tell the user that the Record has been dumped!

    The code If Me.Dirty Then Me.Dirty = False or DoCmd.RunCommand acCmdSaveRecord forces Access to attempt to Save the Record, and your validation code in the Form_BeforeUpdate event will kick in, allowing correction to be made before Closing the Form.

    You need to change your

    DoCmd.Close

    to

    If Me.Dirty Then Me.Dirty = False

    DoCmd.Close

    or

    DoCmd.RunCommand acCmdSaveRecord

    DoCmd.Close

    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

  6. #5
    Join Date
    Apr 2011
    Posts
    24
    Missinglinq, thank you so much , that worked perfectly. I figured I was just missing something, as usual lol. Thanks again.

  7. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    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
  •