Autonumber Generation and reset every month

[sathyaganapathi]

Hi all,
I have a access table linked to a excel file with macro.
I need to create a autonumber based on the input date. the automumber should increment by one on every entry. The data entry to access table will be done through excel by macro.
Once the month changes the autonumber should reset to 1 and so on..
example is given in attachment.
I do not use access form to enter the data. the data entry is done through excel and excel macro.


I have used the formula "serNo: DCount("*","Autonumber","id<=" & [id])" to generate the serial no. But, it will not reset every month. It just gives serial number irrespective of munth.

 

[welshgasman]

No it will not.
You will need a table to store the last used number for each month/year.
If a record does not exist for month 9 year 2022, then create the record, default for number would be zero, then add 1 to it. Or create a set of records to cover your period ahead of time? That might be easier, but you will need to remember to extend the range when it is approaching the last date record?

From then on use DMax() for month and year and add 1 each time.

That is the basics.

 

[sathyaganapathi]

No it will not.
You will need a table to store the last used number for each month/year.
If a record does not exist for month 9 year 2022, then create the record, default for number would be zero, then add 1 to it. Or create a set of records to cover your period ahead of time? That might be easier, but you will need to remember to extend the range when it is approaching the last date record?

From then on use DMax() for month and year and add 1 each time.

That is the basics.

Dear welshgasman,
thanks for the inputs. But, I am not an expert in access table. Could you please help me with more inputs to create the table and set the formula?
Do I need to create a query table ?
how to reset the number every month with DMax()?
Please help?

 

[welshgasman]

I would have a table with field
DateID
DateMonth
DateYear
DateSINo Default is 0

Add the month and year number manually for let's say Aug to Dec for Year 2022. That would be
08 2022
09 2022
10 2022
11 2022
12 2022

Then you would use

Dim intSINo as Integer
intSINo = DMax("DateSINo","YourTableName", "DateMonth = " & Month(InputDate) & " AND DateYear =" & Year(InputDate)) + 1

then your next SINo is intSIno.
You will need to set the new value in DateSINo when a record is saved, so that DMax() can pick up the next number.

All this can be tested out in the immediate window.

Check out this video.

YouTube has lots of videos on how to use various parts of Access.

 

[sathyaganapathi]

I would have a table with field
DateID
DateMonth
DateYear
DateSINo Default is 0

Add the month and year number manually for let's say Aug to Dec for Year 2022. That would be
08 2022
09 2022
10 2022
11 2022
12 2022

Then you would use

Dim intSINo as Integer
intSINo = DMax("DateSINo","YourTableName", "DateMonth = " & Month(InputDate) & " AND DateYear =" & Year(InputDate)) + 1

then your next SINo is intSIno.
You will need to set the new value in DateSINo when a record is saved, so that DMax() can pick up the next number.

All this can be tested out in the immediate window.

Check out this video.

YouTube has lots of videos on how to use various parts of Access.

Dear welshgasman,
Thanks for the detailed inputs..
I tried to put together all of them in to a access table with my little knowledge. But, when I run the macro I am getting error "Run time error '94', Invalid use of Null" and error is pointed on the below line

intSINo = DMax("DateSINo","YourTableName", "DateMonth = " & Month(InputDate) & " AND DateYear =" & Year(InputDate)) + 1

Could you please help?

 

[welshgasman]

If no record exists then you will get a Null, I expect?
So you need to surround that with NZ() and have the default as 0

However you still need to create that record as you will need to update it.?

That is why I suggested creating the monthly records initially with a value of 0. Then you know records will exists and you just need to add more as needed?
If not and you get a 0 back with using the NZ, then you know you need to create the record and start at 1?

Get used to copying that code to the immediate window to test, also F8 and breakpoints to stop and inspect variables/fields etc.