Recordset confusion

[Gerry Abbott]

Hi all,

I having some confusing effects with recordsets in a recent project.

I created several recordsets, each set with the same number of records, and
related with an index value.
I create a table and add the index value and a value/s from each recordset
in turn, into a temporary table, which I used to create a report. I created
this with DAO objects, and it worked fine. I use iteration, and the
..movenext command to get the next values from the recordsets.

I then deployed into a users machine with access 2002, (which had dao 3.6
referenced, along with VB for applications, and Access 10 object library)
Now the results of the recordset appeared be in incorrect order, as if the
recordsets were not ordered in the same way as they had been on the
developer machine.
I then then removed the DAO references in all the objects and found that all
the record sets were then sorted correctly again!

I expected that by making an explicit reference to the DAO objects, and
assuming that the library is referenced on the user machine, that the
results would at least be consistent?
Is there another factor which I might have missed? Both developer and user
machines have up to date service packs.

Since I generally develop on older versions and deploy sometimes in later
versions of Access, its very important that I understand the factors which
might lead to inconsistencies at deployment stage.

Im sure there are threads on this topic, and if anyone has any comments, or
references to relevant threads, they would be most welcome.
Gerry Abbott

[Allen Browne]

No, the order cannot be assumed to be consistent.

Think of a table as a bucket. You throw records in, but they are not ordered
unless you specify the order you want to retrieve them. Things like
compacting the database or using the table as a linked table are likely to
change the retrieval order if you do not specify the order you want. (In
practice, Access tends to use the primary key as the default order for
queries, but anything can happen in reports.)

So, add an AutoNumber field, or a date/time field or something that lets you
specify the order you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ft*****************@news.indigo.ie...

Hi all,

I having some confusing effects with recordsets in a recent project.

I created several recordsets, each set with the same number of records, and related with an index value.
I create a table and add the index value and a value/s from each recordset
in turn, into a temporary table, which I used to create a report. I created this with DAO objects, and it worked fine. I use iteration, and the
.movenext command to get the next values from the recordsets.

I then deployed into a users machine with access 2002, (which had dao 3.6
referenced, along with VB for applications, and Access 10 object library)
Now the results of the recordset appeared be in incorrect order, as if the
recordsets were not ordered in the same way as they had been on the
developer machine.
I then then removed the DAO references in all the objects and found that all the record sets were then sorted correctly again!

I expected that by making an explicit reference to the DAO objects, and
assuming that the library is referenced on the user machine, that the
results would at least be consistent?
Is there another factor which I might have missed? Both developer and user
machines have up to date service packs.

Since I generally develop on older versions and deploy sometimes in later
versions of Access, its very important that I understand the factors which
might lead to inconsistencies at deployment stage.

Im sure there are threads on this topic, and if anyone has any comments, or references to relevant threads, they would be most welcome.

[Gerry Abbott]

Thanks Allen,

What I'm doing is creating a qryDef from a parameter query (which includes
my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

Then I'm moving through the recordset with the move statement.

Is there anywhere in this process where the records can become un'ordered,
and if so,
how should I go about getting it back into order. ?


example
----------------------------------------------------------------------------
-----------------
set db = CurrentDb
set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY qryDepts.deptId")

with myQryDef
.parameter(0) = something
.parameter(1)= something else
Set myRecordSet= .OpenRecordSet
end with

with myRecordset.
.movefirst
for i =1 to .recordCount
......get the record values and do something with
them..............
.moveNext
next i
end with
----------------------------------------------------------------------------
-----------------

Gerry Abbott


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

No, the order cannot be assumed to be consistent.

Think of a table as a bucket. You throw records in, but they are not ordered unless you specify the order you want to retrieve them. Things like
compacting the database or using the table as a linked table are likely to
change the retrieval order if you do not specify the order you want. (In
practice, Access tends to use the primary key as the default order for
queries, but anything can happen in reports.)

So, add an AutoNumber field, or a date/time field or something that lets you specify the order you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ft*****************@news.indigo.ie...

Hi all,

I having some confusing effects with recordsets in a recent project.

I created several recordsets, each set with the same number of records,

and

related with an index value.
I create a table and add the index value and a value/s from each recordset in turn, into a temporary table, which I used to create a report. I

created

this with DAO objects, and it worked fine. I use iteration, and the
.movenext command to get the next values from the recordsets.

I then deployed into a users machine with access 2002, (which had dao 3.6 referenced, along with VB for applications, and Access 10 object library) Now the results of the recordset appeared be in incorrect order, as if the recordsets were not ordered in the same way as they had been on the
developer machine.
I then then removed the DAO references in all the objects and found that

all

the record sets were then sorted correctly again!

I expected that by making an explicit reference to the DAO objects, and
assuming that the library is referenced on the user machine, that the
results would at least be consistent?
Is there another factor which I might have missed? Both developer and user machines have up to date service packs.

Since I generally develop on older versions and deploy sometimes in later versions of Access, its very important that I understand the factors which might lead to inconsistencies at deployment stage.

Im sure there are threads on this topic, and if anyone has any comments,

or

references to relevant threads, they would be most welcome.

[Allen Browne]

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...

Thanks Allen,

What I'm doing is creating a qryDef from a parameter query (which includes
my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

Then I'm moving through the recordset with the move statement.

Is there anywhere in this process where the records can become un'ordered,
and if so,
how should I go about getting it back into order. ?


example
-------------------------------------------------------------------------- -- -----------------
set db = CurrentDb
set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY qryDepts.deptId")
with myQryDef
.parameter(0) = something
.parameter(1)= something else
Set myRecordSet= .OpenRecordSet
end with

with myRecordset.
.movefirst
for i =1 to .recordCount
......get the record values and do something with
them..............
.moveNext
next i
end with
-------------------------------------------------------------------------- -- -----------------

Gerry Abbott


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

No, the order cannot be assumed to be consistent.

Think of a table as a bucket. You throw records in, but they are not

ordered

unless you specify the order you want to retrieve them. Things like
compacting the database or using the table as a linked table are likely to

change the retrieval order if you do not specify the order you want. (In
practice, Access tends to use the primary key as the default order for
queries, but anything can happen in reports.)

So, add an AutoNumber field, or a date/time field or something that lets

you

specify the order you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ft*****************@news.indigo.ie...

Hi all,

I having some confusing effects with recordsets in a recent project.

I created several recordsets, each set with the same number of records,

