Current record for a recordset not bound to a form

I am using Access 2003 and WinXP.

I have the following recordset:

Dim csql As String

csql = "SELECT valuation.* " _
& " FROM Valuation " _
& " INNER JOIN " _
& " (ParcelBlock INNER JOIN ParcelBlockValuation ON
ParcelBlock.ParcelBlockId = ParcelBlockValuation.ParcelBlockId) " _
& " ON Valuation.ValuationId = ParcelBlockValuation.ValuationId " _
& " WHERE (((ParcelBlock.Parcel) = '" & Me.cmbParcel.Value & "') "
_
& " And ((ParcelBlock.Block) = '" & Me.cmbBlock.Value & "')) " _
& " ORDER BY Valuation.DateOfValuation desc"

Set regValuation = CurrentDb.OpenRecordset(csql)

Sometimes the query gives more than one record so I let the user move
to next and previous records using regValuation.MoveNext and
regValuation.MovePrevious code.

How do I get the number of the current record? Example: if the query
gives 5 records I would like to know if I am on record 1, 2, 3, 4, or
5.

There is something called CurrentRecord, but it seems to be only for
recordsets bound to forms.

Do I have to use a manual counter or is there a better way?

Regards,

Jan Nordgreen

I think again!, do you?
http://simpler-solutions.net/pmachin...thinkagain.php

re: Current record for a recordset not bound to a form

damezumari wrote:
[color=blue]
> I am using Access 2003 and WinXP.
>
> I have the following recordset:
>
> Dim csql As String
>
> csql = "SELECT valuation.* " _
> & " FROM Valuation " _
> & " INNER JOIN " _
> & " (ParcelBlock INNER JOIN ParcelBlockValuation ON
> ParcelBlock.ParcelBlockId = ParcelBlockValuation.ParcelBlockId) " _
> & " ON Valuation.ValuationId = ParcelBlockValuation.ValuationId " _
> & " WHERE (((ParcelBlock.Parcel) = '" & Me.cmbParcel.Value & "') "
> _
> & " And ((ParcelBlock.Block) = '" & Me.cmbBlock.Value & "')) " _
> & " ORDER BY Valuation.DateOfValuation desc"
>
> Set regValuation = CurrentDb.OpenRecordset(csql)
>
> Sometimes the query gives more than one record so I let the user move
> to next and previous records using regValuation.MoveNext and
> regValuation.MovePrevious code.
>
> How do I get the number of the current record? Example: if the query
> gives 5 records I would like to know if I am on record 1, 2, 3, 4, or
> 5.
>
> There is something called CurrentRecord, but it seems to be only for
> recordsets bound to forms.
>
> Do I have to use a manual counter or is there a better way?
>
> Regards,
>
> Jan Nordgreen
>
> I think again!, do you?
> http://simpler-solutions.net/pmachin...thinkagain.php
>[/color]

Maybe AbsolutePosition will help.

re: Current record for a recordset not bound to a form

Thanks!

regValuation.AbsolutePosition + 1

did the job.

Regards,

Jan Nordgreen

re: Current record for a recordset not bound to a form

damezumari wrote:
[color=blue]
> Thanks!
>
> regValuation.AbsolutePosition + 1
>
> did the job.
>
> Regards,
>
> Jan Nordgreen
>[/color]
It would have been nice if MS had named the property RecordPosition
instead of AbsolutePosition. With MS's current help system, one would
be nearly guaranteed they'd never find or guess AbsolutePosition.

re: Current record for a recordset not bound to a form

Exactly!

So I am grateful for the Google groups and people who help a fellow
programmer. :)

re: Current record for a recordset not bound to a form

damezumari wrote:[color=blue]
> Exactly!
>
> So I am grateful for the Google groups and people who help a fellow
> programmer. :)
>[/color]
If it weren't for newsgroups, most major comapanies would be bankruipt.

re: Current record for a recordset not bound to a form

Probably very true. Nice observation!