Retrieve new number

[Dirk.VanGiel]

I have a data entry form to add new records to an Access database file called Claims. An auto-incrementing sequence number (SeqNbr) needs to be kept PER YEAR. If the user enters a date the sequence number pertaining to the year of this date needs to be incremented. The first record within a new year of course takes value 1.
Records can be added at random for different years.

A simple SQL-statement can be made to determine the new sequence number:
SELECT max(Claims.SeqNbr) + 1 from Claims where year(this.value) = year(Claims.EventDate)

this.value meaning the value of the date control in which the user entered the date.

I need to return the new sequence number to another field on the form in which also the COMPANY CODE, YYYY and MM from the EventDate, the new sequence number and the USER INITIALS are concatenated.

Being new to this forum I would appreciate every help to point me in the good direction.

 

[Roku]

You might find DMax a better option.

Field=DMax("SeqNbr","Claims","year=" & this.value)+1

SeqNbr and Claims are as you define them.
'year' here is the field name in the table which contains the year of interest. 'Field' is the where you want to put the value in your form.

 

[Dirk.VanGiel]

Thanx Roku, this does the trick.

 

[Dirk.VanGiel]

Something weard happens...
Variable "Field" gets a Null value because there are no records for the year specified.
So I introduce a new field to calculate the new sequence.

Dim NewField as Integer

If Field = Null then NewField = 0 Else NewField = Field
NewField = NewField + 1

However the test does not work: The compiler goes to the Else-part of the If-statement and executes the NewField = Field statement and an error occurs.

If I enter a date for which records exist then all goes well.

 

[Roku]

Try this:

Field=Nz(DMax("SeqNbr","Claims","year=" & this.value),0)+1

This checks for null response from DMax and sets Field to 1 (0+1)