and

related with an index value.
I create a table and add the index value and a value/s from each recordset in turn, into a temporary table, which I used to create a report. I

created

this with DAO objects, and it worked fine. I use iteration, and the
.movenext command to get the next values from the recordsets.

I then deployed into a users machine with access 2002, (which had dao 3.6 referenced, along with VB for applications, and Access 10 object library) Now the results of the recordset appeared be in incorrect order, as if the recordsets were not ordered in the same way as they had been on the
developer machine.
I then then removed the DAO references in all the objects and found

that all

the record sets were then sorted correctly again!

I expected that by making an explicit reference to the DAO objects,

and assuming that the library is referenced on the user machine, that the
results would at least be consistent?
Is there another factor which I might have missed? Both developer and

user machines have up to date service packs.

Since I generally develop on older versions and deploy sometimes in later versions of Access, its very important that I understand the factors which might lead to inconsistencies at deployment stage.

Im sure there are threads on this topic, and if anyone has any

comments, or

references to relevant threads, they would be most welcome.

[Gerry Abbott]

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query
based on the table.
The important point is that the records I put into the table must all match
the same Key Index (deptId) value.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...

Thanks Allen,

What I'm doing is creating a qryDef from a parameter query (which includes

my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

Then I'm moving through the recordset with the move statement.

Is there anywhere in this process where the records can become un'ordered, and if so,
how should I go about getting it back into order. ?


example
--------------------------------------------------------------------------
--

-----------------
set db = CurrentDb
set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY

qryDepts.deptId")

with myQryDef
.parameter(0) = something
.parameter(1)= something else
Set myRecordSet= .OpenRecordSet
end with

with myRecordset.
.movefirst
for i =1 to .recordCount
......get the record values and do something with
them..............
.moveNext
next i
end with

--------------------------------------------------------------------------
--

-----------------

Gerry Abbott


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

No, the order cannot be assumed to be consistent.

Think of a table as a bucket. You throw records in, but they are not

ordered

unless you specify the order you want to retrieve them. Things like
compacting the database or using the table as a linked table are likely to change the retrieval order if you do not specify the order you want.

(In practice, Access tends to use the primary key as the default order for
queries, but anything can happen in reports.)

So, add an AutoNumber field, or a date/time field or something that lets

you

specify the order you want.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ft*****************@news.indigo.ie...
> Hi all,
>
> I having some confusing effects with recordsets in a recent project.
>
> I created several recordsets, each set with the same number of records, and
> related with an index value.
> I create a table and add the index value and a value/s from each

recordset

> in turn, into a temporary table, which I used to create a report. I
created
> this with DAO objects, and it worked fine. I use iteration, and the
> .movenext command to get the next values from the recordsets.
>
> I then deployed into a users machine with access 2002, (which had

dao 3.6

> referenced, along with VB for applications, and Access 10 object

library)

> Now the results of the recordset appeared be in incorrect order, as

if the

> recordsets were not ordered in the same way as they had been on the
> developer machine.
> I then then removed the DAO references in all the objects and found that all
> the record sets were then sorted correctly again!
>
> I expected that by making an explicit reference to the DAO objects, and > assuming that the library is referenced on the user machine, that

the > results would at least be consistent?
> Is there another factor which I might have missed? Both developer

and user

> machines have up to date service packs.
>
> Since I generally develop on older versions and deploy sometimes in

later

> versions of Access, its very important that I understand the factors

which

> might lead to inconsistencies at deployment stage.
>
> Im sure there are threads on this topic, and if anyone has any

comments, or
> references to relevant threads, they would be most welcome.

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:GB*****************@news.indigo.ie:

What I'm doing is creating a qryDef from a parameter query (which
includes my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

The ORDER BY of the recordsource of a report is not necessarily
honored in the report. To be sure the report comes out in the
expected order, you must specify sorting and grouping *in* the
report -- you simply can't depend on the order of the recordsource.

I don't really know why this should be, and I've found it
frustrating myself, but it's the way things are. The order of a
report will be reliable only when you set the sorting in the report
definition. This means that it may be a waste of resources to use a
recordsource with its own ORDER BY clause.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Ol*****************@news.indigo.ie:

The report is not a problem, since I populate a temporary table
with the records from several (ordered) recordsets, then run the
report from a query based on the table.
The important point is that the records I put into the table must
all match the same Key Index (deptId) value.

As I said in another post, you can never depend on the order of
printing in a report unless you've explicitly set ordering and/or
grouping options.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

I understand you have an empty temp table. You fill some columns with
recordset 1. You then update other columns with recordset 2, other
columns with recordset 3, etc. You claim all recordsets have same
number of records and are ordered the same. Still you see mis-matches
so it appears the order was lost.

I would debug this by checking the matching key values myself.
Pseudo-code:
while not rsFrom.eof
debug.assert rsTo.DeptID = rsFrom.DeptID
' Update rsTo with values from rsFrom
rsFrom.MoveNext
rsTo.MoveNext
wend

If your code is indeed this simple, you can execute the whole thing in
SQL using an append query and multiple update queries. This would be
much faster.

-Tom.

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query
based on the table.
The important point is that the records I put into the table must all match
the same Key Index (deptId) value.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...

> Thanks Allen,
>
> What I'm doing is creating a qryDef from a parameter query (whichincludes > my ORDER BY index)
> Im setting my qryDef parameter values,
> Then I'm setting my recordset to the qryDef openRecordset object.
>
> Then I'm moving through the recordset with the move statement.
>
> Is there anywhere in this process where the records can becomeun'ordered, > and if so,
> how should I go about getting it back into order. ?
>
>
>
>
> example

--------------------------------------------------------------------------
--

> -----------------
> set db = CurrentDb
> set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY

qryDepts.deptId")

>
> with myQryDef
> .parameter(0) = something
> .parameter(1)= something else
> Set myRecordSet= .OpenRecordSet
> end with
>
> with myRecordset.
> .movefirst
> for i =1 to .recordCount
> ......get the record values and do something with
> them..............
> .moveNext
> next i
> end with

--------------------------------------------------------------------------
--

> -----------------
>
>
>
> Gerry Abbott
>
>
>
>
>
>
>
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:40***********************@per-qv1-newsreader-01.iinet.net.au...
> > No, the order cannot be assumed to be consistent.
> >
> > Think of a table as a bucket. You throw records in, but they are not
> ordered
> > unless you specify the order you want to retrieve them. Things like
> > compacting the database or using the table as a linked table arelikely

