Recordsets-Old and New

  • Thread starter Thread starter Stu
  • Start date Start date
S

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)
 
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.
 
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
Click to expand...

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)
Click to expand...

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)
 
Wolfgang Kais said:
I have never seen "Me.Dirty = False" before, I always use
If Me.Dirty Then RunCommand acCmdSaveRecord
Click to expand...

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

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

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!
 
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!
Click to expand...

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.
 
Back
Top