How do i automatically generate #'s not starting with 1??? (1 Viewer)

fairygirl85

New member
Local time
Today, 01:18
Joined
Apr 21, 2009
Messages
2
I need to create a table that can automatically generate numbers starting with 9910080 then go to 9910081 and so on.
If anyone can help me i would so appreciate it!!! Its for my job and i'm so lost, i can't figure it out. Thanks
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Sep 12, 2006
Messages
15,613
nextnumber = nz(dmax(targetfield,targetdomain),9910079)+1

ie - return the highest number in the table, and add 1 to it - but if there is no number at all then start at 1 less than the first number you want, and add 1 to THAT.
 

fairygirl85

New member
Local time
Today, 01:18
Joined
Apr 21, 2009
Messages
2
Do you know where i put the code?? When i open access i press alt + F11 but then from there im stuck. Please help!!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:18
Joined
Sep 12, 2006
Messages
15,613
assuming this number is a "key" that you are using, then you need to generate this key immeditely before you save the record

so it belongs in the forms beforeupdate event.

----------
now if you want to show the value to a user, at an earlier point - it becomes more complex - ie if your last number is key 24, so you show your user 25. now another user will need to be shown key 26. but say the guy with key 25, cancels his operation, the second guy has used 26, so the next number will now be 27, and key 25 is lost.

THAT is why these values are normally only looked up when the record is ready to be inserted, and the insert wont be cancelled.

if this strategy is no good, you need to think of another way.
 

KathyL

Registered User.
Local time
Today, 01:18
Joined
Apr 17, 2009
Messages
48
I've done this before by having a table with one record in it with your starting number, and write a query to append it into an empty table with identical structure and an auto number.... and then it will continue from that number. It's been a while since I did it, but it worked. I may have something slightly off and I don't have time to test it tonight... but that should head you in the right direction.
 

Users who are viewing this thread

Top Bottom