to

> > change the retrieval order if you do not specify the order you want.

(In > > practice, Access tends to use the primary key as the default order for
> > queries, but anything can happen in reports.)
> >
> > So, add an AutoNumber field, or a date/time field or something thatlets > you
> > specify the order you want.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Gerry Abbott" <pl****@ask.ie> wrote in message
> > news:Ft*****************@news.indigo.ie...
> > > Hi all,
> > >
> > > I having some confusing effects with recordsets in a recent project.
> > >
> > > I created several recordsets, each set with the same number of

records,

> > and
> > > related with an index value.
> > > I create a table and add the index value and a value/s from each
> recordset
> > > in turn, into a temporary table, which I used to create a report. I
> > created
> > > this with DAO objects, and it worked fine. I use iteration, and the
> > > .movenext command to get the next values from the recordsets.
> > >
> > > I then deployed into a users machine with access 2002, (which haddao > 3.6
> > > referenced, along with VB for applications, and Access 10 object
> library)
> > > Now the results of the recordset appeared be in incorrect order, asif > the
> > > recordsets were not ordered in the same way as they had been on the
> > > developer machine.
> > > I then then removed the DAO references in all the objects and found

that

> > all
> > > the record sets were then sorted correctly again!
> > >
> > > I expected that by making an explicit reference to the DAO objects,

and

> > > assuming that the library is referenced on the user machine, thatthe > > > results would at least be consistent?
> > > Is there another factor which I might have missed? Both developerand > user
> > > machines have up to date service packs.
> > >
> > > Since I generally develop on older versions and deploy sometimes in
> later
> > > versions of Access, its very important that I understand the factors
> which
> > > might lead to inconsistencies at deployment stage.
> > >
> > > Im sure there are threads on this topic, and if anyone has any

comments,

> > or
> > > references to relevant threads, they would be most welcome.

[Gerry Abbott]

Thanks Tom,
Yes you understand what i'm doing precisely. Same number of records etc
(only 13) . I actually have a number of recordsets open concurrently, and I
populate the table one record at a time (see code below. What I do is a
little more complicated, since I run the code a second time, with different
parameter values, to get year to date figures, which I use to populate a
further set of fields in the same set of records, using the 'edit' method in
place of the addnew )

I have a basic checking code, but this is not fool proof. (your suggested
debug.assert method does not exist in my Acc97)

I have not used append queries before, but what you suggest does sound a lot
simpler. I assumed that append queries can only add records, but not change
fields in existing records, which is what I want to do!

You might help me out here.
Gerry
Sample existing code
-------------------------------------
For i = 1 To rsOpen.RecordCount
SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
'CHECKS FOR
If Int(SumDeptId / 3) <> SumDeptId / 3 Then
MsgBox "Records are not being calculated correctly"
Exit Sub
End If
.AddNew
!deptId = rsOpen!deptId
!Open = rsOpen!Open
!Closed = rsClosed!Closed
!Past = rsPast!Past
!Days = rsClosed!totaldays
.Update
rsOpen.MoveNext
rsClosed.MoveNext
rsPast.MoveNext
Next i
---------------------------------------


"Tom van Stiphout" <no*************@cox.net> wrote in message
news:f2********************************@4ax.com...

On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

I understand you have an empty temp table. You fill some columns with
recordset 1. You then update other columns with recordset 2, other
columns with recordset 3, etc. You claim all recordsets have same
number of records and are ordered the same. Still you see mis-matches
so it appears the order was lost.

I would debug this by checking the matching key values myself.
Pseudo-code:
while not rsFrom.eof
debug.assert rsTo.DeptID = rsFrom.DeptID
' Update rsTo with values from rsFrom
rsFrom.MoveNext
rsTo.MoveNext
wend

If your code is indeed this simple, you can execute the whole thing in
SQL using an append query and multiple update queries. This would be
much faster.

-Tom.

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query

based on the table.
The important point is that the records I put into the table must all matchthe same Key Index (deptId) value.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...
> Thanks Allen,
>
> What I'm doing is creating a qryDef from a parameter query (which

includes

> my ORDER BY index)
> Im setting my qryDef parameter values,
> Then I'm setting my recordset to the qryDef openRecordset object.
>
> Then I'm moving through the recordset with the move statement.
>
> Is there anywhere in this process where the records can become

un'ordered,

> and if so,
> how should I go about getting it back into order. ?
>
>
>
>
> example

-------------------------------------------------------------------------

-

--
> -----------------
> set db = CurrentDb
> set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY
qryDepts.deptId")
>
> with myQryDef
> .parameter(0) = something
> .parameter(1)= something else
> Set myRecordSet= .OpenRecordSet
> end with
>
> with myRecordset.
> .movefirst
> for i =1 to .recordCount
> ......get the record values and do something with
> them..............
> .moveNext
> next i
> end with

-------------------------------------------------------------------------

-

--
> -----------------
>
>
>
> Gerry Abbott
>
>
>
>
>
>
>
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:40***********************@per-qv1-newsreader-01.iinet.net.au...
> > No, the order cannot be assumed to be consistent.
> >
> > Think of a table as a bucket. You throw records in, but they are not > ordered
> > unless you specify the order you want to retrieve them. Things like
> > compacting the database or using the table as a linked table are

likely

to
> > change the retrieval order if you do not specify the order you want.(In

> > practice, Access tends to use the primary key as the default order

for > > queries, but anything can happen in reports.)
> >
> > So, add an AutoNumber field, or a date/time field or something that

lets

> you
> > specify the order you want.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Gerry Abbott" <pl****@ask.ie> wrote in message
> > news:Ft*****************@news.indigo.ie...
> > > Hi all,
> > >
> > > I having some confusing effects with recordsets in a recent project. > > >
> > > I created several recordsets, each set with the same number of
records,
> > and
> > > related with an index value.
> > > I create a table and add the index value and a value/s from each
> recordset
> > > in turn, into a temporary table, which I used to create a report. I > > created
> > > this with DAO objects, and it worked fine. I use iteration, and the > > > .movenext command to get the next values from the recordsets.
> > >
> > > I then deployed into a users machine with access 2002, (which had

dao

> 3.6
> > > referenced, along with VB for applications, and Access 10 object
> library)
> > > Now the results of the recordset appeared be in incorrect order, asif

