Preventing Automatic Saves when Activating a tab with a subform.

[caddcop]

My application has three main forms. Each form has tabs and certain tabs are for editing related records in subforms.
If the current main record is "dirty", clicking on one of the tabs with subform automatically fires off the save record command as it moves to the tab. My subject question is, how can I prevent Access from saving the "dirty" record unless a Save Record button is pressed?

[pbaldy]

To the best of my knowledge, you can't. When focus leaves the main form to the subform (or vice-versa) Access will try to save. If you want to save it all at once, you're probably looking at going to an unbound solution, where your forms and subforms are bound to temp tables and data isn't put into the real tables until you click your save button.

[caddcop]

Rats!
Thanks for the info.
I am considering adding code to disable the tabs if the form is "Dirty" and enabling a Save command so users can decide if the save is necessary.
But I am becoming Event Challenged which can be seen in my new thread...

[JvdP]

Considering the fact that this thread isn't too old I'd like to ask my related question here. I seem to have the same problem as the topic starter but I can't seem to figure out the work-around that you used caddcop. What did you do to eventually fix this problem?

I looked at your other topic and it seems like you have simply created a message box that will prompt the user to save after changing tabs, is that correct? I don't quiet like that solution since it will prompt the user too much in my situation.

It would be great to have a solution where the user can go back and forth between tabs without having to worry that data will be saved (or lost). This seems to be the common functionality within programs nowadays (such as setting forms) and as you might understand I'd like to keep it simple.

Of course I understand I can make the form unbound and let a save button manually add or edit records but this would mean a hell of a lot of programming. Does anybody have a quicker/cleaner solution to this problem?

[caddcop]

As I left this tread to start a new one, once I hit this particular brick wall, I will try to respond there where I can refresh my memory as to where I ended up...
But no, that seemed to be a dead end.
What I was able to do, was allow users to view the tabs with subforms, but make them read only until they saved their changes from the main record. It took a little extra coding - the click events on tabs and subforms on tabs are kind of quirky. The alternative seems to be to make everything read only by default and add an edit record button where needed. On the subform tabs, it might be necessary to prevent leaving the tab until changes were saved. And in my case where one tab has two subforms, it would get even more complicated. If I had complete control over my interface, I probably would not setup such complicated forms, but I was instructed to make a tool to mimic another product's interface so users would not need extra training, or would know where to go to edit or review certain fields.
The other application is not in Access or probably even in VB so it may not have the limitation that Access has. And I don't want to start over from scratch in VB without knowing that I would not meet a similar limitation.

[JvdP]

Wow, caddcop, this is great stuff! I wasn't expecting anybody to answer quickly so I'm delighted. Would you, by any change, be willing to share the VBA for this enabling/disabling? I tried to tie some code to the TabGroup OnChange event that would disable a subform but somehow I couldn't get it to work. You see, I'm a rather big n00b when it comes to VBA.

[caddcop]

I have been on other non-access non-vba tasks for a while and do not recall all of my efforts but I can recall a few tidbits and post some code.
This code is used to confirm saving a change. If I recall, by making it an event in my main form, it fires any time Access would attempt to automatically save a record. This would be moving to a new record, or moving to a tab that requires Access to save the main form record.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Result As VbMsgBoxResult
    Result = MsgBox("You Selected to move to a new Record but have not saved your " _
    & vbCrLf & "edits. OK saves, Cancel aborts.", vbOKCancel + vbCritical, "Continue?")
    If Result = vbCancel Then Cancel = True
End Sub

The Cancel = True tells Access to abort the save

[JvdP]

Thank you very much caddcop! You've been a great help.

[stevemanser]

There is a thread here which contains a sample access database with forms bound to temp tables. This gives a Save and Cancel button so that it works a bit like a Word doc "Do you want to save changes?" when you exit a doc. It works fine as it is but I'd love some access gurus to improve it. At the very least it will give you some idea how binding to temp tables can work.

http://www.access-programmers.co.uk/...=206862&page=2

or here

http://www.access-programmers.co.uk/...2&postcount=27