Recordsets-Old and New

[Stu]

I am trying to create two recordsets from a single form, one when the form is
first opened and a second after updates (not entry) are entered. The first
recordset, rsOpen, is defined on the form_open event:
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsOpen = CurrentDb.OpenRecordset(sNewVal) ''' get orginal values

The second I would like to define when a Submit button is clicked:
Me.Dirty = False
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsSubmit = CurrentDb.OpenRecordset(sNewVal) ''' get current values

The problem I'm encountering is that both recordsets have the same field
values, i.e., if Dirty = False both recordsets have the new field values. If
Dirty = true then both recordsets have the old field values. Is there a way
to get the values before updates are entered in my recordset "rsOpen" and
capture the field values after updates are entered in my recordset
"rsSubmit"? Kind of a before and after recordsets. (I'm aware of the
"oldValue" property)

 

[Stu]

I think I may have answered this myself. Using the GetRows method I can save
the the rsOpen recordset. Then after the me.Dirty=false I use GetRows again
and save the rsSubmit recordset into a different array. Then I can compare
array values quite easily.

If someone could confirm this method (GetRows), would appreicate since I
have neither used it before.

 

[Wolfgang Kais]

Hello Stu.

I am trying to create two recordsets from a single form, one when the
form is first opened and a second after updates (not entry) are entered.
The first recordset, rsOpen, is defined on the form_open event:
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsOpen = CurrentDb.OpenRecordset(sNewVal) ' get orginal values

The second I would like to define when a Submit button is clicked:
Me.Dirty = False
sNewVal = "Select * from tblClasses where Itemid = " & Me.ItemID
Set rsSubmit = CurrentDb.OpenRecordset(sNewVal) ' get current values

I have never seen "Me.Dirty = False" before, I always use
If Me.Dirty Then RunCommand acCmdSaveRecord

The problem I'm encountering is that both recordsets have the same
field values, i.e., if Dirty = False both recordsets have the new field
values. If Dirty = true then both recordsets have the old field values.
Is there a way to get the values before updates are entered in my
recordset "rsOpen" and capture the field values after updates are
entered in my recordset "rsSubmit"? Kind of a before and after
recordsets. (I'm aware of the "oldValue" property)

The recordsets opened are of type Dynaset. Afaik, dynasets always retrieve
the actual values. Try to open the first recordset as a snapshot:
Set rsOpen = CurrentDb.OpenRecordset(sNewVal, dbOpenSnapshot)

 

[Douglas J. Steele]

I have never seen "Me.Dirty = False" before, I always use
If Me.Dirty Then RunCommand acCmdSaveRecord

Me.Dirty = False is a legitimate way of performing the save. However, it's
better to use

If Me.Dirty Then Me.Dirty = False

 

[David W. Fenton]

I have never seen "Me.Dirty = False" before, I always use
If Me.Dirty Then RunCommand acCmdSaveRecord

You must think the way I do -- when I first encountered Me.Dirty, it
seemed to me that it should be a read-only property, and setting it
to False to save the record seemed counterintuitive.

I got over it!

 

[Wolfgang Kais]

Hello David.

You must think the way I do -- when I first encountered Me.Dirty, it
seemed to me that it should be a read-only property, and setting it
to False to save the record seemed counterintuitive.

I got over it!

You are right. Also, under "hints" in the online help of my Access 2003
installation it states that this property is read-only, although the
description says that is is read/write.
I used PowerShell to find that indeed this property has a get and a set
implementation, so the Dirty property can be written to.