How to Create Auto Numbering in Ms Access that Automatically Reset every year or every month.

[@iammai5404]

Great tutorial, very helpful and very clever.

[@EdcelleJohnGulfan]

Thanks for watching

[@DonRamonLoco]

Excellent!!! Keep going! And thank you for this video. Very helpful for me!!

[@EdcelleJohnGulfan]

Thanks Don.

[@56cridwan]

thanks for the video, very helpful

[@EdcelleJohnGulfan]

Thanks for watching

[@bck4589]

Thank you sir,❤️ from India

[@EdcelleJohnGulfan]

Welcome

[@boodygarcia5874]

Thank you you Sir Edcelle!

[@EdcelleJohnGulfan]

Welcome and thanks for watching Boody

[@sheikhobaba]

Great

[@patriknemec6253]

Hi Mr Gulfan, what is VBA Code when I delete last invoice and then create new with the same number. For example I delete invoice 2023002 and then create new with number 2023002. 🙏

[@EdcelleJohnGulfan]

Why not delete only the records inside that invoice?

[@japmanumbali8001]

How to create invoicenumber and seriesnumber in to 4 digits like 0001. Thanks

[@EdcelleJohnGulfan]

Try this. kzbin.info/www/bejne/q6mbeqGagtp0bK8

[@bajront66]

Greetings. Sir, you explained this really professionally, but I can't do what I need and that's why I need your help. Namely, the Invoice number must be reset annually, but it should contain the first and last letter of the month, then the Invoice number and at the end the year, for example: JY-1/2021, JY-2/2021; FY-3/2021, FY-4/2021, FY-5/2021; MH-6/2021 etc. JY-1/2022, JY-2/2022; FY-3/2022... Can you help me with this please?

[@EdcelleJohnGulfan]

Actually same procedure but you have to add the LEFT and RIGHT FUNCTION. It's a complex process so i will create a video about this soon.

[@naughtynaughty6453]

Hi Sir, I am currently working on a project similar to this, it's just that I'm using ms excel as my front-end and ms access as the back-end. My challenge is that there are multiple users simultaneously creating record/tickets. Do you have any suggestions how it can be implemented? Thank you.

[@EdcelleJohnGulfan]

Multiple-user at the same time in excel is not doable. Either you work in a Read Only Mode or the one who can save record is the last user. Why not use Access instead of excel?

[@naughtynaughty6453]

@@EdcelleJohnGulfan I am not used to in ms access forms...all my previous projects are in excel - access set up since it is much more like the old VB6.0 and ms access tandem. I was able to create something but it's a hit and miss. I have 4 users creating tickets simultaneously. We are all trying to click the command button at the same time and after a couple of attempts (5 to 6 entries each users) one would encounter an error with the database connection which I couldn't properly troubleshoot...To give you a background the functions invoked when the command button is clicked: 1.) It will make a query to retrieve the max id (auto_number primary key) and the max series (indexed - no duplicate) and store them in variables: max_id and max_series 2.) It will insert a record just to create an initial entry to the table in the database and to determine the order of the auto_number generated. This is my work-around for the multi-user simultaneously creating record. 3.) It will query the last record created and then compare it to the max id. Ex. the max_id is 10 and the last id stored in step 2 is 14 then it means I'll be needing to add 4 in my step 4 to generate the ticket number based on the max_series. 4.) Update the series by adding the difference of the last id stored and the max id to the max_series... Not sure if I explained it clearly. But like I said it's a hit and miss. I know it's not the best approach and I'm still trying to make a work around and hopefully I sort it out.

[@toutypasse44000]

salut je suis content de la solution

[@EdcelleJohnGulfan]

Thanks for watching.

[@amrelnaggar4509]

I need your help, if I have a table with data entry form. This table contains fields (Order date, Total cost, first payment, second payment, Rest and paid date). Rest= Total cost - first payment - second payment. what I need is to have the paid date (now date when the order is paid) filled automatically under only two conditions, when the Total cost > 0 and Rest =0 (which means that the order is paid). Also how to prevent order modification after it is paid. Would you help me with that , please.

[@seshadrisrikant]

When i am running the form for the new year its showing invalid use of null (Error 94) . How to solve this

[@EdcelleJohnGulfan]

If That's the error, you are using or referring the null or empty field. To fixed it. Put NZ FUNCTION to the code that has an error.

[@Raffaely1021]

hello, I tried this but unfortunately it stops at 10...error after 10 input datas...may I ask why? Can you please check the vba, Thank you

[@EdcelleJohnGulfan]

