Undoing changes

Zaeed

Registered Annoyance
Local time
Tomorrow, 06:46
Joined
Dec 12, 2007
Messages
383
I have a form (formA) that opens, a user selects a few values (yes/no checkboxes) and hits save or cancel. These values are then shown as equivalent names on another form (formB) . When Save is clicked on formA, the values are updated on the linked table. (bound form, dynamic recordsource set on FormLoad)

This is what I want to be possible.

- FormB opens (loads from tableB)
- User clicks field that opens formA (loads from tableA)
- changes made to formA
- user clicks save on formA (updates tableA)
- formA closes, bringing user back to formB
- user clicks Cancel on formB
- any changes made to tableA are undone.



Can anyone help?
 
Initial coding thoughts, Zaeed:

- FormB opens (loads from tableB) docmd...
- User clicks field that opens formA (loads from tableA) OnClick event of control
- changes made to formA
- user clicks save on formA (updates tableA) docmd.save (or save the changed record to a temporary table / array)
- formA closes, bringing user back to formB forms("formB").setfocus?
- user clicks Cancel on formB
- any changes made to tableA are undone. delete * from temporary table / lookup a record in the source table and delete?
 
yeah, i was hoping to avoid the temp table approach.

If I have a recordset that contains just one record, the same data that gets displayed by formA, is it possible to load that recordset back into the table after the table has been altered?

Even after reading up on them I still don't fully understand them. Basically I want to update a table from a recordset. I dont know if its possible, but i dont see why it wouldn't be.
 
Basically I want to update a table from a recordset. I dont know if its possible, but i dont see why it wouldn't be.
Of course it's possible. That is what recordsets are used for most of the time!
If I have a recordset that contains just one record, the same data that gets displayed by formA, is it possible to load that recordset back into the table after the table has been altered?
I don't understand your question here Zaeed, but let me make a couple of points here:

**Recordsets, in general, are read/write objects.
**The above fact tells you that you can copy data from another source into the recordset.
**Don't be afraid of the term "recordset". It is simply another "representation" of the "thing" you see through the interface (the table that you see). Just for kicks, here is the "layering" of an actual table (from sourcing to the interface "end product"): tabledef, recordset, object. That is, what I see, as the order of "levels" of thinkness and complication. And it all ends up rolling into one entity. Pretty cool, huh? :)

One more question: Why don't you understand recordsets? What is difficult about that?
 
mmmmk....

To clarify. What i meant was this. On the load of formB, I create a recordset from tableA with just one record. When formA opens, its recordsource is set to the same record. If changes are made to formA, that changes tableA.
What i meant was, can i dump the recordset I initially created, onto tableA, thus overwriting any changes made via formA.


Regarding Recordsets:

The way I picture it in my head is that a recordset is basically a snippet of a table, taking out only what you need, via an sql statement. That is then like a minitable, basically a snapshot of the table.

Is there a actual link between the recordset and the table after the recordset is created?


From what you have said, am I to understand that if simply went
Code:
with rst
    .Edit
    .field("FirstName") = "Frank"
    .Update
End with

That woud update the table?
 
On the load of formB, I create a recordset from tableA with just one record. When formA opens, its recordsource is set to the same record.
Here is where you are getting a bit confused. A form's recordsource can be changed dynamically, and the change is immediate, but I don't believe the recordsource can be set to the actual recordset that you create (e.g. "rst").
If changes are made to formA, that changes tableA.
Only if the form is bound to a tabledef, not a recordset.
What i meant was, can i dump the recordset I initially created, onto tableA, thus overwriting any changes made via formA.
Yes you can, but I'm wondering why you want to do it. Can't you use the Undo action or something to get this stuff accomplished? Or is that out of the question because of all the opening, closing and shifting between forms that you are doing?
Regarding Recordsets:

The way I picture it in my head is that a recordset is basically a snippet of a table, taking out only what you need, via an sql statement.
Almost. A recordset is a piece of any tabledef. I like to think of it as the "shell" of the table's definition and structure.
Is there a actual link between the recordset and the table after the recordset is created?
NO, not in an official sense.
if simply went
Code:
with rst
    .Edit
    .field("FirstName") = "Frank"
    .Update
End with

That woud update the table?
Change .field("FirstName") to !FirstName and you'll be fine with that. Recordsets are located in collections, just like forms (forms!formname!object), reports, etc..., so you need to reference the collection, and then the specific object:
Code:
rst!FirstName
 
Or is that out of the question because of all the opening, closing and shifting between forms that you are doing?

Bingo.

I should have made this more clear, the forms recordsource is being set by a SQL statement that depends on which form opened it (there are 2 places that can open this particular form) The recordset i'm talking about is made in the original form.

So...
How do I do this magical recordset dump that will make all my dreams come true
 
Last edited:
also, do recordsets close when a sub ends. Even if it is declared globally?
 
Recordsets don't properly close when a sub ends, and globablly declared ones need to be cleaned up as well.

rs.Close
Set rs = Nothing

Do that for each recordset you want to close and destroy. Skip the Set rs = Nothing to leave a recordset instantiated but in a closed state. That way, you can do this:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open blah blah blah
.
.
.
rs.Close

