Autonumbering using VBA

[Sniper-BoOyA-]

Good Morning,

Ive got a form which is based on data input from our lab.

The query looks like this:

SELECT queryveldgegevens.tbllabgegevens.proefnummer AS Proefnummer, queryveldgegevens.*, queryveldgegevens.prctrnr, queryveldgegevens.natdchthd, queryveldgegevens.mpdepd, queryveldgegevens.optimumepwatergeh, queryveldgegevens.opmnucleair, IIf([watergeh]="","",([natdchthd]/([watergeh]+100)*100)) AS drgdchthdinsitu, (([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100 AS watergeh, IIf([drgdchthdinsitu]="","",([drgdchthdinsitu]/[mpdepd])*100) AS gecorrverdgrd, Round([gecorrverdgrd],0) AS roundgecorrverdgrdr, Round([drgdchthdinsitu],0) AS rounddrgdchthdinsitu, Round([watergeh],1) AS roundwatergeh
FROM queryveldgegevens
ORDER BY queryveldgegevens.natdchthd DESC;

As you can see its on ORDER by Natdchthd Desc. So the highest value of natdchthd is shown on top.

Ive got a total of 10 records of Natdchthd.

I now would like to give the highest value the tag "P1", the second highest "P2", the third highest "P3".... The lowest "P10".

Is there any way of using some sort of Autonumbering function/loop in VBA ? So i can make a textbox and use it on my contineous form?

Cheers!

 

[Beetle]

You can do this in the query itself if you want. Try this (untested);

SELECT queryveldgegevens.tbllabgegevens.proefnummer AS Proefnummer, queryveldgegevens.*, queryveldgegevens.prctrnr, queryveldgegevens.natdchthd, "P" & ((Select Count(*) From queryveldgegevens As Dummy Where Dummy.natdchthd> [queryveldgegevens].[natdchthd])+1) AS SeqNo, queryveldgegevens.mpdepd, queryveldgegevens.optimumepwatergeh, queryveldgegevens.opmnucleair, IIf([watergeh]="","",([natdchthd]/([watergeh]+100)*100)) AS drgdchthdinsitu, (([massaschlmonnat]-[massa3])/([massa3]-[massaschl]))*100 AS watergeh, IIf([drgdchthdinsitu]="","",([drgdchthdinsitu]/[mpdepd])*100) AS gecorrverdgrd, Round([gecorrverdgrd],0) AS roundgecorrverdgrdr, Round([drgdchthdinsitu],0) AS rounddrgdchthdinsitu, Round([watergeh],1) AS roundwatergeh
FROM queryveldgegevens
ORDER BY queryveldgegevens.natdchthd DESC;

 

[Sniper-BoOyA-]

Omg, you are my hero. It worked like a charm.