Results 1 to 5 of 5
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Post Discard dirty bound form and subform content

    I've got a form with a subform for data entry. I'd like for it to not end up generating a new record if the user does not click save. My current method is to replace the standard "close" control with a custom close button that does the following:



    Code:
    Private Sub cmdClose_Click()    Me.Undo
        Cancel = True
        DoCmd.Close
    End Sub
    This effectively discards the contents of the main form, BUT, if anything has been entered on the subform, it still saves the record. So I'm wondering what a more effective way would be to not save anything unless you click through the button that saves the record. Ideally would like to keep the form bound so I don't need to custom program everything.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you have relationships set up and enforce referential integrity?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by June7 View Post
    Do you have relationships set up and enforce referential integrity?
    Yes, there's a one to many, one record on the main form can be related to multiple of the subform. So if you cancel out once you've created a subform record, it fails to undo because its already written the record to the table once you dirty the subform.

    I could add a "delete" button to the main form that cascade deletes the record and all related, but that will only account for cases where people are being conscious and intentional about their data entry (i.e. not reality).

    I suppose I could have the close button delete the record if the form is dirty? Seems like a messy workaround to some extent.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not an issue I've had to deal with so not sure how to approach.

    To delete record, I think would have to first commit to table. May seem messy but maybe the simplest approach. Commit main record then delete and allow cascade delete to clean up dependent record(s).
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only thing I can think of is 2 staging tables; 1 for the main and 1 for the subform and bind to those. If the process goes as you wish, write back to the main table(s) as appends or updates as the case may be. If it doesn't, flush the staging tables for next time and the 'main' tables remain unaffected. This way, you bind the form(s) to the staging tables and retain all the usual automation. If there is a better way, I don't know of it, mainly because in many cases a button to save a record on a bound form is pointless without staging tables. That's because anything you do that causes a new record to not be current will automatically save it. F'rinstance, you have a datasheet form, create a new record then select a prior record. New record just got saved - unless it fails validation. If that happens with staging tables, you still have a chance to discard the edits or new record.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Bound main form locks subform
    By treckberg in forum Forms
    Replies: 4
    Last Post: 05-29-2020, 12:38 PM
  2. Replies: 10
    Last Post: 09-29-2016, 08:00 PM
  3. Replies: 6
    Last Post: 12-23-2013, 07:38 PM
  4. Subform Opens Dirty
    By EddieN1 in forum Forms
    Replies: 2
    Last Post: 05-20-2013, 09:07 AM
  5. On Dirty fires but Me.Dirty = False (v2010)
    By Rod in forum Programming
    Replies: 5
    Last Post: 07-30-2011, 08:42 PM

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