Annual Record Code Generator

[michi.for]

Hi and thank for all the Help/suggestions:


I have a table records with a "data creation" field and a "sequential number" field.
On the same table, I need to generate a "sequential number" and a "code number" like: YYYY+"0"+(sequential number starting from "1", EVERY YEAR).

For example:
RECORD 1 - 2015/07/02 - Seq.number 1 - Code: 201501
RECORD 2 - 2015/07/02 - Seq.number 2 - Code: 201502
RECORD 3 - 2015/07/02 - Seq.number 3 - Code: 201503
RECORD 4 - 2016/07/02 - Seq.number 1 - Code: 201601
RECORD 5 - 2016/07/02 - Seq.number 2 - Code: 201602

I need to generate the code, when filling the Data Entry form.
I fill the Data field, and then i need the code (if is possible visualize it on the Form).


This is what i think:
Insert the data field.
On updating the data field, start a vba code that take from the data field only the year.
Inser the year in a field.
Start a query that take the year from the field, and search all the records that match the year.
From that, searche the "last seq_code".
Take that last code, add 1, and put in the seq_code of the record.
Generate and concatenate the code with: year+0+seq_code.

I hope you understand.

Thank again.
Francesco F

 

[arnelgp]

for code generator:

=Format(Nz(dmax("Val([field1])","table1", "Val(Left(field1,4)) = Year(Date())")+1, Val(Year(Date()) & "01")), "000000")

for sequence just extract the last two digit from the above formula:

=Val(Mid(expr1, 5, 2))

 

[michi.for]

for code generator:

=Format(Nz(dmax("Val([field1])","table1", "Val(Left(field1,4)) = Year(Date())")+1, Val(Year(Date()) & "01")), "000000")

for sequence just extract the last two digit from the above formula:

=Val(Mid(expr1, 5, 2))

Sorry, just to better understand:

From the first expression:
DMAX(...) -> find the record with the latter-day of the year that i want.
If founded, take the code and add 1.
Nz(...) -> if null, the code is YEAR01. If not null is year+(last code+1)
format(...) -> The code is composed by "000000" numbers

Is it right??

 

[arnelgp]

let start with the inner code, Dmax
search for the maximum value in table1 where the Year is the same as the current year and return the value of that field as numeric, if found, plus 1.
if there is no record found, which means the current year is not in the table, we create it with initial sequence of 1.
we then convert to string in the format "000000", which is 201601.

note for this to work we then later save this value back to the table. so as the make the sequence running.

 

[michi.for]

let start with the inner code, Dmax
search for the maximum value in table1 where the Year is the same as the current year and return the value of that field as numeric, if found, plus 1.
if there is no record found, which means the current year is not in the table, we create it with initial sequence of 1.
we then convert to string in the format "000000", which is 201601.

note for this to work we then later save this value back to the table. so as the make the sequence running.

Ok, the only thing is that the date of the record come from a data entry mask.
...but i can't it in a query....
How can i do in VBA?

 

[arnelgp]

on the After Update event of your date textbox:

private sub yourdatecontrol_AfterUpdate()
Me.yourCodeNumberControl =Format(Nz(dmax("Val([field1])","table1", "Val(Left(field1,4)) = Year(Date())")+1, Val(Year(Date()) & "01")), "000000")

Me.yourSequenceNumberControl = =Val(Mid(Me.yourCodeNumberControl, 5, 2))
End Sub

blue-colored text are to be replaced with actual control, field and table name that you have.

 

[michi.for]

on the After Update event of your date textbox:

private sub yourdatecontrol_AfterUpdate()
Me.yourCodeNumberControl =Format(Nz(dmax("Val([field1])","table1", "Val(Left(field1,4)) = Year(Date())")+1, Val(Year(Date()) & "01")), "000000")

Me.yourSequenceNumberControl = =Val(Mid(Me.yourCodeNumberControl, 5, 2))
End Sub

blue-colored text are to be replaced with actual control, field and table name that you have.

I am trying on VBA with your code, but there is something not working:

In DMAX method:
Year(Date())
This take the TODAY YEAR. I need to put the date of the data entry form (that could be different from "today year").

Right?

 

[arnelgp]

Instead of Year(Date()), use your control:

Year(CDate(Me.DateControl))

 

[Minty]

I have to question if you have the date and the sequence number why you would want to store this in the first place? You can always display it as a calculated field...

 

[michi.for]

I have to question if you have the date and the sequence number why you would want to store this in the first place? You can always display it as a calculated field...

Thank you Minty,
i have to store in a table an estimate of car lease payment.
I have a table Estimations, a table Clients.
In a data entry form called "Estimate form", i put all the estimate data:
client, payment, lease data, date of estimate.
After i stored the record i can generate a .pdf document.
Every document / record need a unique code number.

I store the code number for printing, search and edit these documents.
Is it the wrong way?

 

[Minty]

In ideal world theory you shouldn't store anything that you can calculate - as long as the calculation can't change. So for instance you would store an invoice total where there was tax involved if the tax rate might change and you aren't storing the tax rate with the invoice, because the calculation would change over time.

If on your forms you can always create the unique number you could argue that you don't need to you don't need to store it, and if the sequence number can change / be added to you could automatically calculate the latest ref number without having to restore it against the payment schedule.

In the real world if this is unlikely to change - tbh it may well be easier to simply store it and save some additional programming later. The only thing looking forwards is that you may be searching by dates in which case it would in fact be easier to look for the original date on the record than have to extrapolate the date from your stored field.

Think about how you will use the stored field. If no one will use it other than you to link to the pdf or search, you may as well just use the unique record number, which would simplify everything.