Calculating Next Available Number

  • Thread starter Thread starter Douglas J. Steele
  • Start date Start date
D

Douglas J. Steele

Assuming you're using a form, you could use the DMax function to get the
largest value used, then add 1 to it, in the form's BeforeUpdate event. If
you're trying to do this strictly in the query output grid, I don't believe
it's possible.
 
I have a Form linked to a query (Query linked to a Table - using Query for
Calculations). When the user clicks on new entry, I want it to be able to
generate a new number for a particular field [Ref1] based upon the next
available number for that year the entry was recorded [DateLogged]. The
first entry for each year commencing at 1.

Any Ideas.

Steve
 
I'm trying to generate it at the time of entering a new record on the form.
So instead of me filtering down on the date field for the current year and
then finding the highest value in the [Ref1] field so I can insert the next
number in the sequence. I presumed it was something to do with the Dmax
Function, like:

DMax("[Ref1]","NameOfTable") + 1

But how do you get it to only look at those in the current year?

or am I barking up the wrong tree.

Regards

Steve

Douglas J. Steele said:
Assuming you're using a form, you could use the DMax function to get the
largest value used, then add 1 to it, in the form's BeforeUpdate event. If
you're trying to do this strictly in the query output grid, I don't believe
it's possible.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve M said:
I have a Form linked to a query (Query linked to a Table - using Query for
Calculations). When the user clicks on new entry, I want it to be able to
generate a new number for a particular field [Ref1] based upon the next
available number for that year the entry was recorded [DateLogged]. The
first entry for each year commencing at 1.

Any Ideas.

Steve
Click to expand...
Click to expand...
 
DMax("[Ref1]","NameOfTable", "[DateField] >= #01/01/" & _
Format( Date(), "yyyy\#" ) ) + 1

HTH
Van T. Dinh
MVP (Access)
 
Thank you both for your help. It's now generating the next available
number. The only problem I now have is trying to get it to do it
automatically. I have attached it to a command button for the time being.
I wanting it to be generated when the add record button is pressed on the
navigation bar.

Regards

Steve

Van T. Dinh said:
DMax("[Ref1]","NameOfTable", "[DateField] >= #01/01/" & _
Format( Date(), "yyyy\#" ) ) + 1

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
I'm trying to generate it at the time of entering a new record on the form.
So instead of me filtering down on the date field for the current year and
then finding the highest value in the [Ref1] field so I can insert the next
number in the sequence. I presumed it was something to do with the Dmax
Function, like:

DMax("[Ref1]","NameOfTable") + 1

But how do you get it to only look at those in the current year?

or am I barking up the wrong tree.

Regards

Steve
Click to expand...
Click to expand...
 
Use the Form_Current Event and check for New Record. This way, as soon as
the user "moves" to a new Record, the next avail.ill be calculated.

Beware that this may allocate duplicate numbers if 2 or more users trying to
add Record at the same time in a multi-user database.
 
Back
Top