(and then elsewhere in the same sub, or the same project if global):

rs.Open blah blah blah
.
.
.
rs.Close

(and then when you're all done)

rs.Close
Set rs = Nothing
 
I should have made this more clear, the forms recordsource is being set by a SQL statement that depends on which form opened it (there are 2 places that can open this particular form) The recordset i'm talking about is made in the original form.
OK Zaeed, it's been 10 posts so far, and we haven't gotten anywhere, so let me say this:

Just tell me (as simple as you can) what you want. I already know the list from your first post. And, I can already read this:
How do I do this magical recordset dump that will make all my dreams come true
...and I must say that what you've said about the recordset is not very "comprehendable" to a programmer. Instead of going back through this post and trying to decipher all of this, can you draw me a picture with notations from Adobe or something? I only ask because I still think that you are making this too darn complicated.

And, about the recordsets, one thing that Moniker did not mention was that the closing of a recordset automatically deletes it from the recordsets collection, but does not destroy it. That is what the SET statement is for.
 
Ok, i'm attatching the db so you can have a look at it. I'm going to step you through this.


Select a username
bob is the password

First place to observe this in action is 'New Change Request'
Click the field 'Secondary Locations' and you will see the locations form open. The second area you can see in use is if you click 'User Management' on the main page and go to new user.



If you go back to the main page and click 'All Changes' and then click View on one of them, you should see a filled out form. (note to make changes you may need to change to a different user due to privileges)
If you click the secondary locations field again and make a change, then hit save on that form, you will be taken back to the Change form. If you now click cancel, this will close the form and take you back to the main menu. Open that same change up again, and you will see that the changes you made to the Secondary Location field are still there, even though you clicked cancle on the Change form.

Now go to the code for the Chane form and scroll down till you see the big commented bit i've left for you explaining the rest.


Sorry for my posts being hard to follow.

Regarding recordsets closing.

Is it possible to keep one alive and intact (meaning it retains the table information still) once a sub has ended?
 

Attachments

Regarding recordsets closing.

Is it possible to keep one alive and intact (meaning it retains the table information still) once a sub has ended?
I'm not sure about that Zaeed. But, I doubt it.

I'll take a look at your file and get back to you, OK?
 
Have you had a look at it yet adam?
 
no problem, I appreciate you taking the time to help :)
 
Zaeed,

First, let me tell you the truth: I don't have the time to sift through all of your code. You've got a lot, but you're not even close to the volume that was used to create Windows, so keep trying. :D :D :D

Now, to address the comment blocks regarding recordsets: First thing, explain this in words:
Code:
            With rstSecLoc
                For Each fld1 In .Fields
                    If fld1.Value = -1 Then
                        If locations = "" Then
                            locations = fld1.name
                        Else
                            locations = locations & ", " & fld1.name
                        End If
                    End If
                Next
            End With
        End If
        Me.RecordSource = strSQL
        Me.frm_Change_SecLoc = locations
        
        Me.Requery
Second, according to your comments, all of your code currently works fine. Does it? Is the only problem left the one that we have been talking about?

I am hesitant to tell you that an array would be fine here, because the code volume is already pretty large. But, I think you should do it.

If everything works fine for you, throw whatever you have into a dynamic array, and then retrieve it at whatever code point you want. If you don't know how to do this, let me know and I will write a block for you. Have you looked at the "randomizing array values" sample yet? The coding standards are in there too...

From the time I had to look at your file, I don't really know where you are pulling the data from to get it into the recordset, but it doesn't matter. You can just use GetRows here, and hopefully that will help you in the way that you need!
 
Ok, explaining that block in words:

1) Recordset is set to record from tbl_Secondary_Location for the given LocationID

2) Since this record is made up of yes/no fields, I am cycling through the record looking for fields that have value of -1 (yes), when if finds one, it takes the fields name (the location) and adds that to a string. At the end of the loop, the string looks like this:
LocationA, LocationB, LocationC
_________________________________

Yes, the issue that i'm addressing here is that I want to be able to load the contents of rstSecLoc back into the Secondary_Location table, in the event of the form being cancled. This can't be done with .Undo since the locations are not being alterd on this form. When the Change form loads, it builds rstSecLoc with the current information, I want to keep that intact, as a snapshot of the Secondary_Location record so that changes can be overwritten.
________________________________________

Would the array approach be faster than using temp tables?

________________________________________

I don't understand where you mean to use GetRows()

_________________________________________

There were more people working on Windows :D
 
Would the array approach be faster than using temp tables?
YES!! Because, you're not creating anything physical, although you will be using a little bit of memory with the variant, but who cares about that? It won't be big.
I don't understand where you mean to use GetRows()
GetRows is an action, not a function. It does exactly what it says. :) Did you look at my array thread in the repository?

In very simple terms, here's what it does:
Code:
dim var as variant

strSQL = A statement that you have already built.

rs = currentdb.openrecordset(strSQL, dynaset)
  rs.movelast
  rs.movefirst

[color=red]var = rs.getrows[/color]
The array called var now has every piece of data from your recordset in it. Now, do what you want with it. Do you need help with the next step here?
 
ahhhhh i see what you mean now...

cheers adam
 

Users who are viewing this thread

Back
Top Bottom