Autonumbering in VBA

[Sniper-BoOyA-]

Good Afternoon,

I started to work on a database a few months ago, but never got to finish it.

Now i got some spare time again, i really would like to get back into it. But i am encountering the following "problem".

Ive got a table named : tblfielddata

This table is the source of a form, and the user will use the form to enter data into the table.

One of the fields in the table is Wet Density.

What i would like to do is to put the table in a query, and order it by Wet Density (DESC) . And assign Proctorid's to them.

The highest get "P1" , 2nd highest "P2" , third highest "P3" etc...

To do this, ive implemented the following into the query:

Prctrid: "P" & ((Select Count(*) From queryfielddata As Dummy Where Dummy.natdchthd> [queryfielddata].[natdchthd])+1)

Works great, BUT it makes the query not updateable.

Question: Is there a different (better) way of having some sort of autonumber function, without risking the fact that the query locks up and is not updateable anymore?

Cheers!

 

[Galaxiom]

Firstly get rid of the P from the stored value and convert the field to Number. You can display it by using the Format Property of a control or field.

Then use a DMax function on the field to get the largest existing value.

Most of the posts here about DMax are devoted to this kind of thing so a search of the forum for this term will give you a lot of info.

 

[Sniper-BoOyA-]

Thanks,

I guess i will look into Dmax...

Im guessing the Row_Number() function i used to use in SQL doesnt work in AccesS?