What are the benefits of .AbsolutePosition property?

[mcupito]

I came across the .AbsolutePosition property on MS Office Dev Center website and was curious as to its' importance.

I've been active on Bytes primarily as far as VBA/Access forums are concerned and often I see people post Do While loops, yet they don't contain .AbsolutePosition. There is always the same starting point

If .BOF Or .EOF Else

But I never see .AbsolutePosition.

That begs the question - when is a good time to use it, and what are its' uses?

If I'm being too vague - please let me know! Thanks

[ADezii]

I use it primarily to track the progress of a Loop as a percentage of Records processed, as in:

'It is Zero based, so
(rst.AbsolutePosition/rst.RecordCount)

P.S. - Code obviously omitted.

[NeoPa]

It's something that gives you the position of the current record within the recordset.

NB. This is not the relative position of the record in the table, as that is meaningless. It is also not 100% reliable, as other members have noticed. As ADezii has said, it can be useful for determining progress through a recordset.

[ADezii]

@mcupito:
Here is another context for which .AbsolutePosition can be used. I forgot to mention that I also use it to pluck a Random Record from a Recordset, namely generating a Random Number between 1 and .RecordCount then setting .AbsolutePosition to it.

Dim rst As ADODB.Recordset
 
Set rst = New ADODB.Recordset
rst.Open "tblCustomers", CurrentProject.Connection, adOpenKeyset
 
Debug.Print rst.Fields(0).Value
 
'Move approximately halfway into Recordset
rst.AbsolutePosition = 0.5 * rst.RecordCount
Debug.Print rst.Fields(0).Value
 
'Move to the 35th row
rst.AbsolutePosition = 35
Debug.Print rst.Fields(0).Value
 
rst.Close
Set rst = Nothing

[mcupito]

Thanks for the examples, ADezii. Thanks for the reply, NeoPa.

That being said - are there foreseeable problems in this code :

    DoCmd.Hourglass True
    DoCmd.SetWarnings False
        DoCmd.OpenQuery "CheckConfirmVestUpdateQry"
        DoCmd.OpenQuery "CheckConfirmPytUpdateQry"
    DoCmd.Hourglass False
    DoCmd.SetWarnings True
 
    With Recordset
        If .AbsolutePosition = .RecordCount - 1 Then
        'you are on the last record
            DoCmd.GoToRecord , , acFirst
            Me.Requery
            Me!CheckConfirmEntryFrm.Requery
            Me!CheckConfirmVestingFrm.Requery
        Else
        'you are on some other record
            DoCmd.GoToRecord , , acNext
            Me!CheckConfirmEntryFrm.Requery
        End If
    End With

[ADezii]

I do not see a problem as long as:

1. The Recordset supports the AbsolutePosition Property, not all do.
2. You obtain an 'exact' RecordCount by traversing the Recordset, namely:
   

   rst.MoveLast:rst.MoveFirst

3. You do not use the Object Variable Recordset to refer to an actual Recordset as you have in Code Line# 8:
   

   With Recordset
     'code segment
   End With

[mcupito]

So for #3 you're suggesting something like:

Dim rs as DAO.Recordset
rs = (SELECT * FROM Table)

Instead of referring to the Recordset as just a Recordset Object?

[ADezii]

I am referring to the use of the Reserved Word Recordset to refer to an actual Recordset Object.

'Define Recordset rs
With rs
  'Code segment
End With
 

NOT

'Define Recordset Recordset
With Recordset
  'Code segment
End With
 

[NeoPa]

You cannot set a Recordset variable to SQL like that Mark. Even if it did make sense and have quotes around it to make a string, it would still not be valid code.

You need to open a recordset, and possibly pass the SQL string as a parameter.

[mcupito]

Okay, I understand now.
Would this work then, NeoPa?

    Set rs = db.OpenRecordset("SELECT * FROM Table")

[NeoPa]

Fundamentally - yes.

db would have to be set to a valid, open, database. rs would have to be usable as a Recordset variable. Table would have to be a valid table name. "Table" isn't usable without the [] - but that's just the details.

[mcupito]

Got it. Thanks for your time NeoPa and ADezii.