> the
> > > recordsets were not ordered in the same way as they had been on

the > > > developer machine.
> > > I then then removed the DAO references in all the objects and found that
> > all
> > > the record sets were then sorted correctly again!
> > >
> > > I expected that by making an explicit reference to the DAO objects, and
> > > assuming that the library is referenced on the user machine, that

the

> > > results would at least be consistent?
> > > Is there another factor which I might have missed? Both developer

and

> user
> > > machines have up to date service packs.
> > >
> > > Since I generally develop on older versions and deploy sometimes in > later
> > > versions of Access, its very important that I understand the factors > which
> > > might lead to inconsistencies at deployment stage.
> > >
> > > Im sure there are threads on this topic, and if anyone has any
comments,
> > or
> > > references to relevant threads, they would be most welcome.

[Gerry Abbott]

Thanks David,
Once i've got the records in the temporary table correct, the order on the
printed report is trivial.

Gerry

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Ol*****************@news.indigo.ie:

The report is not a problem, since I populate a temporary table
with the records from several (ordered) recordsets, then run the
report from a query based on the table.
The important point is that the records I put into the table must
all match the same Key Index (deptId) value.

As I said in another post, you can never depend on the order of
printing in a report unless you've explicitly set ordering and/or
grouping options.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Xj*****************@news.indigo.ie:

I actually have a number of recordsets open concurrently. . .

What, exactly, are you doing that can't be done without a temp table
by using SQL?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Gerry Abbott]

Sorry David, I just replied to your last previous post, and should serve as
a reply here.

Regards,

Gerry

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:GB*****************@news.indigo.ie:

What I'm doing is creating a qryDef from a parameter query (which
includes my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

The ORDER BY of the recordsource of a report is not necessarily
honored in the report. To be sure the report comes out in the
expected order, you must specify sorting and grouping *in* the
report -- you simply can't depend on the order of the recordsource.

I don't really know why this should be, and I've found it
frustrating myself, but it's the way things are. The order of a
report will be reliable only when you set the sorting in the report
definition. This means that it may be a waste of resources to use a
recordsource with its own ORDER BY clause.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Tom van Stiphout]

On Mon, 5 Jul 2004 23:08:55 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

Rather than debug.assert you can use:
if rsTo.DeptID <> rsFrom.DeptID then Msgbox "Very bad condition
encountered!"

To work with queries, this pseudo-code would work:
RunQuery "delete * from mytemptable" ' clear out temp table.
RunQuery "insert into mytemptable select DeptID, Field2, Field3 from
Table1" ' insert rows, and first few columns.
RunQuery "update mytemptable inner join Table2 on
Table2.DeptID=mytemptable.DeptID set Field4=Table2.Field2,
Field5=Table2.Field3" ' update some other columns

If you have a simple situation, you can create a query that pulls all
columns for the temp table, and add the data with one INSERT query:
myquery looks like this:
select Table1.DeptID, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5 from Table1 inner join Table2 on
Table1.DeptID=Table2.DeptID
and the insert query:
insert into mytemptable select * from myquery

David's point is well taken: if things are that simple, you don't need
a temp table:
myreport.recordsource = myquery

Typically the temp table route is only taken when it's just too
complicated to create a single query to pull the data.

-Tom.

Thanks Tom,
Yes you understand what i'm doing precisely. Same number of records etc
(only 13) . I actually have a number of recordsets open concurrently, and I
populate the table one record at a time (see code below. What I do is a
little more complicated, since I run the code a second time, with different
parameter values, to get year to date figures, which I use to populate a
further set of fields in the same set of records, using the 'edit' method in
place of the addnew )

I have a basic checking code, but this is not fool proof. (your suggested
debug.assert method does not exist in my Acc97)

I have not used append queries before, but what you suggest does sound a lot
simpler. I assumed that append queries can only add records, but not change
fields in existing records, which is what I want to do!

You might help me out here.
Gerry
Sample existing code
-------------------------------------
For i = 1 To rsOpen.RecordCount
SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
'CHECKS FOR
If Int(SumDeptId / 3) <> SumDeptId / 3 Then
MsgBox "Records are not being calculated correctly"
Exit Sub
End If
.AddNew
!deptId = rsOpen!deptId
!Open = rsOpen!Open
!Closed = rsClosed!Closed
!Past = rsPast!Past
!Days = rsClosed!totaldays
.Update
rsOpen.MoveNext
rsClosed.MoveNext
rsPast.MoveNext
Next i
---------------------------------------


"Tom van Stiphout" <no*************@cox.net> wrote in message
news:f2********************************@4ax.com.. .

On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

I understand you have an empty temp table. You fill some columns with
recordset 1. You then update other columns with recordset 2, other
columns with recordset 3, etc. You claim all recordsets have same
number of records and are ordered the same. Still you see mis-matches
so it appears the order was lost.

I would debug this by checking the matching key values myself.
Pseudo-code:
while not rsFrom.eof
debug.assert rsTo.DeptID = rsFrom.DeptID
' Update rsTo with values from rsFrom
rsFrom.MoveNext
rsTo.MoveNext
wend

If your code is indeed this simple, you can execute the whole thing in
SQL using an append query and multiple update queries. This would be
much faster.

-Tom.

>Thanks again,
>The report is not a problem, since I populate a temporary table with the
>records from several (ordered) recordsets, then run the report from aquery >based on the table.
>The important point is that the records I put into the table must allmatch >the same Key Index (deptId) value.
>
>
>"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>news:40***********************@per-qv1-newsreader-01.iinet.net.au...
>> The records will retain their order while they are open.
>>
>> The report is a different story.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Gerry Abbott" <pl****@ask.ie> wrote in message
>> news:GB*****************@news.indigo.ie...
>> > Thanks Allen,
>> >
>> > What I'm doing is creating a qryDef from a parameter query (which
>includes
>> > my ORDER BY index)
>> > Im setting my qryDef parameter values,
>> > Then I'm setting my recordset to the qryDef openRecordset object.
>> >
>> > Then I'm moving through the recordset with the move statement.
>> >
>> > Is there anywhere in this process where the records can become
>un'ordered,
>> > and if so,
>> > how should I go about getting it back into order. ?
>> >
>> >
>> >
>> >
>> > example
>>

