Rollback / Undo Function

[youngchestnut]

Hi all,
I have a table called FinalResults which contains 1 field 'Result'. A form links to this table (in continuious form view) and when the form is loaded displays all the results in the FinalResult table. The user can change any of the results shown. What I want to be able to do is to 'rollback' or 'undo' ALL of the changes that the user may have made, on the click of a button ie. set the value of the records back to what they were at the point the form was loaded.
I thought I would be able to do by populating an array when the form was loaded but because (as I soon found out) I can't have a Public array this has proved fruitless.
Secondly I thought that I would be able to populate a Public recordset on form load but, strange to say, this recordset changes dynamically as the user changes the values. That I can't get my head around!!
There may be a really easy way of doing this but I'll be stuffed if I know what it is.

Love you forever if you can help me.

Love and peace

 

[RiderJon]

Make the text box unbounded and "update" on some event, for example, on exit (if necessary ofcourse) ..... Below is a crude algorithm:

FOR DISPLAY (on form open):
Me.txtBOX1 = PT2ProjectedEntering

FOR UPDATE (say on exit):
if (update checked or something like that)

PT2ProjectedEntering = Me.txtBOX1




RiderJon
"I might have created ctrl+alt+del,
But Bill made it famous" - Dr. Dave

 

[LambertH]

While it's true that you cannot have a Public Array in a form's code module (because it is a class module), for your purposes there's nothing to stop you just using

Dim SomeArray()

You don't need a pubic array as the code is goingto run in the form which declares the array.

As it's not a public array this will compile. Then in your form's Load event you can figure out how many records thereare and then ...

Redim SomeArray([however many recordthere are])

and fill the array.

 

[youngchestnut]

To RiderJon : Can't use the AfterUpdate event because the form is shown in continious view, this results in all of the text boxes showing the new value. ie the same value over and over again.

LambertH : I can populate an array on the way into the form but how can call these values on the click event of another button?

 

[jsteph]

youngchestnut,
The most robust way to do this is to use temporary tables.

Since you have said you need the rollback capability, then you must define 'session' boundaries.

Once defined, then when the session begins, load the temp table(s) with the 'live' tables' data based on the criteria that the user will be editing that session, ie, all header/detail records for the particular customer. The form/subform are based on these temp tables. Deletions are handled via a 'delete' flag in the temp tables.

When session is done, prompt user to save session, then you have code that is wrapped in it's own transaction and:
1. delete all records in live table with delete flag set on the 'temp' side.
2. Update all remaining data with keys matching temp data
3. append any new temp data.
4. option final prompt before commiting this transaction

It seems like a lot of work and it is, but it's the most robust way in my opinion.
--jsteph

 

[youngchestnut]

Jsteph,
I'll have a whizz at coding that this afternoon. Let you know the results

Cheers for the reply

YC

 

[jsteph]

This may help, I used it to save my fingers when doing the Update queries that set the temp fields to the live fields:

Sub MAKE_SQL()
'generates UPDATE SET sql for updates from similar tables to each other
Dim DB As Database, strSrcT As String, td As TableDef, strFn As String
Dim strTmp As String, i As Integer, strDestT As String, strJ As String, bExclude As Boolean
Dim strWhr As String, j As Integer, idx As Index, strFlds As String, arEx() As String
Set DB = CurrentDb

'We must assume identical tables... OR
'...the other table may have more fields than this one, but hey won't get included...
'...and that the fields here in dest do exist in source.
'Typically for a temp-table situation they will be virtually equal, except for delete flags, etc.

strSrcT = "tblCust_TEMP" 'Source These can be passed in as args
strDestT = "tblCust" 'Destination table, table with fields being UPDATE...SET
Set td = DB.TableDefs(strDestT) 'Destination--we use this as a base for fetching fieldnames


'Create a Join clause, use any unique index--again, we must assume these tables are equal in structure
strJ = "" 'init
For i = 0 To td.Indexes.Count - 1
    'just need a unique one, not necessarily named 'PrimaryKey'
    If td.Indexes(i).Unique = True Then
        Set idx = td.Indexes(i)
        'below array is for exclude list, explained further down
        ReDim arEx(idx.Fields.Count - 1)
        For j = 0 To idx.Fields.Count - 1
            strJ = strJ & IIf(j = 0, " INNER JOIN " & strSrcT & " ON  ", "") & "(" & strDestT & "." & idx.Fields(j).Name & " = " & strSrcT & "." & idx.Fields(j).Name & ") AND "
            'build exclude list because many db's don't allow updating of keys--even if it's to the same value
            arEx(j) = idx.Fields(j).Name
        Next j
    End If
Next i
If strJ = "" Then
    strJ = vbCrLf & "****HowToJoin??* No unique index!! ***" & vbCrLf
Else 'strip trailing AND
    strJ = Trim(strJ)
    strJ = Left$(strJ, Len(strJ) - 3)
End If

'Now get SET list...
For i = 0 To td.Fields.Count - 1 'loop Destination fields
    strFn = td.Fields(i).Name
    'since we will disallow update of keys, we exclude them from update
    bExclude = False 'init
    For j = 0 To UBound(arEx) 'quick & dirty scan for unique index fields
        If strFn = arEx(j) Then
            bExclude = True
        End If
    Next j
    If bExclude = False Then
        strFlds = strFlds + strDestT & "." & strFn & " = " & strSrcT & "." & strFn & ","
        'For easier reading, break every 2 fields
        If i Mod 2 = 0 Then
            strFlds = strFlds & vbCrLf
        End If
    End If '
Next i
'strip trailing comma
If Right$(strFlds, 1) = Chr(10) Then 'if crlf is trailing
    strfds = Left$(strFlds, Len(strFlds) - 3)
Else
    strFlds = Left$(strFlds, Len(strFlds) - 1)
End If
'now put it all together
strTmp = "UPDATE " & strDestT & " " & vbCrLf & strJ & vbCrLf & " SET " & vbCrLf & strFlds
strTmp = strTmp & " WHERE..."  'let user add where clause or parameter
Debug.Print strTmp 'cut/paste this from debug window

End Sub

--jsteph

 

[youngchestnut]

Temp table method worked. just populated on the table on form load and the On Click event of a button ran a update query. Update query just had the two tables linked together. No need for power code or complex SQL statements. Cheers matey. Nice 1