Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    14

    Question Unanswered: Access Data Entry Form - cancel button not working

    I have a data entry form with lots of required fields.
    On moving to the form from the "main menu", I can't get out of the form. I want a button that if the user changes their mind, they can click the button and get back to the main menu (even if they've started data entry and have changed their mind!).
    I've tried this code behind a button and the prompt comes up - I click yes and then I get my error saying you must type into the required fields.. please help, its sooo frustrating!
    Here's the code... please tell me what I need to put in..
    If Me.NewRecord Then
    If MsgBox("Are you sure you wish to cancel record", 36, " Continue?") = vbYes Then

    Me.Undo
    DoCmd.Close
    End If
    End If

    What do I want it to do?? cancel the record input and close the form.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There must be something else that prevents the form from being closed. I tried your procedure:
    Code:
    Private Sub Command2_Click()
    
        If Me.NewRecord Then
            If MsgBox("Are you sure you wish to cancel record", 36, " Continue?") = vbYes Then
                Me.Undo
                DoCmd.Close
            End If
        End If
        
    End Sub
    and it works as expected. What makes filling some fields mandatory, is it in the table definition or elsewhere?
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    14

    Exclamation Code! What's going on!

    Hi friend!
    Thank you - you're quite right I do. In the unload event of the form I have the following:
    Private Sub Form_Unload(Cancel As Integer)
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
    If Ctrl.Tag = "*" And IsNull(Ctrl) Then
    MsgBox "A required data entry has not been made - please check fields with * are filled in"
    Cancel = True
    Ctrl.SetFocus
    Exit For
    End If
    Next Ctrl
    End Sub

    I tried moving this code to a button on the form that runs this code on click and I added Docmd.close at the end of it.

    Now all I get is an error saying: The expression On Open you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name. Weird.

    I've attached the code for the form just incase i've actually done something really silly somewhere.

    Thank you so much for any help you can give...
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There's a problem with the declaration of the "Command205_Click" procedure:
    Code:
    Private Sub Command205_Click(Cancel As Integer)
    It should be:
    Code:
    Private Sub Command205_Click()
    Have a nice day!

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The validation code you have in the Form_Unload event needs to be in the Form_BeforeUpdate event, instead.

    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. #6
    Join Date
    Jan 2012
    Posts
    14

    Next issue is this...can you clever ones help again?

    Great - thank you. I'm still learning!

    The next issue is that the validation on the button Command205_click (I must name objects better!) runs and then the form closes. I want it so that the validation is run and any missed fields can be entered but if all has been filled in correctly the form closes... should this be an if or something do you think? This is the code I have at the moment..

    Private Sub Command205_Click()
    Dim Ctrl As Control
    For Each Ctrl In Me.Controls
    If Ctrl.Tag = "*" And IsNull(Ctrl) Then
    MsgBox "A required data entry has not been made - please check fields with * are filled in"
    Cancel = True
    Ctrl.SetFocus
    Exit For
    End If
    Next Ctrl

    On Error GoTo Err_Command205_Click

    DoCmd.Close

    Exit_Command205_Click:
    Exit Sub

    Err_Command205_Click:
    MsgBox Err.Description
    Resume Exit_Command205_Click

    End Sub

    Thank you friends..

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Once again, the validation code
    Code:
    Dim Ctrl As Control
       For Each Ctrl In Me.Controls
           If Ctrl.Tag = "*" And IsNull(Ctrl) Then
              MsgBox "A required data entry has not been made - please check fields with * are filled in"
              Cancel = True
              Ctrl.SetFocus
              Exit For
           End If
       Next Ctrl
    Needs to be in, and only in, the Form_BeforeUpdate event!

    As I think I've said, I've never used this particular Validation code, employing the Tag Property, but it looks doable.

    You don't need validation code in the 'Close' Button! When the Form goes to close, the Form_BeforeUpdate event, with its validation code, will run. All you need your Button to do is start the closing process.

    In point of fact, you really don't need a 'Close' Button! Moving to another Record, closing the Form, or closing Access itself, will start the validation process. If you insist on a 'Close' Button, all the code you need in it is
    Code:
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close
    The first line

    If Me.Dirty Then Me.Dirty = False

    is needed because of a long standing bug in Access. When the line

    DoCmd.Close executes, to close a Form thru code, it does so without regard as to

    • Whether or not Validation Rules have been violated
    • Whether or not Required Fields have been left blank

    If either of these conditions are true, Access will merely dump the Record, without any warning, and close the Form! Setting Me.Dirty to False, if it currently Is Dirty, forces a Save and starts the Validation Process in Form_BeforeUpdate, as well as enforcing PK/Required Field rules.

    As for a Button to simply dump the Record, in order to return to your previous menu Form, all you need is Sinndho's code from Post # 2.

    If you're still having problems, you know where we are!

    BTW, we're all still learning! No matter how long we've been doing this!

    Linq ;0)>
    Last edited by Missinglinq; 01-29-12 at 12:07.
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Jan 2012
    Posts
    14

    Thanks but there is still a problem... please help!

    Thank you so much for your help. I've tried what you said and i've still a problem. (code attached - its probably obvious to you!). When I click the button to close the form (which takes the user to a main menu of choices), I get a run-time error 3021 - No current record. I thought maybe i'd need a button to process the request before returning to the main menu? So I tried that too! Can you see where its going wrong?

    All I need is a data entry form with a button to cancel and close form, a button to close the form and one to add another new record... i'm getting there but not quite there yet... please can you help me again.

    Much appreciation... thanks.. Tracy.

  9. #9
    Join Date
    Jan 2012
    Posts
    14

    Here's the code

    Oops - code is here!
    Attached Files Attached Files

Posting Permissions

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