-------------------------------------------------------------------------- >> --
>> > -----------------
>> > set db = CurrentDb
>> > set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY
>> qryDepts.deptId")
>> >
>> > with myQryDef
>> > .parameter(0) = something
>> > .parameter(1)= something else
>> > Set myRecordSet= .OpenRecordSet
>> > end with
>> >
>> > with myRecordset.
>> > .movefirst
>> > for i =1 to .recordCount
>> > ......get the record values and do something with
>> > them..............
>> > .moveNext
>> > next i
>> > end with
>>

-------------------------------------------------------------------------- >> --
>> > -----------------
>> >
>> >
>> >
>> > Gerry Abbott
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>> > news:40***********************@per-qv1-newsreader-01.iinet.net.au...
>> > > No, the order cannot be assumed to be consistent.
>> > >
>> > > Think of a table as a bucket. You throw records in, but they arenot >> > ordered
>> > > unless you specify the order you want to retrieve them. Things like
>> > > compacting the database or using the table as a linked table are
>likely
>> to
>> > > change the retrieval order if you do not specify the order youwant. >(In
>> > > practice, Access tends to use the primary key as the default orderfor >> > > queries, but anything can happen in reports.)
>> > >
>> > > So, add an AutoNumber field, or a date/time field or something that
>lets
>> > you
>> > > specify the order you want.
>> > >
>> > > --
>> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
>> > > Tips for Access users - http://allenbrowne.com/tips.html
>> > > Reply to group, rather than allenbrowne at mvps dot org.
>> > >
>> > > "Gerry Abbott" <pl****@ask.ie> wrote in message
>> > > news:Ft*****************@news.indigo.ie...
>> > > > Hi all,
>> > > >
>> > > > I having some confusing effects with recordsets in a recentproject. >> > > >
>> > > > I created several recordsets, each set with the same number of
>> records,
>> > > and
>> > > > related with an index value.
>> > > > I create a table and add the index value and a value/s from each
>> > recordset
>> > > > in turn, into a temporary table, which I used to create a report.I >> > > created
>> > > > this with DAO objects, and it worked fine. I use iteration, andthe >> > > > .movenext command to get the next values from the recordsets.
>> > > >
>> > > > I then deployed into a users machine with access 2002, (which had
>dao
>> > 3.6
>> > > > referenced, along with VB for applications, and Access 10 object
>> > library)
>> > > > Now the results of the recordset appeared be in incorrect order,as >if
>> > the
>> > > > recordsets were not ordered in the same way as they had been onthe >> > > > developer machine.
>> > > > I then then removed the DAO references in all the objects andfound >> that
>> > > all
>> > > > the record sets were then sorted correctly again!
>> > > >
>> > > > I expected that by making an explicit reference to the DAOobjects, >> and
>> > > > assuming that the library is referenced on the user machine, that
>the
>> > > > results would at least be consistent?
>> > > > Is there another factor which I might have missed? Both developer
>and
>> > user
>> > > > machines have up to date service packs.
>> > > >
>> > > > Since I generally develop on older versions and deploy sometimesin >> > later
>> > > > versions of Access, its very important that I understand thefactors >> > which
>> > > > might lead to inconsistencies at deployment stage.
>> > > >
>> > > > Im sure there are threads on this topic, and if anyone has any
>> comments,
>> > > or
>> > > > references to relevant threads, they would be most welcome.
>>
>>
>

[Gerry Abbott]

David,
I diden't say that what i'm doing cannot be done with sql. Its just not the
way i've done it
My reply to Tom VanStiphout, is a reasonable description of what i'm doing.
Gerry Abbott
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Xj*****************@news.indigo.ie:

I actually have a number of recordsets open concurrently. . .

What, exactly, are you doing that can't be done without a temp table
by using SQL?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Allen Browne]

Okay: you populate a temp table by using AddNew on several different
recordsets. All records in the temp table have the same deptId value,
presumably some kind of foreign key.

You then close the orignal recordsets(?), and open another one based on a
parameter query. If this one sorts by deptId (the same for all values), then
you have not specified any sort order for this recordset.

You talk about setting an index as well. I think you can only specify an
Index for a dbOpenTable type recordset, so that should not be possible for
the dbOpenDynaset type recordset you get when you OpenRecordset using a
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ol*****************@news.indigo.ie...

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query based on the table.
The important point is that the records I put into the table must all match the same Key Index (deptId) value.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...

Thanks Allen,

What I'm doing is creating a qryDef from a parameter query (which includes my ORDER BY index)
Im setting my qryDef parameter values,
Then I'm setting my recordset to the qryDef openRecordset object.

Then I'm moving through the recordset with the move statement.

Is there anywhere in this process where the records can become un'ordered, and if so,
how should I go about getting it back into order. ?


example

--------------------------------------------------------------------------

--

-----------------
set db = CurrentDb
set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY

qryDepts.deptId")

with myQryDef
.parameter(0) = something
.parameter(1)= something else
Set myRecordSet= .OpenRecordSet
end with

with myRecordset.
.movefirst
for i =1 to .recordCount
......get the record values and do something with
them..............
.moveNext
next i
end with

--------------------------------------------------------------------------

--

-----------------

Gerry Abbott


"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...
> No, the order cannot be assumed to be consistent.
>
> Think of a table as a bucket. You throw records in, but they are not
ordered
> unless you specify the order you want to retrieve them. Things like
> compacting the database or using the table as a linked table are likely

to

> change the retrieval order if you do not specify the order you want. (In > practice, Access tends to use the primary key as the default order for > queries, but anything can happen in reports.)
>
> So, add an AutoNumber field, or a date/time field or something that lets you
> specify the order you want.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Gerry Abbott" <pl****@ask.ie> wrote in message
> news:Ft*****************@news.indigo.ie...
> > Hi all,
> >
> > I having some confusing effects with recordsets in a recent project. > >
> > I created several recordsets, each set with the same number of

records,

> and
> > related with an index value.
> > I create a table and add the index value and a value/s from each
recordset
> > in turn, into a temporary table, which I used to create a report. I > created
> > this with DAO objects, and it worked fine. I use iteration, and the > > .movenext command to get the next values from the recordsets.
> >
> > I then deployed into a users machine with access 2002, (which had dao 3.6
> > referenced, along with VB for applications, and Access 10 object
library)
> > Now the results of the recordset appeared be in incorrect order, as if

the
> > recordsets were not ordered in the same way as they had been on

the > > developer machine.
> > I then then removed the DAO references in all the objects and found that

> all
> > the record sets were then sorted correctly again!
> >
> > I expected that by making an explicit reference to the DAO

