More than one Table

[rberry]

I have an Old table TABLE 1 in an application which has a check fields CHK1,
CHK2 in it.

I have recently upgraded the application so that the check field are now a
series of records in a sub-form TABLE 2.

QUESTION
I want to increment through TABLE 1 one record at a time, then run a
conditional statement to see if CHK1, CHK2 is yes in each respective check
field and if it is I want to open TABLE 2 and add record to TABLE 2 so that
reflects the respective CHKn.

My question is can 2 recordsets be open simultaneously and if so how do I
swap between the 2 so that my VBA application knows which table I am working
on.

Basically I want to open TABLE 1.
Run a condition statement.
IF met save 2 fields to 2 variables.
Swap to TABLE 2
Add new record containg 2 field with the 2 variables
SWAP back to TABLE 1
Increment record till end of file.

Any ideas


Don't have any problems opening up

 

[John Spencer]

Yes, two (or more) recordsets can be open at one time.

In your case, I don't see a good reason for having the two open.

I would suggest that you use two append queries and do the job in two queries.

Without more details on your table structure, it is a little tough to tell you
an exact SQL (query) to use.

If you insist on doing it your way in VBA then a generic idea of the code
might be something like

Dim rst1 as DAO.Recordset
Dim rst2 as DAO.Recordset
Dim V1 as Variant, V2 as Variant

'Open the source recordset
Set rst1 = Currentdb().OpenRecordset ("SELECT Field1, Field2 FROM Table1")
'Open a recordset with no records
Set rst2 = Currentdb().OpenRecordset _
("SELECT FieldA, FieldB FROM Table2 WHERE 1=2")

While Rst1.EOF = False
V1 = rst1.Fields(0)
V2 = rst1.Fields(1)
'Do you testing to see what to do

'If things test correctly then add a record to rst2
WITH rst2
.AddNew
.Field("FieldA") = V1
.Field("FieldB") = V2
.Update
END With

rst1.MoveNext
WEND


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have an Old table TABLE 1 in an application which has a check fields CHK1,
CHK2 in it.

I have recently upgraded the application so that the check field are now a
series of records in a sub-form TABLE 2.

QUESTION
I want to increment through TABLE 1 one record at a time, then run a
conditional statement to see if CHK1, CHK2 is yes in each respective check
field and if it is I want to open TABLE 2 and add record to TABLE 2 so that
reflects the respective CHKn.

My question is can 2 recordsets be open simultaneously and if so how do I
swap between the 2 so that my VBA application knows which table I am working
on.

Basically I want to open TABLE 1.
Run a condition statement.
IF met save 2 fields to 2 variables.
Swap to TABLE 2
Add new record containg 2 field with the 2 variables
SWAP back to TABLE 1
Increment record till end of file.

Yes you can do that using this kind of code:

Dim rsFrom As Recordset. rsTo As Recordset
Set rsFrom = db.OpenRecordset(fromtable,...
Set rsTo = db.OpenRecordset(totable. ...
Do Until rsFrom.EOF
If rsFrom!chk1 And rsFrom!chk2 Then
rsTo.AddNew
rsTo!thisfield = rsFrom!thisfield
rsTo!thatfield = rsFrom!thatfield
. . .
rsTo.Update
End If
rsFrom.MoveNext
Loop

But, it would probably be easier/faster/safer to use an
append query to do all the records in one shot:

INSERT INTO totable (thisfield, thatfield, ...)
SELECT fromtable.thisfield, fromtable.thatfield, ...
FROM fromtable LEFT JOIN totable
ON fromtable.keyfield = totable.keyfield
WHERE totable.keyfield Is Null
And fromtable.chk1 And fromtable.chk2

 

[rberry]

Marshall
Many thanks for this it was spot on.

Yes you can do that using this kind of code:

Dim rsFrom As Recordset. rsTo As Recordset
Set rsFrom = db.OpenRecordset(fromtable,...
Set rsTo = db.OpenRecordset(totable. ...
Do Until rsFrom.EOF
If rsFrom!chk1 And rsFrom!chk2 Then
rsTo.AddNew
rsTo!thisfield = rsFrom!thisfield
rsTo!thatfield = rsFrom!thatfield
. . .
rsTo.Update
End If
rsFrom.MoveNext
Loop

But, it would probably be easier/faster/safer to use an
append query to do all the records in one shot:

INSERT INTO totable (thisfield, thatfield, ...)
SELECT fromtable.thisfield, fromtable.thatfield, ...
FROM fromtable LEFT JOIN totable
ON fromtable.keyfield = totable.keyfield
WHERE totable.keyfield Is Null
And fromtable.chk1 And fromtable.chk2