Save both parent form and subform from parent form.

So I could write the subform record to a temp table and then use the save button on the main form to actually save the record to the 'live' subform table, correct? And if they don't save the changes then it just deletes that temp record from the temp table. That's actually a good solution. Takes a little more work to set it up but it will give me the results I am looking for.

Yup, that's exactly it.

  But then you need an "Edit" button as well or you have to copy the data every time the OnCurrent fires for the main form, and then you also need to get into the locking and unlocking of controls.

  and BTW, the subform record does get saved as well when you exit.   I implied that it did not.

 There used to be a nice MSKB article and sample DB covering this, but I think it has been removed.

The other options are:

1. Binding the form to a recordset that you open in code.   By doing this, you can use transactions.
2. Going the "unbound" route; you get/put all the data into all the controls, and use recordsets behind the scenes to handle all the record operations.   This is a lot of work, and if you go to this extent, you might as well not use Access.

 #1 works, but then you need to control all the linking between the main and subform(s).

 Temp tables is probably the least amount of work to do this type of interface.

Jim.

Here's an old thread BTW on this:

https://www.experts-exchange.com/questions/27176789/MS-Access-VBA-Undo-on-a-sub-form.html

 The last comment covers a link for an example of the unbound approach.    It also has a link to the MSKB article I mentioned, but that link is now dead :(

I may have the sample DB here though (have to look)

Jim.

Let me take a run at creating everything before I close this post. If you have a sample that easily accessible I would love to take a look at it while I am creating this new demo.

I do have a 'modify/edit button' that I didn't mention. I lock all my controls via a custom function when the form is opened. The controls will unlock when the user either creates a new record or modifies an existing one.

Just one question Jim. When you say 'Binding the form to a recordset and opening in code', do you mean using VBA to load the records instead of using a table as the record source? What advantages are there by doing it this way? I've always just used tables or queries as the record source.

One more thing....This is a multi-user environment so how would I prevent users from updating the same record using temp tables? Is there a way to locking the 'Edited Record' from the main table and not the temp table?

This is a multi-user environment so how would I prevent users from updating the same record using temp tables?

 I use this approach to "lock" a record:

https://www.experts-exchange.com/articles/5328/Resource-locking-in-your-applications.html

If you have a sample that easily accessible I would love to take a look at it while I am creating this new demo.

 I'll look to see if I have it.

I do have a 'modify/edit button' that I didn't mention. I lock all my controls via a custom function when the form is opened. The controls will unlock when the user either creates a new record or modifies an existing one.

 I did this type of interface years ago, but gave up on it.   Had a "control panel" on the top of each form:


  Boxes to the left controlled look-up.  When you clicked "Edit", it would look like this:


  Orange fields were required.

Just one question Jim. When you say 'Binding the form to a recordset and opening in code', do you mean using VBA to load the records instead of using a table as the record source? What advantages are there by doing it this way? I've always just used tables or queries as the record source.

  Yes.  Starting with Access 2000, you can open a recordset in code, and then assign that as the forms recordset.  Doing so allows you to use transactions (BeginTrans, Commit, and Rollback).

  However it causes problem with main/subform linking, which you then need to handle yourself.

Jim.

OK, did a lot of digging, and unfortunatley I don't have the A2000 version, which as I remember it, was a little more polished than what I found; the Access 2.0 (yes that's correct) version of this.

It doesn't compile, but that's due to some old syntax.   Still though, you can look at the form and the module, and see what was done.

Jim.
UNDOFM.TXT
UNDOFM_A2003.MDB

BTW, this is how you set a forms recordset in code:

Option Compare Database
Option Explicit
Public conn As ADODB.Connection
Public rst As ADODB.Recordset

Private Sub Form_Close()
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
    
End Sub

Private Sub Form_Open(Cancel As Integer)
    
    Set conn = CurrentProject.Connection
    
    'Create an instance of the ADO Recordset class, and
    'set it's properties
    Set rst = New ADODB.Recordset
    With rst
        Set .ActiveConnection = conn
        .Source = "SELECT * FROM tblCustomers"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .Open
    End With
    
    'Set the form's Recordset property to the ADO recordset
    Set Me.Recordset = rst
    
End Sub

Select allOpen in new window

and since were covering all the bases, attached is the sample forms DB where that code came from, along with a bunch of other examples, one of which is an unbound form example.

Jim.
FrmSampl.mdb

Thank you very much Jim. I really appreciate all the help and information. I'm going to review everything today and work on a demo. I will post back with any questions as well as the working demo.

Another method that is much less labor intensive is to use a flag on the parent record.  When the parent record is originally created, the flag is set to "incomplete" and ALL queries except the one for the maintenance form should ignore incomplete items.  This method is most appropriate for batch entries where you have to enter a set of details that all have to balance before the entire set can be accepted.

Then you have a button on the main form that the user presses when the transaction is complete.  Your code then balances the amounts and if it is indeed in balance, you update the flag to "complete".

Additionally, you will need a query that runs every time the database opens that lists all incomplete transactions.  You don't want people to forget to complete them.

If it is possible to update this set of data, then this is probably not the best option but you could do it, you would just have to change the parent flag from "complete" to "incomplete" for the duration of the change process and then mark it complete again.

Jim, This is going to take me a little longer than I thought to get this demo working. I'm going to close this ticket for now and post back when I have a working model. I'll alert you when I do so you can take a look at it if you have time. But the information you gave me is a great start and I now know how to build this to do what I want it to do. Thank you again for all your help. I will be in touch shortly....

Your welcome.

Jim.