asked on

How create number field using current year and sequential 5 digit number

When I create a new record using a form, I have a field that I want to auto populate with the current year and a 5 digit number. So, for example, it I am creating a record for this year I want the field to indicate "201900001". And the next record to be 201900002", etc.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

If you use that DMAX technique, I would strongly recommend that you not compute the value until the BeforeUpdate event of the form.

In a multi-user environment, Gustav's suggestion could result in two or more records having the same value, especially if you use the Form_Current event.

BTW, in my recommendation above, the Form_Current event would look something like:

Private Sub Form_Current

    if me.NewRecord Then  me.txt_SeqNum = fnNextNumber("tblInvoice")

End Sub

Open in new window

BTW, what you name your NumberTypes in tbl_Next_Numbers is entirely up to you, but I would give them names which relate directly to the field they are going in. So, a better example in my code above would be:

fnNextNumber("tblInvoice_InvoiceID")

SOLUTION