0

I am new to MS Access and I would like to generate an autogenerated sequential and unique alphanumeric number of the format SYYMM001, SYYMM002, SYYMM003... (ex for 2023 january: S2301001, S2301002, S2301003).

I use MS Access 2016.

I am in my table, in View mode, in the column InvoiceCode in which I want the number to appear, in the general sheet, in Default Value I used the following code:

= "S" & Format(Now(),"yymm") & Format((DCount("[InvoiceID]","InvoiceTable")),"000") where InvoiceID is the autonumber column and InvoiceTable the name of the table.

This code does not work and generate the following error: "Unknown function "Dcount" in validation expression or default value on "Invoice Table.InvoiceCode"

I tried another code that I found online which works but instead of giving me a sequential number it generate a random number ex S2301586, S2301236 ...

="S" & Format(Now(),"yymm") & Format(Int(Rnd()*1000),"000")

Would you have a code that would do what I need? Thanks in advance for your help

Seashade
  • 1

1 Answers1

0

You can't set this in the table.

You could try this in your form you use for data entry - in the BeforeInsert event of the form:

Me!InvoiceID.Value = "S" & Format(Date,"yymm") & Format(DCount("*","InvoiceTable"),"000")
Gustav
  • 53,498
  • 7
  • 29
  • 55