find highest value in field, add 1 and then display in form

[Guest]

In query "filter-for-highest-archive-used" is a field called "volume number"

What I want to do is find the highest number in that field,
Then add 1 to it (as a calculation - not altering the underlying recoed) and
then display the sum on a form to inform the user what the next avaliable
(unused) volume number is. It will display this in a pop-up form window.

The above mentioned query shows only digital works that have been archived
and it is the data in the query i am working with here.

Thanks for your help

 

[missinglinq via AccessMonster.com]

Rather than a query, why not simply use the DMax function? This works whether
VolumeNumber is text or numerical datatype:

NextVolumeNumber = DMax("val([VolumeNumber])", "YourTable") + 1

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Thanks very nuch.

I put the line you provided (changing the names to suit)
into the code for the "onOpen" event for the form.
I checked it against the table to confirm it gave the right value.

This form will be a pop-up form to inform the user what the currently
highest and next number is avaliable. This is so they can key it in manually.
The reason for keying it in manually is that when Digital projects are
entered in, they are archived to dvdr. Depending on the size of the project
folder, more than one project may be archived on the same disk.
Also the volume number field is also used for drawings to indicate what
folder they are in (The media field tells if it is dvd or physical folder).

Thanks very much.

 

[missinglinq via AccessMonster.com]

Glad it met you needs, Darcy!

Have a great weekend!

Linq

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com