recordset - incorrect number of records returning

[Steven Cheng]

i have a base table (transactioncodes) which has 4 fields (transactionno
(duplicates allowed, indexed, integer type), description, default code, and
search string).

i have declared two variables, temprs and rs1 which call upon
transactioncodes twice for different reasons:
1. set rs1 = db.openrecordset("Select * from
Transactioncodes",dbOpensnapshot) used at the beginning of the module and
left until the module closes.
2. set temprs = db.openrecordset("Select * from Transactioncodes WHERE
transactionno = " & x) which is called upon to look for only those records
that satisfy the x value passed onto it (integer type). this is created in
the middle of the procedure.

i have tested the sqlstring used for temprs and it appears to be fine.
however, in certain instances where a ceran transaction code appears multiple
times, temprs should return those records but only returns it once.

is this having to do with the fact that i transactioncodes called as a
snapshot previously?

 

[George Nicholson]

but only returns it once.

Are you basing that statement on the value of temprs.Recordcount? Did you
use temprs.MoveLast before testing Recordcount?

Per the Help entry for Recordcount (DAO):

**************
The RecordCount property doesn't indicate how many records are contained in
a dynaset-, snapshot-, or forward-only-type Recordset object until all
records have been accessed. Once the last record has been accessed, the
RecordCount property indicates the total number of undeleted records in the
Recordset or TableDef object. To force the last record to be accessed, use
the MoveLast method on the Recordset object.
**************

 

[Steven Cheng]

George, indeed it did. Thanks.

Are you basing that statement on the value of temprs.Recordcount? Did you
use temprs.MoveLast before testing Recordcount?

Per the Help entry for Recordcount (DAO):

**************
The RecordCount property doesn't indicate how many records are contained in
a dynaset-, snapshot-, or forward-only-type Recordset object until all
records have been accessed. Once the last record has been accessed, the
RecordCount property indicates the total number of undeleted records in the
Recordset or TableDef object. To force the last record to be accessed, use
the MoveLast method on the Recordset object.
**************