By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

What are the benefits of .AbsolutePosition property?

100+
P: 294
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

Expand|Select|Wrap|Line Numbers
  1. 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
Feb 18 '14 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,597
I use it primarily to track the progress of a Loop as a percentage of Records processed, as in:
Expand|Select|Wrap|Line Numbers
  1. 'It is Zero based, so
  2. (rst.AbsolutePosition/rst.RecordCount)
P.S. - Code obviously omitted.
Feb 18 '14 #2

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 18 '14 #3

ADezii
Expert 5K+
P: 8,597
@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.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As ADODB.Recordset
  2.  
  3. Set rst = New ADODB.Recordset
  4. rst.Open "tblCustomers", CurrentProject.Connection, adOpenKeyset
  5.  
  6. Debug.Print rst.Fields(0).Value
  7.  
  8. 'Move approximately halfway into Recordset
  9. rst.AbsolutePosition = 0.5 * rst.RecordCount
  10. Debug.Print rst.Fields(0).Value
  11.  
  12. 'Move to the 35th row
  13. rst.AbsolutePosition = 35
  14. Debug.Print rst.Fields(0).Value
  15.  
  16. rst.Close
  17. Set rst = Nothing
Feb 18 '14 #4

100+
P: 294
Thanks for the examples, ADezii. Thanks for the reply, NeoPa.

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

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.Hourglass True
  2.     DoCmd.SetWarnings False
  3.         DoCmd.OpenQuery "CheckConfirmVestUpdateQry"
  4.         DoCmd.OpenQuery "CheckConfirmPytUpdateQry"
  5.     DoCmd.Hourglass False
  6.     DoCmd.SetWarnings True
  7.  
  8.     With Recordset
  9.         If .AbsolutePosition = .RecordCount - 1 Then
  10.         'you are on the last record
  11.             DoCmd.GoToRecord , , acFirst
  12.             Me.Requery
  13.             Me!CheckConfirmEntryFrm.Requery
  14.             Me!CheckConfirmVestingFrm.Requery
  15.         Else
  16.         'you are on some other record
  17.             DoCmd.GoToRecord , , acNext
  18.             Me!CheckConfirmEntryFrm.Requery
  19.         End If
  20.     End With
Feb 18 '14 #5

ADezii
Expert 5K+
P: 8,597
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:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. With Recordset
    2.   'code segment
    3. End With
Feb 18 '14 #6

100+
P: 294
So for #3 you're suggesting something like:

Expand|Select|Wrap|Line Numbers
  1. Dim rs as DAO.Recordset
  2. rs = (SELECT * FROM Table)
Instead of referring to the Recordset as just a Recordset Object?
Feb 18 '14 #7

ADezii
Expert 5K+
P: 8,597
I am referring to the use of the Reserved Word Recordset to refer to an actual Recordset Object.
Expand|Select|Wrap|Line Numbers
  1. 'Define Recordset rs
  2. With rs
  3.   'Code segment
  4. End With
  5.  
NOT
Expand|Select|Wrap|Line Numbers
  1. 'Define Recordset Recordset
  2. With Recordset
  3.   'Code segment
  4. End With
  5.  
Feb 18 '14 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 18 '14 #9

100+
P: 294
Okay, I understand now.
Would this work then, NeoPa?
Expand|Select|Wrap|Line Numbers
  1.     Set rs = db.OpenRecordset("SELECT * FROM Table")
Feb 18 '14 #10

NeoPa
Expert Mod 15k+
P: 31,186
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.
Feb 18 '14 #11

100+
P: 294
Got it. Thanks for your time NeoPa and ADezii.
Feb 18 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.