OpenRecordset not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LeAnn - 05 May 2005 18:41 GMT

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

Reply to this Message

Rick Brandt - 05 May 2005 19:06 GMT

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

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.

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

LeAnn - 05 May 2005 20:54 GMT

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. :)

> > 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
[quoted text clipped - 9 lines]
> that it only show the number of records that have been accessed (so far) in
> the RecordSet.

Reply to this Message

LeAnn - 06 May 2005 02:25 GMT

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

> 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
[quoted text clipped - 35 lines]
> > that it only show the number of records that have been accessed (so far) in
> > the RecordSet.

Reply to this Message