Referring to a recordset in an SQL statement.

[Guest]

I want to archive records in a manpower table if any field is changed. When I open the form for the table I run:
General section:
Public Mdb As Database, SnapShotTemp As Recordset

Private Sub Form_Load()
Set Mdb = CurrentDb
Set SnapShotTemp = Mdb.OpenRecordset("Associate Info", dbOpenSnapshot)

Later when I close the form I want to compare (find unmatched) records between the table when the form was opened and the table when the form was closed. I created a string for my SQL statement which refers to SnapShotTemp

Dim Temptable1 As Recordset, OriginalRecs As QueryDef, ORTemptable As Recordset, SQLstringUnmatched1 As String
SQLstringUnmatched1 = "SELECT DISTINCTROW SnapShotTemp.AssOrigID, SnapShotTemp.[Associate Name - Last],........
Set OriginalRecs = Mdb.CreateQueryDef("", SQLstringUnmatched1)
Set ORTemptable = OriginalRecs.OpenRecordset()

Running this gives me the error:
Run-time error 3078
....cannot find the input table or query 'SnapShotTemp'. Make sure it exists and.....
And debug takes me to the last line of code I have provided.

How do I refer to a recordset in this SQL statement???

 

[TC]

Not to answer your question, but: I have found by experiment that a snapshot
is not necessarrily populated with all of the relevant records, at the time
you actually open it. So if you plan to stay with the snapshot method, I
recommend you do a .Movelast on the recordset, immediately after you have
opened it, to ensure that it is fully populated before the user starts to
make changes.

HTH,
TC

I want to archive records in a manpower table if any field is changed.

When I open the form for the table I run:

General section:
Public Mdb As Database, SnapShotTemp As Recordset

Private Sub Form_Load()
Set Mdb = CurrentDb
Set SnapShotTemp = Mdb.OpenRecordset("Associate Info", dbOpenSnapshot)

Later when I close the form I want to compare (find unmatched) records

between the table when the form was opened and the table when the form was
closed. I created a string for my SQL statement which refers to SnapShotTemp

Dim Temptable1 As Recordset, OriginalRecs As QueryDef, ORTemptable As

Recordset, SQLstringUnmatched1 As String

SQLstringUnmatched1 = "SELECT DISTINCTROW SnapShotTemp.AssOrigID,

SnapShotTemp.[Associate Name - Last],........

 

[Guest]

Thanks.
That I can do and will do. As an aside, this is just a department manpower list with slightly less than 500 records, but I will do that MOVELAST method.

To answer the question; do you have any suggestions for referring to the SnapShot recordset, that I take and then keep on a Public level in memory, in my SQL statement? I know that it wants me to refer to a Table or a Query, but I want to just refer to this recordset(variable) name.

----- TC wrote: -----

Not to answer your question, but: I have found by experiment that a snapshot
is not necessarrily populated with all of the relevant records, at the time
you actually open it. So if you plan to stay with the snapshot method, I
recommend you do a .Movelast on the recordset, immediately after you have
opened it, to ensure that it is fully populated before the user starts to
make changes.

HTH,
TC

 

[Dirk Goldgar]

I want to archive records in a manpower table if any field is
changed. When I open the form for the table I run:
General section:
Public Mdb As Database, SnapShotTemp As Recordset

Private Sub Form_Load()
Set Mdb = CurrentDb
Set SnapShotTemp = Mdb.OpenRecordset("Associate Info", dbOpenSnapshot)

Later when I close the form I want to compare (find unmatched)
records between the table when the form was opened and the table when
the form was closed. I created a string for my SQL statement which
refers to SnapShotTemp

Dim Temptable1 As Recordset, OriginalRecs As QueryDef, ORTemptable As
Recordset, SQLstringUnmatched1 As String
SQLstringUnmatched1 = "SELECT DISTINCTROW SnapShotTemp.AssOrigID,
SnapShotTemp.[Associate Name - Last],........
Set OriginalRecs = Mdb.CreateQueryDef("", SQLstringUnmatched1)
Set ORTemptable = OriginalRecs.OpenRecordset()

