How to show what record number you are on in form?

[adamskiii]

I have created an unbound form in Access 2007 and am listing the records
which are in my table. I found out how to show how many records there are in
the table but would also like to show which record you are on. For example,
when I click the next button to see the next record it will show: "Record 2
Of 30", and so on. This is the one I used to count the amount of records:

Dim countRecords As Long
countRecords = DCount("customerNumber", "customer")
Me.txtRecordAmount = "Record # Of " & countRecords

How can I show what record I am on?

Thanks

 

[John W. Vinson]

I have created an unbound form in Access 2007 and am listing the records
which are in my table. I found out how to show how many records there are in
the table but would also like to show which record you are on. For example,
when I click the next button to see the next record it will show: "Record 2
Of 30", and so on. This is the one I used to count the amount of records:

Dim countRecords As Long
countRecords = DCount("customerNumber", "customer")
Me.txtRecordAmount = "Record # Of " & countRecords

How can I show what record I am on?

Thanks

Since Tables have no defined order, and no record numbers, the question as
posted has no answer. If you have a Recordset you could use its
AbsolutePosition property; if you have a sorted query you could use DCount()
to count the number of records with a key less than the current record's key.
What's the context? Why are you using a (complex, less efficient, programming
intensive) unbound form rather than a bound one, where all these come
provided?

 

[adamskiii]

Since Tables have no defined order, and no record numbers, the question as
posted has no answer. If you have a Recordset you could use its
AbsolutePosition property; if you have a sorted query you could use DCount()
to count the number of records with a key less than the current record's key.
What's the context? Why are you using a (complex, less efficient, programming
intensive) unbound form rather than a bound one, where all these come
provided?

I just want it to show for example, if you are on the first record in the
table then that is record 1, the next one is 2, and so on. I am using an
unbound form because it helps me learn new things in Access.

-Adam

 

[John W. Vinson]

I just want it to show for example, if you are on the first record in the
table then that is record 1, the next one is 2, and so on.

Again:

A Table is an *unordered heap of records*. It does NOT contain record numbers.
The order of records in the table is arbitrary, and Access does not expose it.
If you have something you consider a "record number" then you must include it
in the table as a field and maintain it yourself.

The closest thing to it would be an Autonumber, but Autonumbers will have gaps
in the sequence and can become random.

I am using an
unbound form because it helps me learn new things in Access.

Just so you're aware that it's a much more complex and difficult approach than
bound forms. It's sometimes necessary but I only use unbound forms when I
must.

 

[DevlinM]

If you are just wanting to show the ordinal position of the cursor, you can
use the form's CurrentRecord property in the Form_Current event.

Private Sub Form_Current()
Me.SomeTextBox = Me.CurrentRecord

 

[DevlinM]

For an even simpler method, just set the form's Navigation Buttons to yes.
That will display the record number, the number of records and nav buttons
for navigating the records.

Cheers!

 

[John W. Vinson]

For an even simpler method, just set the form's Navigation Buttons to yes.
That will display the record number, the number of records and nav buttons
for navigating the records.

Only with a bound form. Adamskii is using an *unbound* form (so the nav
buttons will show 1 of 1).

 

[adamskiii]

Thanks. That is what I want, to show the current position of the cursor. I
tried this code but I keep getting an error. This is what I get:

"Run-Time error '2455'

You entered an expression that has an invalid reference to the property
CurrentRecord."


Here is the Code I used which keeps giving me errors:

Me.txtRecordAmount = "Record " & Me.CurrentRecord & " Of " & countRecords


Any help?
Thanks

 

[Dennis]

My memory of this is fuzzy (as I hardly ever used it), but you MAY need to use:

"Me.Recordset.CurrentRecord" and "Me.Recordset.RecordCount"

To get what you want.

 

[krishna kunwar]

I have created an unbound form in Access 2007 and am listing the records
which are in my table. I found out how to show how many records there are in
the table but would also like to show which record you are on. For example,
when I click the next button to see the next record it will show: "Record 2
Of 30", and so on. This is the one I used to count the amount of records:

Dim countRecords As Long
countRecords = DCount("customerNumber", "customer")
Me.txtRecordAmount = "Record # Of " & countRecords

How can I show what record I am on?

Thanks

Do this =[currentrecord] & " of " & count (*)

 

[krishna kunwar]

For new record =[currentrecord] & " of " & count (*) -[newrecord]
this formula for text box, tested ok : =[CurrentRecord] & " of " & Count(*)-[newrecord]