Simple Access

[njwilli3]

Whats the code to return the record number of the record you are currently viewing on a form?

[RobDog888]

You can set a recordset object to your fors one and access the rs properties like so.

Dim oRs As Recordset
    Set oRs = Me.Recordset
    MsgBox oRs.RecordCount

[dannymking]

However (God I like that word)

It's not as simple as that.. AbsolutePosition and PercentPosition and all that... plus the recordset is opened in a Cache, great for small recordsets but a nightmare for a large table..

I can give you some code that you will be able to set the RecordSource of the form via SQL and a sequence number..

All you need is one unique field in the table/query and it will work everytime (and I'm not talking about just using the Autonumber...

SELECT *, DCount("RecNumber","TableName","RecNumber<" & [RecNumber])+1 AS SeqNr FROM [TableName];

Where RecNumber represents the primary key in the table.. you can then reference the SeqNr for your record count..

[RobDog888]

No, DCount is slow. If your Form already is opened then the recordset is already populated and set. Alll your doing is attaching a oRs recordset object to it to access the .RecordCoutn property.

There is no need to perform a second call to the table delaying things twice as much.

[dannymking]

.RecordCount returns the number of records in the recordset.

And the SQL is for the form, and does not call it twice... I'll demonstrate my point in the best way possible..

Attached is a small database which uses the SQL as the recordsource.. it shows my SeqNr.. RD's RecordCount.. and the autonumber on the form.. plus a few names from this site..

The first screen all match perfectly.. until you click on the bottom arrow to move to the next record.. RD's jumps to 8 mine jumps to 2 (The correct record position) and the autonumber jumps to 3 (I removed rec number 2 to prove my point..)

Hmm.. Your sig needs a change RD..

[RobDog888]

Oh, record position and not count. Still you can use the .AbsolutePosition property of the rs to determine that, depending on how your cursor type, etc.

Hmm.. Your sig needs a change RD..

What are you talking about?

[dannymking]

Oh, record position and not count. Still you can use the .AbsolutePosition property of the rs to determine that, depending on how your cursor type, etc.

Try again..

.AbsolutePosition +1 will do it, but when adding a new record this will state the recordcount until the new record is written.. mine doesn't..

[njwilli3]

nice 1 guys, sounds like theres a bit of a battle going on but cheers for the info.

[dannymking]

Battle... No just discussion.. Me and RD have these all the time..

And Besides my method requires no code behind the form..

[njwilli3]

ok so i used your code from your example and changed it to

SELECT ContractDetails.*, DCount("ContractReference","ContractDetails","ContractReference<" & [ContractReference])+1 AS SeqNr
FROM ContractDetails;

//where ContractDetails is the name of my table and ContractReference is my primary key in that table

BUT i get "The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'CON.''

[dannymking]

Just created a sample table matching your details above and also created a query with the SQL above and a form with the RecordSource set the same query.. I got no error..

This looks like it is something in your form that is causing the problem.. check your form's RecordSource to make sure that the SQL is not in quotes and is definately like what you have posted..

If it's not there then it must be a control..

Once you have changed the recordsource bring up the field list and check each control to match the fields.. the sequence (record count) number will be called SeqNr.

[njwilli3]

ahhh its killin me, gonna take a rest for the weekend and have a look on Monday...Good effort mate, i've been doing it all day and reckon a bit of sleep will sort it out

[dannymking]

You could just upload an example of the DB in Zip format for us to take a look at..

[RobDog888]

Try again..

.AbsolutePosition +1 will do it, but when adding a new record this will state the recordcount until the new record is written.. mine doesn't..

But thats the way Access Forms do it, by adding a new recordcount before its actually updated and saved to the table.

Sounds like this thread is about solved. Good work DMK.