Sounds like fun Markus. But whats the Difference between [OK] and [Apply]  ie What do they do differently. and also how do you confirm a record deletion ie. Delete then [OK] or [Apply] or what.

Leigh

It's just to mimic standard buttons in option dialogs. [OK] and [Cancel] both close the form. [Apply] validates the changes without closing.

While the form is open, you should be able to edit freely: insert records, change them, delete records (as a bonus you can change the default message: "you will not be able to undo" to nothing or to "you can press [Cancel] at any time later to undo").

You can use the form's Before Update event to validate data in a single record. Potentially, this should allow you to validate data entry for several records (even in several tables if we expand the basic idea).

(°v°)

@Leigh (ldunscombe)

You show the default mechanism offered by Access to handle single-record updates. It works, of course, and the addition of the Undo event handles even complex cases nicely.

However, the aim here is to allow several modifications to a subform, including deletion of records, and to undo that with one click. Not just the record shown in the main form (in the suggested setup, there is no record in the main form), but all records in the subform. The user should be able to add two records, delete three, and then go: "Ah, no, this is wrong, let's cancel the hole thing!" The same mechanism could be used for a main form, naturally.

@Joe (DatabaseMX)

Your first assumption is correct: [Cancel] means rollback and close, [Apply] means commit and start a new transaction, [OK] means commit and close. This means that [Cancel] would only undo editing since the last [Apply], exactly like in any of the option dialogs that offer an [Apply] button.

Both DAO and ADO can be used. I'm more familiar with DAO, but I know it can be done with ADO as well.

@Gustav (cactus_data)

Yes, bound forms are simple and easy to implement in Access. However, the rollback capability is not integrated into forms. It is of course possible to create an unbound form, even with a subform using a disconnected ADO recordset, and have the [OK] or [Apply] buttons copy the information from the unbound controls and the disconnected recorset into the live tables. There are some tricks involved to manage deleted records, but it can be done.

The "challenge" here is to use bound forms, because it's simple, but to allow all updates performed to be undone, even for several records (or several records in several tables if we expand the idea that far).


Hint: rollback is implemented and well documented for action queries and recordsets created in VB. Is it possible to use that or do something similar for forms as well?

Thanks for the comments so far!
(°v°)

"Both DAO and ADO can be used. I'm more familiar with DAO,"

I am definitely more familiar with DAO. Perhaps - in order to keep this apples to apples, we should specify it s/b DAO ?  From some recent discussions on ADO Disconnected Recordsets, seems the overall opinion of them is not too high ?  I recall a Q/discussion posted by Mr Bullwinkle as such.

mx

<cough choke chortle>

Ah, this is starting to roll! Great!

@ Leigh (LPurvis)

Thanks for monitoring. I know you are here only for the fun, and so should everybody...

@ Joe (DatabaseMX)

In some ways, it makes more sense to use DAO for Jet databases, partly because in that case forms use DAO recordsets anyway. Also, it's not possible (am I entirely sure of that?) to manipulate DAO object properties (formatting and other display properties of tables) through ADO. This being said, as pure database API, ADO is a superset, and implements many things that DAO can't do.

Anyway, that's not the point: both DAO and ADO implement transactions and can be used to create a form with rollback capability.

@ capricorn1

That's the start, but surely there is no example of applying transactions to a form, is there?

(°v°)

Yes, everything. In my suggested setup, it's an ubound main form displaying a simple table in a subform, but it could be expanded to a one-to-many relationship (including cascade update and cascade delete).

(°v°)