Running this gives me the error:
Run-time error 3078
...cannot find the input table or query 'SnapShotTemp'. Make sure it
exists and.....
And debug takes me to the last line of code I have provided.

How do I refer to a recordset in this SQL statement???

You can't refer to a recordset in an SQL statement, so you'll have to
find a different approach. I suppose you could open a second recordset
on the table as it stands as the form is closed, and then compare the
two recordsets by looping through their contents. However, it may be
simpler to just copy the table to a backup table when you open the form,
and then use SQL to compare that backup table to the live table after
the form is closed. Another possibility is to put code in the form's
BeforeUpdate event that records the fact of the update and the old/new
values of the fields, with similar code in the Delete or AfterDelConfirm
event to handle deletions.

 

[Guest]

In response to Dirk's suggestion of
You can't refer to a recordset in an SQL statement, so you'll have t
find a different approach. I suppose you could open a second recordse
on the table as it stands as the form is closed, and then compare th
two recordsets by looping through their contents. However, it may b
simpler to just copy the table to a backup table when you open the form
and then use SQL to compare that backup table to the live table afte
the form is closed. Another possibility is to put code in the form'
BeforeUpdate event that records the fact of the update and the old/ne
values of the fields, with similar code in the Delete or AfterDelConfir
event to handle deletions