objects, and

> > assuming that the library is referenced on the user machine, that

the > > results would at least be consistent?
> > Is there another factor which I might have missed? Both developer and user
> > machines have up to date service packs.
> >
> > Since I generally develop on older versions and deploy sometimes

in later
> > versions of Access, its very important that I understand the factors which
> > might lead to inconsistencies at deployment stage.
> >
> > Im sure there are threads on this topic, and if anyone has any

comments,

> or
> > references to relevant threads, they would be most welcome.

[Gerry Abbott]

Thanks again Tom,
Will try out the query option. in place of the recordsets. But I think I
still have to use the temp table, as Im using three parameter queries with
two parameters in each, and each query is derived for a query derived from a
query. I use these queries twice with different parameter values each time.

Gerry
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:qf********************************@4ax.com...

On Mon, 5 Jul 2004 23:08:55 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

Rather than debug.assert you can use:
if rsTo.DeptID <> rsFrom.DeptID then Msgbox "Very bad condition
encountered!"

To work with queries, this pseudo-code would work:
RunQuery "delete * from mytemptable" ' clear out temp table.
RunQuery "insert into mytemptable select DeptID, Field2, Field3 from
Table1" ' insert rows, and first few columns.
RunQuery "update mytemptable inner join Table2 on
Table2.DeptID=mytemptable.DeptID set Field4=Table2.Field2,
Field5=Table2.Field3" ' update some other columns

If you have a simple situation, you can create a query that pulls all
columns for the temp table, and add the data with one INSERT query:
myquery looks like this:
select Table1.DeptID, Table1.Field2, Table1.Field3, Table2.Field4,
Table2.Field5 from Table1 inner join Table2 on
Table1.DeptID=Table2.DeptID
and the insert query:
insert into mytemptable select * from myquery

David's point is well taken: if things are that simple, you don't need
a temp table:
myreport.recordsource = myquery

Typically the temp table route is only taken when it's just too
complicated to create a single query to pull the data.

-Tom.

Thanks Tom,
Yes you understand what i'm doing precisely. Same number of records etc
(only 13) . I actually have a number of recordsets open concurrently, and I

populate the table one record at a time (see code below. What I do is a
little more complicated, since I run the code a second time, with differentparameter values, to get year to date figures, which I use to populate a
further set of fields in the same set of records, using the 'edit' method inplace of the addnew )

I have a basic checking code, but this is not fool proof. (your suggested
debug.assert method does not exist in my Acc97)

I have not used append queries before, but what you suggest does sound a lotsimpler. I assumed that append queries can only add records, but not changefields in existing records, which is what I want to do!

You might help me out here.
Gerry
Sample existing code
-------------------------------------
For i = 1 To rsOpen.RecordCount
SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
'CHECKS FOR
If Int(SumDeptId / 3) <> SumDeptId / 3 Then
MsgBox "Records are not being calculated correctly"
Exit Sub
End If
.AddNew
!deptId = rsOpen!deptId
!Open = rsOpen!Open
!Closed = rsClosed!Closed
!Past = rsPast!Past
!Days = rsClosed!totaldays
.Update
rsOpen.MoveNext
rsClosed.MoveNext
rsPast.MoveNext
Next i
---------------------------------------


"Tom van Stiphout" <no*************@cox.net> wrote in message
news:f2********************************@4ax.com.. .

On Mon, 5 Jul 2004 17:29:57 +0100, "Gerry Abbott" <pl****@ask.ie>
wrote:

I understand you have an empty temp table. You fill some columns with
recordset 1. You then update other columns with recordset 2, other
columns with recordset 3, etc. You claim all recordsets have same
number of records and are ordered the same. Still you see mis-matches
so it appears the order was lost.

I would debug this by checking the matching key values myself.
Pseudo-code:
while not rsFrom.eof
debug.assert rsTo.DeptID = rsFrom.DeptID
' Update rsTo with values from rsFrom
rsFrom.MoveNext
rsTo.MoveNext
wend

If your code is indeed this simple, you can execute the whole thing in
SQL using an append query and multiple update queries. This would be
much faster.

-Tom.

>Thanks again,
>The report is not a problem, since I populate a temporary table with the >records from several (ordered) recordsets, then run the report from a

query

>based on the table.
>The important point is that the records I put into the table must all

match

>the same Key Index (deptId) value.
>
>
>"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>news:40***********************@per-qv1-newsreader-01.iinet.net.au...
>> The records will retain their order while they are open.
>>
>> The report is a different story.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Gerry Abbott" <pl****@ask.ie> wrote in message
>> news:GB*****************@news.indigo.ie...
>> > Thanks Allen,
>> >
>> > What I'm doing is creating a qryDef from a parameter query (which
>includes
>> > my ORDER BY index)
>> > Im setting my qryDef parameter values,
>> > Then I'm setting my recordset to the qryDef openRecordset object.
>> >
>> > Then I'm moving through the recordset with the move statement.
>> >
>> > Is there anywhere in this process where the records can become
>un'ordered,
>> > and if so,
>> > how should I go about getting it back into order. ?
>> >
>> >
>> >
>> >
>> > example
>>
------------------------------------------------------------------------ --

>> --
>> > -----------------
>> > set db = CurrentDb
>> > set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY
>> qryDepts.deptId")
>> >
>> > with myQryDef
>> > .parameter(0) = something
>> > .parameter(1)= something else
>> > Set myRecordSet= .OpenRecordSet
>> > end with
>> >
>> > with myRecordset.
>> > .movefirst
>> > for i =1 to .recordCount
>> > ......get the record values and do something with
>> > them..............
>> > .moveNext
>> > next i
>> > end with
>>
------------------------------------------------------------------------

--

>> --
>> > -----------------
>> >
>> >
>> >
>> > Gerry Abbott
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
>> >

news:40***********************@per-qv1-newsreader-01.iinet.net.au... >> > > No, the order cannot be assumed to be consistent.
>> > >
>> > > Think of a table as a bucket. You throw records in, but they are

not

>> > ordered
>> > > unless you specify the order you want to retrieve them. Things like >> > > compacting the database or using the table as a linked table are
>likely
>> to
>> > > change the retrieval order if you do not specify the order you

want.

>(In
>> > > practice, Access tends to use the primary key as the default orderfor

>> > > queries, but anything can happen in reports.)
>> > >
>> > > So, add an AutoNumber field, or a date/time field or something

