Fixed autonumber reset everyday

[astroshark]

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.

[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.

Private Sub Form_AfterInsert()
    Me!AutoKey = getNextKey()
    DoCmd.Save
End Sub
 
Public Function getNextKey() As String
    getNextKey = Val(Nz(DMax("AutoKey", "AutoKeyTest", "RecordDate=#" & Date & "#"), 7999)) + 1
End Function

[astroshark]

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.

[astroshark]

I think I found out my problem will report back when I test it out.

[astroshark]

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!