OpenRecordset not working

L

LeAnn

I'm using Access 97 and trying to write a function. I am calling the
function from a query and am passing several arguments to the function - one
of which is a key field (MSET_ID). In the code I have:
------------------
Dim db as DAO.Database
Dim rst as DAO.Recordset

Set db = CurrentDb()

some other code that works fine...

Set rst = db.OpenRecordset("Select * from qryRunTime Where MSET_ID = " &
lngMSET) 'key field passed to function

rst = Nothing
db = Nothing
------------------
Using the debug.print rst.RecordCount I discovered that the recordset only
has 1 record when it should have several based on the key field. The
qryRunTime is a simple select query. Where am I going wrong with this?

I guessing this isn't the most efficient process. For each record in the
query, it needs to open a recordset. Is this aspect ok?

Thanks
 
R

Rick Brandt

LeAnn said:
I'm using Access 97 and trying to write a function. I am calling the
function from a query and am passing several arguments to the
function - one of which is a key field (MSET_ID). In the code I have: [snip]
Set rst = db.OpenRecordset("Select * from qryRunTime Where MSET_ID =
" & lngMSET) 'key field passed to function [snip]
Using the debug.print rst.RecordCount I discovered that the recordset
only has 1 record when it should have several based on the key field.
Click to expand...

RecordSet.RecordCount is not accurate until you do a MoveLast. Prior to
that it only show the number of records that have been accessed (so far) in
the RecordSet.
 
L

LeAnn

Thanks Rick! Should have seen that. My recordset is accurate but now I have
a second struggle. I need to access previous records' fields. I've tried
many approaches. In the recordset I have a CycleNumber field (not unique
because there could be several records with CycleNumber = 2 (or 3 etc).
There is also a field called CycleSeconds which calculates the cycle time.
For all the cyclenumber = 2 the CycleSecondss is the same etc. If it is
cycle 3 I need to grab both CycleSeconds from cycle 1 and 2 records in order
to get a cumulative time. I wrote a select case statement but I'm struggling
with Cases 3 +. Since I don't know for any given set how many cycle 2s or 1s
etc. I can't navigate to a specific record. I tried the filter property:

rst.Filter = "CycleNumber < ' " & intCycle & " ' "
rst.MoveFirst
lngPrevSeconds = rst![CycleSeconds]
rst.MoveLast
lngPrev2Seconds = rst![CycleSeconds]

For lngPrevSeconds I do get cycle 1 CycleSeconds but for lngPrev2Seconds I
am getting cycle 3 seconds instead of cycle 2.

Once you filter a recordset, how do you remove the filter?
rst.showallrecords is not?

Hope this makes sense. I know there must be a better way, I'm just trying
to SOMETHING to work. :)

Rick Brandt said:
LeAnn said:
I'm using Access 97 and trying to write a function. I am calling the
function from a query and am passing several arguments to the
function - one of which is a key field (MSET_ID). In the code I have: [snip]
Set rst = db.OpenRecordset("Select * from qryRunTime Where MSET_ID =
" & lngMSET) 'key field passed to function [snip]
Using the debug.print rst.RecordCount I discovered that the recordset
only has 1 record when it should have several based on the key field.
Click to expand...

RecordSet.RecordCount is not accurate until you do a MoveLast. Prior to
that it only show the number of records that have been accessed (so far) in
the RecordSet.
Click to expand...
 
L

LeAnn

Well, I figured out how to use the filter property so I got my Function to
work properly.

LeAnn said:
Thanks Rick! Should have seen that. My recordset is accurate but now I have
a second struggle. I need to access previous records' fields. I've tried
many approaches. In the recordset I have a CycleNumber field (not unique
because there could be several records with CycleNumber = 2 (or 3 etc).
There is also a field called CycleSeconds which calculates the cycle time.
For all the cyclenumber = 2 the CycleSecondss is the same etc. If it is
cycle 3 I need to grab both CycleSeconds from cycle 1 and 2 records in order
to get a cumulative time. I wrote a select case statement but I'm struggling
with Cases 3 +. Since I don't know for any given set how many cycle 2s or 1s
etc. I can't navigate to a specific record. I tried the filter property:

rst.Filter = "CycleNumber < ' " & intCycle & " ' "
rst.MoveFirst
lngPrevSeconds = rst![CycleSeconds]
rst.MoveLast
lngPrev2Seconds = rst![CycleSeconds]

For lngPrevSeconds I do get cycle 1 CycleSeconds but for lngPrev2Seconds I
am getting cycle 3 seconds instead of cycle 2.

Once you filter a recordset, how do you remove the filter?
rst.showallrecords is not?

Hope this makes sense. I know there must be a better way, I'm just trying
to SOMETHING to work. :)

Rick Brandt said:
LeAnn said:
I'm using Access 97 and trying to write a function. I am calling the
function from a query and am passing several arguments to the
function - one of which is a key field (MSET_ID). In the code I have: [snip]
Set rst = db.OpenRecordset("Select * from qryRunTime Where MSET_ID =
" & lngMSET) 'key field passed to function [snip]
Using the debug.print rst.RecordCount I discovered that the recordset
only has 1 record when it should have several based on the key field.
Click to expand...

RecordSet.RecordCount is not accurate until you do a MoveLast. Prior to
that it only show the number of records that have been accessed (so far) in
the RecordSet.
Click to expand...
Click to expand...
 
Top