Would maintaining the main (parent) form as unbound not just add to the total lines of code?
(Part of the challenge is to keep those lines down isn't it? :-)
Or are we talking about the main form not even showing any data at all?
If not then I'd agree that limiting to one record at a time on the parent is worthwhile.  Unbound or not.
(Or providing custom record navigation instead).

I'm excused from offerings until the end of this thread :-) but just to comment that, given the requirements stated by Markus (and this is his game after all) either DAO or ADO is viable.  
I'd say it's down to the preference of whoever wants to offer something.  (No actual code yet though I see from anyone? :-p)

To one extent or another, the technique described should be implementable in Acc2000 as well as subsequent versions.  (Though of course - it's still only been described thus far :-s...)

Acc2000 was a bit on the raw side for some binding issues - but much of the general stuff works.
(Yes - especially DAO... lol :-p)

I agree though - such an example should be a data centric main form as well as subform.  So it tackles one of the inherent update commital issues that we often get asked about.

@ Leigh (LPurvis)

I don't recall now why I asked for a subform for this challenge, perhaps because it's a bit more spectacular and clearly draws attention to the multi-record aspect.

@ Jim (JDettman)

Absolutely! Up to Access 97 it was not possible to perform multi-records (and multi-table) validation without using a temp table. But since Access 2000 "you could now bind a form to a recordset opened in code, so you could handle the transactioning in the background". That's more than a hint, it's basically the solution.

(°v°)

Marcus:

"any editing done in the subform since the last [Apply] should be undone."

Now, are we saying that one might add several records to the subform, then decide to roll them all back by hitting [Cancel] or Commit all by hitting [Apply]?  If so, seems we are missing one other button ...lets call it [Save].  Because, as we are adding records prior to an [Apply] or [Cancel] ... we need to 'save' individual adds and/or edits .... *unless* you are just depending on the fact that when you go to a new row in a continuous subform - the record is automatically saved (all things being equal).

Or are you not assuming a continuous subform? Even still, you need a clear way to save 'intermediate' records - prior to an [Apply] or [Cancel] .... if you see what I mean ?

mx

Again this is Markus' thread, but I'm kinda privy to what he has in mind, so I expect I'm right in saying that I don't follow? lol

Why would we want to commit individual records but be able to rollback all en masse?
We're effectively just talking about Transactions here (I don't think there's any doubt there).
And I think all are pretty clear both that we're needing to wrap the functionality of a form within a Transaction and how that can be accomplished. ;-)

So every row (IMO on both a data displaying main form *and* continuous or otherwise subform) will be implicitly saved when edited - but ultimately not committed until explicitly permitted to do so.

All data - both that of the record on the main form and those on the subform - would then be committed as one or rolled back as one (the essence of a transaction).

Unless we're talking about an unbound scenario mimicking a bound form and subform.
Then "Save" buttons to indicate that an individual row has been updated may be tempting (a "Save" button is a common concept in unbound work) - but would detract from the overall "mimic" of functionality.
Though I suppose it could still be a viable solution to the problem as proposed, I'd say it would be better to make saves implicit (or at least appear implicit).  Albeit only in memory until committed as one.

ok ... but, along those some lines ... how does the concept of 'before update validation' on each record fit in here ?

mx

The idea isn't to perform any special validation when updating single records, or deleting them, for that matter. Au contraire! The user should feel free to do whatever to the data, knowing that everything can be undone if it doesn't look right. From the programmers point of view, and entire set of records can thus be validated at once.

Let's take a silly example: a form to enter customized weekday names (in your own language). The form opens, you are asked to enter the numbers 1--7 and provide seven names. The user starts, but can press [Cancel] at any time, whatever was entered or deleted. (The form could perform some validation when [Apply] or [OK] are used, e.g. "Please enter exactly seven names".)

But perhaps a live example of a transaction is best. In the following code, I display poor knowledge of statistics, and ignorance of sub-queries or nested queries. I want to calculate a DISTINCT Count and an average from my Table1, but only for the records where SomeText starts with b.

Here I go: deleting records, creating a temp table, obtaining the result from a recordset. Then the transaction is rolled back: no records have been lost, no table has been really added, no harm was done.

I would like to do the same in a form. Do whatever I want, but with a friendly large [Cancel] button. Jim gave the basic idea, and Leigh is biting his fingers, nobody wants to try...

At least, we can talk about transactions. Bonus question: why doesn't the DoCmd line work?

(°v°)

Sub DumbButCool()
 
    Dim mdb As Database
    Dim strSQL As String
 
On Error GoTo Done
 
    DBEngine(0).BeginTrans
    Set mdb = DBEngine(0)(0)
    strSQL _
        = " DELETE FROM Table1" _
        & " WHERE SomeText Not Like 'b*'"
    mdb.Execute strSQL
    strSQL _
        = " SELECT SomeText, Sum(SomeNumber) As Total" _
        & " INTO Table2 FROM Table1" _
        & " GROUP BY SomeText"
    mdb.Execute strSQL
    strSQL _
        = " SELECT Count(*) As N, Avg(Total) As A" _
        & " FROM Table2"
    With mdb.OpenRecordset(strSQL, dbOpenSnapshot)
        Debug.Print !N, !a
    End With
    ' DoCmd.OpenTable "Table2"
    
Done:
    If Err Then MsgBox Err.Description: Err.Clear
    DBEngine(0).Rollback
 
End Sub

Select allOpen in new window

> why doesn't the DoCmd line work?

Because there is no Table2 until you call CommitTrans which you never do.

/gustav

Tricky. It does exist in memory, as the previous recordset shows, and as you can easily verify with:

    Debug.Print mdb("Table2").Name

But it's not yet written to the database itself. How come the table is visible for CurrentDb, but not for the DoCmd object? Or, in pure EE Asker style:

« In the code above, I created Table2 in a transaction, and I want to open it. When I try, I get the message: "Access can't find the object 'Table2'.", although I can use it it code... I cannot commit the transaction at this point (it's complicated, but I can't), but I really, really need to open the table. PLEASE HELP! »

[It's totally related to the main question, incidentally...]

(°v°)

You would need to add it to the current db's table collection.  Wouldn't you ?

Not in this case. Table2 wasn't created using the DAO method, but through a make-table query. It's appended automatically. If you duplicate the line "mdb.Execute strSQL" of that query, you get: "Table 'Table2' already exists."

Yet, DoCmd does not see it before a CommitTrans is issued. Weird, no?

(°v°)

Ah, full points to both of you! Ah, wait, they tell me I can't do that. Well, we'll work something out.

In a few more words (trying to define "the current scope", the technical answer could be:

During a transaction, only the workspace or the connection performing it "sees" the changes. Obviously, Access uses at least three workspaces: one for the interface (including forms, macros, and the DoCmd object); one for VB (the default DBEngine(0)); and there must be a third for Jet when performing schema changes (using the account "Engine", owner of the system tables), but that's not the issue here. Since DoCmd uses a different workspace, it doesn't see the changes, just like any other user of the database.

I wouldn't know how to start a transaction for the DoCmd object; I suspect it's not possible. But forms can use VB's default workspace, active connection, or newly created objects, and thus use transactions.

(°v°)

So are you feeling that we're done Markus?
Can I put my example mdb back up? lol

I do have a couple of things to mention... but am about to rush out the door for today (perhaps 'til the weekend...)

Anyway... still no code from anyone other than the OP? lol
This must be a first. :-p

Still none. I'll post my 30 lines tonight, I suppose... and please do put your example back up!
(°v°)

"Transactions In Forms" example returned.
I'll post my other thoughts when I have a moment.

Cheers!

Well here is the basic code, using DAO. No error handling, and using only a subform (but it's easy to add one line for the main form as well). It's really boilerplate code, simple to insert into an existing form if you want to make your own tests.

As soon as Leigh saw the question, he remained me that he had not only done so, but provided a comprehensive demo database covering the basics (for both DAO and ADO) on his Access sample page:

Example Access Methods: Transactions In Forms (Download FormTransaction.zip)
http://www.databasedevelopment.co.uk/examples.htm

Reactions? Comments? I thought it was really cool!

Cheers all, and thanks for participating!
(°v°)

Option Explicit
 
Private Sub cmdApply_Click()
    With DBEngine(0): .CommitTrans: .BeginTrans: End With
End Sub
 
Private Sub cmdCancel_Click()
    DBEngine.Rollback
    DoCmd.Close acForm, Me.Name
End Sub
 
Private Sub cmdOK_Click()
    DBEngine(0).CommitTrans
    DoCmd.Close acForm, Me.Name
End Sub
 
Private Sub Form_Close()
On Error Resume Next   ' might have commited on [OK]
    DBEngine(0).Rollback
End Sub
 
Private Sub Form_Load()
    With Me.subData.Form
        Set .Recordset = DBEngine(0)(0).OpenRecordset( _
            .RecordSource, _
            dbOpenDynaset)
    End With
    DBEngine(0).BeginTrans
End Sub

Select allOpen in new window

It's time to close this. I'll accept Leigh's answer and split points for participation. Yes, I could include my own, but that wasn't the idea, was it?

I have a few other ideas for "expert questions". Now please tell me frankly, is this something I should continue? I suppose that anyone annoyed by the concept can simply skip them, so I guess I will try again.

Anyway, thanks for participating!

Markus (°v°)

wow ... what happened a post I made several days ago? Weird!  

Well, what is said was ... I did try this out (downloaded the mdb) and it IS very cool ... and in the final analysis - quite simple (not to down play the elegance of it).  It would require a bit of error trapping, but hey ... it's a cool concept.

mx

I thought so too! I even use it from time to time... -- (^v°)

! I even use it from time to time"

Actually, can we give a few real world examples where this might be used ?

mx