I went that route this morning. When I open the form I run a query to create a backup Temp table with all the records (essentially my SnapShot recordset). Then when I close I run 1 query to find unmatched in the Temp table and then use code to add them to my History table
Set ORTemptable = Mdb.OpenRecordset("AI Close02q (Unmatched)", dbOpenSnapshot
If ORTemptable.BOF And ORTemptable.EOF Then GoTo Finish

Set Temptable1 = Mdb.OpenRecordset("Associate Info History", dbOpenDynaset
ORTemptable.MoveFirs
Do Until ORTemptable.EO
With Temptable
.AddNe
!AssOrigID = ORTemptable.AssOrigID: ![Associate Name - Last] = ORTemptable.[Associate Name - Last]: ![Associate Name - First] = ORTemptable.[Associate Name - First
![Modified Date] = ORTemptable.[Modified Date
.Updat
End Wit
ORTemptable.MoveNex
Loo
Set Temptable1 = Nothin

I then run a query to find the unmatched in the Active table and then attempt to do 2 things
1. Find the unmatched records and DELETE them from the Active tabl
2. ADD the unmatched records back in to the Active table so that they are assigned new ID'

Set MRTemptable = Mdb.OpenRecordset("AI Close03q (Unmatched)", dbOpenSnapshot
Set Temptable1 = Mdb.OpenRecordset("Associate Info", dbOpenDynaset
MRTemptable.MoveFirs
Do Until MRTemptable.EO
Temptable1.FindFirst "[Associate #] = " & MRTemptable.[Associate #
With Temptable
.Delet
.MoveLas
.AddNe
![Associate Name - Last] = MRTemptable.[Associate Name - Last]: ![Associate Name - First] = MRTemptable.[Associate Name - First
.Updat
End Wit
MRTemptable.MoveNex
Loo
Set Temptable1 = Nothin

The problem with this now is that it will find and delete the right record (assuming only 1 modified for testing) but then it won't go past ![Associate Name - Last] = because it is treating the word "Name" as a variable which is holding the name of the table
I cannot figure this one out no matter how I syntax it
Why does it not understand that I am referring to a field, which does exist in the table, called ![Associate Name - Last] ??

 

[Dirk Goldgar]

In response to Dirk's suggestion of:
You can't refer to a recordset in an SQL statement, so you'll
have to find a different approach. I suppose you could open a
second recordset on the table as it stands as the form is
closed, and then compare the two recordsets by looping through
their contents. However, it may be simpler to just copy the
table to a backup table when you open the form, and then use SQL
to compare that backup table to the live table after the form is
closed. Another possibility is to put code in the form's
BeforeUpdate event that records the fact of the update and the
old/new values of the fields, with similar code in the Delete or
AfterDelConfirm event to handle deletions.

I went that route this morning. When I open the form I run a query to
create a backup Temp table with all the records (essentially my
SnapShot recordset). Then when I close I run 1 query to find
unmatched in the Temp table and then use code to add them to my
History table: Set ORTemptable = Mdb.OpenRecordset("AI Close02q
(Unmatched)", dbOpenSnapshot)
If ORTemptable.BOF And ORTemptable.EOF Then GoTo Finish2

Set Temptable1 = Mdb.OpenRecordset("Associate Info History",
dbOpenDynaset)
ORTemptable.MoveFirst
Do Until ORTemptable.EOF
With Temptable1
.AddNew
!AssOrigID = ORTemptable.AssOrigID: ![Associate Name - Last] =
ORTemptable.[Associate Name - Last]: ![Associate Name - First] =
ORTemptable.[Associate Name - First] ![Modified Date] =
ORTemptable.[Modified Date] .Update
End With
ORTemptable.MoveNext
Loop
Set Temptable1 = Nothing

I then run a query to find the unmatched in the Active table and then
attempt to do 2 things:
1. Find the unmatched records and DELETE them from the Active table
2. ADD the unmatched records back in to the Active table so that they
are assigned new ID's

Set MRTemptable = Mdb.OpenRecordset("AI Close03q (Unmatched)",
dbOpenSnapshot)
Set Temptable1 = Mdb.OpenRecordset("Associate Info", dbOpenDynaset)
MRTemptable.MoveFirst
Do Until MRTemptable.EOF
Temptable1.FindFirst "[Associate #] = " & MRTemptable.[Associate #]
With Temptable1
.Delete
.MoveLast
.AddNew
![Associate Name - Last] = MRTemptable.[Associate Name - Last]:
![Associate Name - First] = MRTemptable.[Associate Name - First]
.Update
End With
MRTemptable.MoveNext
Loop
Set Temptable1 = Nothing

The problem with this now is that it will find and delete the right
record (assuming only 1 modified for testing) but then it won't go
past ![Associate Name - Last] = because it is treating the word
"Name" as a variable which is holding the name of the table!
I cannot figure this one out no matter how I syntax it.
Why does it not understand that I am referring to a field, which does
exist in the table, called ![Associate Name - Last] ???

You can't use the dot notation (e.g., MRTemptable.[Associate Name -
Last]) to refer to the fields of a recordset. You must use the bang:

MRTemptable![Associate Name - Last]

Note, by the way, that there is no need to call .MoveLast before calling
..AddNew. Also, I haven't analyzed your logic, so I don't know if it's
possible that your statement

Temptable1.FindFirst "[Associate #] = " & MRTemptable.[Associate #]

might not find a match. It may be a good idea to check for that,
though, before blithely deleting the record.

 

[TC]

Hi Hunter

I see the other respondent is helping you here.

Cheers,
TC

Thanks.
That I can do and will do. As an aside, this is just a department manpower

list with slightly less than 500 records, but I will do that MOVELAST
method.

To answer the question; do you have any suggestions for referring to the

SnapShot recordset, that I take and then keep on a Public level in memory,
in my SQL statement? I know that it wants me to refer to a Table or a Query,
but I want to just refer to this recordset(variable) name.

 

[TC]

(snip)

Set MRTemptable = Mdb.OpenRecordset("AI Close03q (Unmatched)", dbOpenSnapshot)
Set Temptable1 = Mdb.OpenRecordset("Associate Info", dbOpenDynaset)
MRTemptable.MoveFirst
Do Until MRTemptable.EOF
Temptable1.FindFirst "[Associate #] = " & MRTemptable.[Associate #]
With Temptable1
.Delete
.MoveLast
.AddNew

etc.


Hi Hunter

Just butting-in here, in case that MoveLast came from my earlier suggestion.
That suggestion was, that a snapshot type recordset (like MRTemptable) is
not always fully populated, just by opening it. I suggested that you might
like to do a MoveLast on >that< recordset - immediately after opening it -
to ensure that it is fully populated.

As Dirk said, there is no point to the MoveLast on the dynaset recordset,
just before the AddNew.

HTH,
TC

 

[Guest]

Excellent! The !Bang worked! I have no idea why the first section of code doesn't need it, but it does the trick in the second section. Great! I'm done!
Thanks Dudes!
Hunter
(e-mail address removed)