Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Forums > Developers
Back to Access Developer Forum    Comments List
VBA code for special numbering Upload Images   Link   Email  
Pierre-Jean Tessier       
3 months ago
I have a problem with my vba code for auto numbering a field.

The field [DOSSIER_RAI] is a auto generated numbers and are incremented by 0001 for each new record.
The file number is based on the current date of the day with an incremential number of 4 digits at the end (RAI-yymmdd-sequential #) RAI-YEARMONTHDAY-SEQUENTIAL.

The user has to click on the pen to generate a new file number. Everything is working fine throughout the year. The problem happens at the beginning of the new year.

So when the date changes for 2023-12-31 to 2024-01-01, The [DOSSIER_RAI] sequential number should reset to 0001 and not continue counting.

What I am experiencing is that on December 31st 2023, I was at RAI-231231-1500 and on January 1st 2024, the file number was RAI-240101-1501 and should have been RAI-240101-0001 (counter should reset for the first entry of the year). So it has to detect that the year has changed and then reset the counter to 0001.

Need help fixing this issue in the vba code.
I have included a sample of the database to make it easier to understand.

Any help would be appreciated!
Thanks :-)
Pierre-Jean Tessier       
3 months ago

Pierre-Jean Tessier       
3 months ago

Pierre-Jean Tessier       
3 months ago
Edit: Removed dropbox link, see Rule #3
Adam Schwanz             
3 months ago
It appears you are just looking up if a value exists in the table on your DLOOKUP. Which should always be true so I don't see how it would ever go back to 001. You need to add some where criteria to it to check if a value exists in the current year. The compteur=DLOOKUP line.

Richard Rost             
3 months ago
Kevin Yip       
3 months ago
Hi Pierre-Jean, in 2023, the ID always began with RAI-23.  As soon as 2024 started, it needed to start with RAI-24.  So you need to check the first 6 characters of your ID to determine whether a new year has started and that you should reset your sequence.  Therefore, you need to use DMax (or DLookup) to check if any ID exists that begins with RAI-xx, where xx is the current 2-digit year.  If none exists, you reset the sequence.

Your DMax function should be modified to:

     DMax("[DOSSIER_RAI]", "RAI", "Left([DOSSIER_RAI], 6) = 'RAI-" & Annee & "'")


     DLookup("[DOSSIER_RAI]", "RAI", "Left([DOSSIER_RAI], 6) = 'RAI-" & Annee & "'")

If Annee is "24" and no entry exists for 2024, the above should return null, and you should reset your sequence.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/20/2024 10:55:11 PM. PLT: 0s