Results 1 to 6 of 6
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    793

    Closing form without saving data

    I have a form what auto numbers 2 fields. If I close the form without doing anything it saves that record with the new numbers. I want to be able to close the form and delete the record when certain fields are not filled in.
    What is the correct code where the "Close" Button is selected.

    If IsNull(Change_Requested) And IsNull(Rationale) And Me.Close.Enabled = True Then


    DoCmd.RunCommand acCmdDeleteRecord
    End if

    Thanks

  2. #2
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,942
    Sorry, but this really makes no sense!

    First off, Access will only allow one Autonumber Field in a Table. And a Record with an Autonumber Field will not be saved if you "close the form without doing anything!" It will only be saved if you enter at least one character in another Field. How are these 'autonumbers' being generated?

    Secondly, if your 'Close' Button wasn't Enabled, you couldn't run any of this code, could you, since you couldn't click on the button?

    Third, you cannot delete a Record that hasn't been saved; you need to Undo it, instead.

    The code behind your 'Close' Button should simply be

    DoCmd.Close

    Then, in your Form_BeforeUpdate event, check your required Fields and Cancel the Update:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If IsNull(Me.Change_Requested) And IsNull(Me.Rationale) Then
        Me.Undo
        Cancel = True
      End If
    End Sub

    You might want to consider popping a warning Messagebox, informing the user that the Record is being dumped, maybe even offering to let them correct the missing data oversight, but this code will dump the Record as you requested, if the two Fields are empty.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    793
    Good point on auto number. I have a VBA incremented number with 2 fields that will increment under certain conditions.

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
     On Error GoTo Error
      If IsNull(Me.Change_Requested) And IsNull(Me.Rationale) Then
        Me.Next.Enabled = False
        Me.Undo
        Cancel = True
        Else
        Me.Next.Enabled = True
    Error_Handler_Exit:
    Exit Sub
    Error:
    Select Case Err.Number
        Case 2501
        Err.Clear
    Resume Error_Handler_Exit
        Case Else
        MsgBox "Error No. " & Err.Number & vbCrLf & "Description: " & Err.Description, vbExclamation, "Database Error"
        Err.Clear
    Resume Error_Handler_Exit
    End Select
    Exit Sub
    End If
    End Sub
    Private Sub Form_Current()
        Me.O6Vote.Enabled = False
        Me.GOVote.Enabled = False
        Me.FinalVote.Enabled = False
        Me.Soft_Level.Visible = (Me.Level = "Software")
    Dim tmpNIE As Double
        tmpNIE = DLookup("NIE", "Change Request")
        Me.NIE = tmpNIE
    If IsNull(CR_No) And DLast("Action_Complete", "[Change Request]") = True Then
            Me.CR_Num = DMax("CR_No", "[Change Request]") + 1
            Me.Sub_Num = 0
       Else
        
    If IsNull(CR_No) And DLast("Action_Complete", "[Change Request]") = False Then
            Me.CR_Num = DMax("CR_No", "[Change Request]")
            Me.Sub_Num = DLast("Sub_No", "[Change Request]") + 1
        End If
        End If
    End Sub
    I know I'm not as eloquent as others, but thus seems to work well for me. so far. With your code added I get a fail "You can't go to the specified record" when I select the next button - which is good. When I select any other button (Except "Close" - Fail to save error continue") I receive the same error as when I select Next. Once accepted I can select the other buttons (for Record navigation) and it will work. I didn't see the error code, but I would like to make an excepts code for everything but the close and next button.

  4. #4
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,942
    One would have to assume that if you don't want to save a partially filled Record, when closing the Form, that you don't want to save a partially filled Record when moving to another Record, either; is this not true?

    What does this line have to do with dumping your record if it is not complete:

    Me.Next.Enabled = False

    That's why you're getting the error when you try to go to the next Record, using what I assume is a custom navigation button. If you have an incomplete Record and hit 'Next,' you will be taken to the Form_BeforeUpdate event, the code I gave you will fire and the Record will be dumped; there's no need to enable/disable the next button.

    Lastly, what, exactly, does "I would like to make an excepts code for everything but the close and next button" mean?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    793
    Me.Next.Enabled = False does not give the error it only greys out the next button until Change Requested and Rationale are filled in.

    Code:
     If IsNull(Me.Change_Requested) And IsNull(Me.Rationale) Then   ' Requirements
        
    'if requirements are not met
        Me.Next.Enabled = False ' Greyed out
        Me.Undo 
        Cancel = True   ' Killes the record
        Else
    
    ' If requirements are met
        Me.Next.Enabled = True  'Allows user to select button
    excepts code - apologies - Exception code.

    Something like this:
    Code:
    Select Case Err.Number
        Case 2501
        Err.Clear
    Err.clear = true if <> Me.next

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    793
    This solved the issue:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
      If IsNull(Me.Change_Requested) And IsNull(Me.Rationale) Then
        Me.Next.Enabled = False
        Me.Undo
      If IsNull(Me.Change_Requested) And IsNull(Me.Rationale) Then
            Me.Next.Enabled = True
    End If
    End If
    End Sub

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

Similar Threads

  1. Closing access database without saving
    By onaggar in forum Access
    Replies: 7
    Last Post: 12-19-2013, 06:23 PM
  2. Closing the form without saving the information
    By selvakumar.arc in forum Forms
    Replies: 5
    Last Post: 12-11-2013, 08:50 AM
  3. Closing a Form Based on Table Data
    By perihelia in forum Programming
    Replies: 2
    Last Post: 03-05-2012, 05:33 PM
  4. Replies: 3
    Last Post: 06-02-2011, 07:40 AM
  5. Closing and saving a form
    By Lxmanager in forum Forms
    Replies: 14
    Last Post: 11-21-2010, 02:04 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 - Senior Forums