that >lets
>> > you
>> > > specify the order you want.
>> > >
>> > > --
>> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
>> > > Tips for Access users - http://allenbrowne.com/tips.html
>> > > Reply to group, rather than allenbrowne at mvps dot org.
>> > >
>> > > "Gerry Abbott" <pl****@ask.ie> wrote in message
>> > > news:Ft*****************@news.indigo.ie...
>> > > > Hi all,
>> > > >
>> > > > I having some confusing effects with recordsets in a recent

project.

>> > > >
>> > > > I created several recordsets, each set with the same number of
>> records,
>> > > and
>> > > > related with an index value.
>> > > > I create a table and add the index value and a value/s from each >> > recordset
>> > > > in turn, into a temporary table, which I used to create a report.I

>> > > created
>> > > > this with DAO objects, and it worked fine. I use iteration,

andthe

>> > > > .movenext command to get the next values from the recordsets.
>> > > >
>> > > > I then deployed into a users machine with access 2002, (which

had >dao
>> > 3.6
>> > > > referenced, along with VB for applications, and Access 10 object >> > library)
>> > > > Now the results of the recordset appeared be in incorrect order,as

>if
>> > the
>> > > > recordsets were not ordered in the same way as they had been

onthe

>> > > > developer machine.
>> > > > I then then removed the DAO references in all the objects and

found

>> that
>> > > all
>> > > > the record sets were then sorted correctly again!
>> > > >
>> > > > I expected that by making an explicit reference to the DAO

objects,

>> and
>> > > > assuming that the library is referenced on the user machine,

that >the
>> > > > results would at least be consistent?
>> > > > Is there another factor which I might have missed? Both developer >and
>> > user
>> > > > machines have up to date service packs.
>> > > >
>> > > > Since I generally develop on older versions and deploy

sometimesin

>> > later
>> > > > versions of Access, its very important that I understand the

factors

>> > which
>> > > > might lead to inconsistencies at deployment stage.
>> > > >
>> > > > Im sure there are threads on this topic, and if anyone has any
>> comments,
>> > > or
>> > > > references to relevant threads, they would be most welcome.
>>
>>
>

[Gerry Abbott]

Allen,
One recordset populates empty tempoaray table, the other three recordsets
are where I get the values to put into my table. The three recordsets are
derived from qrydefs, with passed parameter values, which are derived from
three parameter action queries. They all have the same number of records,
and a common 'foreign key', the DeptId. I pass values from these recordsets
to the table recordset, one record at a time, until the table has got all
the records. Then I close all the reocrdsets. The sorting is done in the
Action queries on which the qryDefs are based.

My comment about setting and index is misleading. What I was trying to
explain is that all the action queries have the DeptId, which is a Key filed
from the Department table. This is the field which relates all the fields
from the different queries. I probably could join the three queries, and
changed the parameter descriptions to be different, giving me one query.
This would ensure that all the correct values resided in the correct record.
However since Ive gotta run the query again, with different values for the
parameters, and get the results into different fields in the temp table,
matching the DeptID, i still have to be sure that the order of the records
is correct in the recordsets.

Gerry

Full code is here.
http://homepage.eircom.net/~fish44/recordsets.txt

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

Okay: you populate a temp table by using AddNew on several different
recordsets. All records in the temp table have the same deptId value,
presumably some kind of foreign key.

You then close the orignal recordsets(?), and open another one based on a
parameter query. If this one sorts by deptId (the same for all values), then you have not specified any sort order for this recordset.

You talk about setting an index as well. I think you can only specify an
Index for a dbOpenTable type recordset, so that should not be possible for
the dbOpenDynaset type recordset you get when you OpenRecordset using a
QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Gerry Abbott" <pl****@ask.ie> wrote in message
news:Ol*****************@news.indigo.ie...

Thanks again,
The report is not a problem, since I populate a temporary table with the
records from several (ordered) recordsets, then run the report from a query

based on the table.
The important point is that the records I put into the table must all

match

the same Key Index (deptId) value.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:40***********************@per-qv1-newsreader-01.iinet.net.au...

The records will retain their order while they are open.

The report is a different story.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gerry Abbott" <pl****@ask.ie> wrote in message
news:GB*****************@news.indigo.ie...
> Thanks Allen,
>
> What I'm doing is creating a qryDef from a parameter query (which

includes

> my ORDER BY index)
> Im setting my qryDef parameter values,
> Then I'm setting my recordset to the qryDef openRecordset object.
>
> Then I'm moving through the recordset with the move statement.
>
> Is there anywhere in this process where the records can become

un'ordered,

> and if so,
> how should I go about getting it back into order. ?
>
>
>
>
> example

--------------------------------------------------------------------------

