OpenRecordset not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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...
 
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...
 
Back
Top