I have mistake in my coding. 1:14 series number, the data type must be Number not a short text.

[@patriknemec6253]

Hi Mr Gulfan, I would like to set my invoice number like 2023001, …and I do not know hot to set three numbers at the end of invoice number. Now I have 20231,…numbering. I have tried your another video. But it does not work. Please help. 🙏🙏 Many thanks.

[@EdcelleJohnGulfan]

Have you seen this video? kzbin.info/www/bejne/q6mbeqGagtp0bK8si=thKrnyQWNLVynxwu Well, if not, watch the video and apply this format. sample: Me.Invoice_Number= Format(Date, "yyyy") & Format(Your_autonumber, "000")

[@murealnoauy]

From the example you did, it was very good. But I would like to add the ID number to 0001, 0002. What should I do? 2023-04-1 to 2023-04-0001 2023-04-2 to 2023-04-0002 2022-01-1 to 2022-01-0001

[@TheOrangeGecko]

Change the line "Me.SeriesNumber = InvNext" to Me.SeriesNumber = Format(InvNext, "0000")

[@admiralrichvi]

How to make it reset each fiscal year ?

[@EdcelleJohnGulfan]

Hi thanks for asking. Same procedure but you need to set up first your fiscal date as your variable (September + Year)

[@admiralrichvi]

If you going to use max ID for the series number, how to rest the numbering for the new fiscal? I have 101-2023, combined series with year.@@EdcelleJohnGulfan

[@moyal3455]

This method does not work when records go above 10. System error that value has been duplicated.

[@EdcelleJohnGulfan]

Thanks for the info but im using this since 2013 and i don't have problem. You can avoid the duplicate values if you combine the year+month+series number.

[@moyal3455]

@@EdcelleJohnGulfan This actually resolved the error: vlast = DMax("Val([SeriesNumber])", "invoice", "InvYear='" & Me!invyear.Value & "'").

[@admiralrichvi]

This happening to me as well

[@MaraingMuong168]

@@moyal3455 Thank you very much thank you

[@NoumanAzam01]

@@moyal3455 So great of u Moya L

[@kidsworld7459]

Hi sir, I want to create unique index number for customers. I want to get first letter of Coustomer name with number such as A001, A002, B001, C001, C002 etc. Please help me.

[@elvesreis2235]

I tried this but it does not increment :Private Sub Form_BeforeInsert(Cancel As Integer) Dim Vlast As Variant Dim InvNext As Integer Me.Invyear = Format(Date, " YYYY ") & "-" & Format(Date, "mm") Vlast = DMax("SeriesNumber", "Invoice ", " InvYear =' " & Me.Invyear.Value & "'") If IsNull(Vlast) Then InvNext = 1 Else InvNext = Vlast + 1 End If Me.SeriesNumber = InvNext End Sub

[@EdcelleJohnGulfan]

Send me your file and I'll to check.

[@kiranarana7274]

Please help I get aan Syntax error (missing operator) in query expression InvYear=*2023* Private Sub Form_BeforeInsert(Cancel As Integer) Dim vLast As Variant Dim InvNext As Integer Me.InvYear = Format(Date, "YYYY") vLast = DMax("SeriesNumber", "Invoice", "InvYear=*" & Me.InvYear.Value & "*") If IsNull(vLast) Then InvNext = 1 Else InvNext = vLast + 1 End If Me.SeriesNumber = InvNext End Sub

[@yasirkhan-mw1zr]

Me.InvYear = Format(Date, "YYYY") vLast = DMax("SeriesNumber", "Invoice", "InvYear='" & Me.InvYear.Value & "'")

[@Stache987]

Auto numbers used for keys are NOT for you, no need to reset them, it only opens up corruption

[@EdcelleJohnGulfan]

YES I ABSOLUTELY UNDERSTAND. Let's say... You have a database under testing and after you finalize it, client wants to start from 0, what will you do?

[@EdcelleJohnGulfan]

But this method is resetting the numbering variables and not the auto numbers.

[@patriknemec6253]

⁠​​⁠@EdcelleJohnGulfan because it is made for wrong customer.

[@Raffaely1021]

hello, I tried this but unfortunately it stops at 10...error after 10 input datas...may I ask why? Can you please check the vba, Thank you

[@Raffaely1021]

I wish to create a field that shows, mm-"9999"-yyyy, the "9999" is the unique ID autonumber, please help thank you!

[@EdcelleJohnGulfan]

I have mistake in my coding. 1:14 series number, the data type must be Number not a short text.