--
> -----------------
> set db = CurrentDb
> set myQryDef = db.qryDefs("SELECT * FROM qryDepts ORDER BY
qryDepts.deptId")
>
> with myQryDef
> .parameter(0) = something
> .parameter(1)= something else
> Set myRecordSet= .OpenRecordSet
> end with
>
> with myRecordset.
> .movefirst
> for i =1 to .recordCount
> ......get the record values and do something with
> them..............
> .moveNext
> next i
> end with

--------------------------------------------------------------------------

--
> -----------------
>
>
>
> Gerry Abbott
>
>
>
>
>
>
>
>
> "Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
> news:40***********************@per-qv1-newsreader-01.iinet.net.au...
> > No, the order cannot be assumed to be consistent.
> >
> > Think of a table as a bucket. You throw records in, but they are not > ordered
> > unless you specify the order you want to retrieve them. Things like > > compacting the database or using the table as a linked table are

likely

to
> > change the retrieval order if you do not specify the order you

want.

(In

> > practice, Access tends to use the primary key as the default order for > > queries, but anything can happen in reports.)
> >
> > So, add an AutoNumber field, or a date/time field or something

that lets

> you
> > specify the order you want.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> >
> > "Gerry Abbott" <pl****@ask.ie> wrote in message
> > news:Ft*****************@news.indigo.ie...
> > > Hi all,
> > >
> > > I having some confusing effects with recordsets in a recent project. > > >
> > > I created several recordsets, each set with the same number of
records,
> > and
> > > related with an index value.
> > > I create a table and add the index value and a value/s from each
> recordset
> > > in turn, into a temporary table, which I used to create a

report. I > > created
> > > this with DAO objects, and it worked fine. I use iteration, and the > > > .movenext command to get the next values from the recordsets.
> > >
> > > I then deployed into a users machine with access 2002, (which

had dao

> 3.6
> > > referenced, along with VB for applications, and Access 10 object
> library)
> > > Now the results of the recordset appeared be in incorrect order, as

if

> the
> > > recordsets were not ordered in the same way as they had been on

the > > > developer machine.
> > > I then then removed the DAO references in all the objects and found that
> > all
> > > the record sets were then sorted correctly again!
> > >
> > > I expected that by making an explicit reference to the DAO objects, and
> > > assuming that the library is referenced on the user machine,

that the

> > > results would at least be consistent?
> > > Is there another factor which I might have missed? Both

developer and

> user
> > > machines have up to date service packs.
> > >
> > > Since I generally develop on older versions and deploy sometimes

in > later
> > > versions of Access, its very important that I understand the factors > which
> > > might lead to inconsistencies at deployment stage.
> > >
> > > Im sure there are threads on this topic, and if anyone has any
comments,
> > or
> > > references to relevant threads, they would be most welcome.

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:om*****************@news.indigo.ie:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Ol*****************@news.indigo.ie:

> The report is not a problem, since I populate a temporary table
> with the records from several (ordered) recordsets, then run
> the report from a query based on the table.
> The important point is that the records I put into the table
> must all match the same Key Index (deptId) value.

As I said in another post, you can never depend on the order of
printing in a report unless you've explicitly set ordering and/or
grouping options.

Once i've got the records in the temporary table correct, the
order on the printed report is trivial.

Yes, it is, indeed, trivial to set sorting and grouping on a report,
but I don't think that's what you meant.

The point of my message: the order in the table and the order in the
report's Recordsource is not reliably maintained when the report
prints, unless you explicitly set sorting and grouping.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:QU*****************@news.indigo.ie:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Xj*****************@news.indigo.ie:

> I actually have a number of recordsets open concurrently. . .

What, exactly, are you doing that can't be done without a temp
table by using SQL?

I diden't say that what i'm doing cannot be done with sql. Its
just not the way i've done it
My reply to Tom VanStiphout, is a reasonable description of what
i'm doing.

Well, the point of my question is that it looks to me that you've
introduced a lot of irrelevancies into the discussion. It doesn't
really matter how the records get into the table that you're
printing.

Second, you can't assume that because the records appear to be in
the correct order when you view the table that they actually *are*
in the correct order. You really don't know how they are actually
stored -- you only know how datasheet view presents them.

I repeat: set the sorting and grouping in the report and you'll be
able to control the order of printing reliably.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:qf********************************@4ax.com:

David's point is well taken: if things are that simple, you don't
need a temp table:
myreport.recordsource = myquery

Typically the temp table route is only taken when it's just too
complicated to create a single query to pull the data.

Actually, another case where a temp table is useful is when you want
to display the data in multiple UI objects (reports/forms). I also
often use temp tables for query-by-form results, with a checkbox to
allow selection/deselection for further operations (export,
printing, etc.). In all those cases, the same recordset could,
indeed, be constructed in SQL alone, but the temp table has
important uses that SQL by itself cannot provide.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Pi*****************@news.indigo.ie:

Will try out the query option. in place of the recordsets. But I
think I
still have to use the temp table, as Im using three parameter
queries with two parameters in each, and each query is derived for
a query derived from a query. I use these queries twice with
different parameter values each time.

I don't use parameter queries much, myself.

I'm not sure I understand a situation where the resultset is derived
from the same base tables/queries where you couldn't get the same
result by simply have multiple rows of criteria (as displayed in the
QBE). In that case, you'd just make your parameters distinct for
each row.

Of course, there are plenty of other ways to filter a recordset that
don't involve parameters.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Gerry Abbott]

Thank you David,
Your point is well made.
Gerry Abbott

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:om*****************@news.indigo.ie:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Ol*****************@news.indigo.ie:

> The report is not a problem, since I populate a temporary table
> with the records from several (ordered) recordsets, then run
> the report from a query based on the table.
> The important point is that the records I put into the table
> must all match the same Key Index (deptId) value.

As I said in another post, you can never depend on the order of
printing in a report unless you've explicitly set ordering and/or
grouping options.

Once i've got the records in the temporary table correct, the
order on the printed report is trivial.

Yes, it is, indeed, trivial to set sorting and grouping on a report,
but I don't think that's what you meant.

The point of my message: the order in the table and the order in the
report's Recordsource is not reliably maintained when the report
prints, unless you explicitly set sorting and grouping.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Gerry Abbott]

David,
I apologise if the point in the original message was not clear. And I agree
that the discussion has drifted from the original question, which is not
related to merits of how I'm achieving my objective (althought those points
have helped me simplify my code, and consider alternative methods) The
problem I have is also not with the order of the records in my temporary
table, or Report for that matter. The problem is the order in which
individual fields in the table are populated, as the field values are
derived from different recordsets. Since the information in the recordsets
is related to a common key field, its imperative that the field values are
placed in the correct 'record' each time. Once in the temporary table or a
single recordset for that matter, I'm not concerned as to the order the
records appear on the report, since there's only thirteen records, and
sorting them is a trivial matter in the report.

Since the discussion, I have simiplified the queries, and now have a single
query running from three, each of which is derived from one. So im only
using a single qryDef object, and pass 6 parameters. It would be very
elegant to have all this in code, and no stand alone queries, but I fear
that that is beyond my present capability in SQL, if it is possible.

Gerry

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.78...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:QU*****************@news.indigo.ie:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@24.168.1 28.86...

"Gerry Abbott" <pl****@ask.ie> wrote in
news:Xj*****************@news.indigo.ie:

> I actually have a number of recordsets open concurrently. . .

What, exactly, are you doing that can't be done without a temp
table by using SQL?

I diden't say that what i'm doing cannot be done with sql. Its
just not the way i've done it
My reply to Tom VanStiphout, is a reasonable description of what
i'm doing.

Well, the point of my question is that it looks to me that you've
introduced a lot of irrelevancies into the discussion. It doesn't
really matter how the records get into the table that you're
printing.

Second, you can't assume that because the records appear to be in
the correct order when you view the table that they actually *are*
in the correct order. You really don't know how they are actually
stored -- you only know how datasheet view presents them.

I repeat: set the sorting and grouping in the report and you'll be
able to control the order of printing reliably.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc