Save both parent form and subform from parent form.

I have a parent form and a subform that I want to create a save button on the parent form that will save both the main form and subform when pressed. I know once you leave the subform the record is automatically saved but is there a way around this to use one save button on the parent form instead of having to put a separate save button on the subform? Just trying to get some ideas on how I can make one set of command buttons run both the main form and the subform. I have a save button, new record button, and undo button on the main form but they only run the main form. Is there a way around the record being saved on the subform once you leave the form?
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I know once you leave the subform the record is automatically saved >>

 Actually, it's when you enter a subform control, the main record is automatically saved.

<<Just trying to get some ideas on how I can make one set of command buttons run both the main form and the subform.>>

  It's not easy.   The commands are:

  Me.Dirty = False
  Me.<subformcontrolName>.Form.Dirty = False

  But you can't get around the fact that Access saves things automatically when the user moves the focus to the sub-form.    In many cases, you provide a "cancel" along with a "save" and you can't do that without copying data to temp tables basing the forms on the temp tables.

Jim.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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:

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

Region-Capture2.jpg
  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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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.
0
 
PatHartmanCommented:
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.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
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....
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Your welcome.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.