By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,157 Members | 891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,157 IT Pros & Developers. It's quick & easy.

Fixed autonumber reset everyday

P: 19
I have a field in a form that I want to have automatically generated a number. Specifically, it starts at 8000 and goes up by one every time a number is used. Multiple people will also be using this form at the same time.

So let's say Person A and Person B open the form at the same time, so they both get the same unused generated number. The goal of it is if Person A submits his form first, then I want Person B form to lookup records from the table. Then after the form detects if it was already used, have it pop up a message box saying "someone has already used this number, so your number will be the next unused number," and then the number will automatically go up by one upon submission.

Then have that series of numbers start all over, back at 8000 the very next day. If you need more clarification, please let me know. Any help on this would be appreciated.
Mar 6 '17 #1

✓ answered by jforbes

You could use the AfterInsert Event to grab the next number and stamp it on the Record. This way the number isn't shown or pulled until the user saves the Record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2.     Me!AutoKey = getNextKey()
  3.     DoCmd.Save
  4. End Sub
  5.  
  6. Public Function getNextKey() As String
  7.     getNextKey = Val(Nz(DMax("AutoKey", "AutoKeyTest", "RecordDate=#" & Date & "#"), 7999)) + 1
  8. End Function

Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
You could use the AfterInsert Event to grab the next number and stamp it on the Record. This way the number isn't shown or pulled until the user saves the Record.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterInsert()
  2.     Me!AutoKey = getNextKey()
  3.     DoCmd.Save
  4. End Sub
  5.  
  6. Public Function getNextKey() As String
  7.     getNextKey = Val(Nz(DMax("AutoKey", "AutoKeyTest", "RecordDate=#" & Date & "#"), 7999)) + 1
  8. End Function
Mar 6 '17 #2

P: 19
So thank you for providing this it has worked to some degree, it works but I changed the date on my computer manually to check to see if it would reset the number, the access form detects the date, but when the next day happens the number resets to 1 instead of 8000. I have tried playing around with the code, but have not found any viable solution to it.
Mar 7 '17 #3

P: 19
I think I found out my problem will report back when I test it out.
Mar 7 '17 #4

P: 19
No clue what I did haha I played with it and then when i'm looking at it, it's back to your original code and it works once again. Thank you for your help!
Mar 7 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.