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

Alternative to using Autonumber

P: 12
I would like to know the code to use in an Access form that would be an alternative to using autonumber. My source table is called tblHelp Desk Tickets and my number table is called tblNextNum. The field I am using to for the next number to be pulled from is NextNumber. The control box on the form is called Helpdesk Ticket No, txtHelpdesk Ticket No.

Would apprecaite any help in this area, I am not that familiar with creating codes. Would also like the ticket number to pop in when I click on the create ticket button, so I don't know where within the code screen to even put this information.

Thanks so much,
Johnny
Jan 4 '07 #1
Share this Question
Share on Google+
13 Replies


Expert 5K+
P: 8,434
I would like to know the code to use in an Access form that would be an alternative to using autonumber. My source table is called tblHelp Desk Tickets and my number table is called tblNextNum. The field I am using to for the next number to be pulled from is NextNumber. The control box on the form is called Helpdesk Ticket No, txtHelpdesk Ticket No.

Would apprecaite any help in this area, I am not that familiar with creating codes. Would also like the ticket number to pop in when I click on the create ticket button, so I don't know where within the code screen to even put this information.
You should be able to create a public function which returns the next number and updates tblNextNum. Then depending on how your form works, maybe one of the following:
  • In the click event for the create button, assign the value returned by your function to the ticket number control.
  • Do something similar in the forms Before Insert or After Insert event.
  • Maybe just set the function name as the default value for the ticket number field in [tblHelp Desk Tickets]. If it achieves what you want, this is probably the simplest.
Jan 4 '07 #2

NeoPa
Expert Mod 15k+
P: 31,660
You haven't really said exactly how you want it to work.
Do you want to reuse any number that gets finished with?
Do you just want to keep counting upwards infinitely?
The answer would depend on the answer to this question.
Jan 7 '07 #3

Expert 5K+
P: 8,434
You haven't really said exactly how you want it to work.
Do you want to reuse any number that gets finished with?
Do you just want to keep counting upwards infinitely?
The answer would depend on the answer to this question.
True. also, something I forgot to mention. Not sure how important it will be in the Access environment, but any public function which does the number-generation stuff I mentioned, should do it in a separate transaction. In other words, it should BeginTrans, generate/update the next-number value, then CommitTrans. And this part of the process should be done as quickly as possible, to reduce contention for the control record.
Jan 8 '07 #4

P: 12
jlf
You haven't really said exactly how you want it to work.
Do you want to reuse any number that gets finished with?
Do you just want to keep counting upwards infinitely?
The answer would depend on the answer to this question.

I want a unique number every time I want to create a new ticket. No number will be reused. A good example of what I am trying to do would be when someone creates a new purchase order request and it gets assigned a unique purchase order number. I just want an automatic number to pop into the helpdesk ticket control box. Hope this helps.
Jan 8 '07 #5

P: 12
jlf
True. also, something I forgot to mention. Not sure how important it will be in the Access environment, but any public function which does the number-generation stuff I mentioned, should do it in a separate transaction. In other words, it should BeginTrans, generate/update the next-number value, then CommitTrans. And this part of the process should be done as quickly as possible, to reduce contention for the control record.

I apologize, but I am a complete newbie, so not really following along on your suggestion.
Jan 8 '07 #6

P: 12
jlf
Thanks for your help, but I got it working.
Jan 8 '07 #7

NeoPa
Expert Mod 15k+
P: 31,660
It should not be necessary to store the numbers separately in a table.
We can get the next number by simply looking at the numbers already in use and adding one to the highest found.
Assuming your command button is called cmdNewTicket and the field in your table [tblHelp Desk Tickets] is called [Ticket No] :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdNewTicket_OnClick()
  2.     [txtHelpdesk Ticket No] = DMax("[Ticket No]", "[tblHelp Desk Tickets]") + 1
  3. End Sub
Jan 8 '07 #8

Expert 5K+
P: 8,434
It should not be necessary to store the numbers separately in a table.
We can get the next number by simply looking at the numbers already in use and adding one to the highest found.
...
Well yes, but that sort of assumes a single user.

In a multi-user setup, this would only work if you can somehow ensure no overlap. For instance, assign the number and immediately store your record containing it, so that the next person using the DMax will see yours. Even then, to be really sure, you'd have to check again after storing the record, to ensure you didn't create a duplicate.

Seems to me, a separate "assigned numbers" table would allow more reliability, with less work.
Jan 8 '07 #9

Expert 5K+
P: 8,434
I apologize, but I am a complete newbie, so not really following along on your suggestion.
Don't worry too much. I suppose the situation can be dealt with, if and when it occurs.

However, I did have one question for you. Why can't you just use an AutoNumber?
Jan 8 '07 #10

P: 12
jlf
I did exactly what Neopa suggested and it is working fine. This is not a multiuser environment. I chose this method because of past experiences with autonumber and this also gives us the option to assign a year to each ticket and not starting diirectly with 1, we are starting with 20070001. This is per request of management, but thank you again for your assistance.
Jan 8 '07 #11

NeoPa
Expert Mod 15k+
P: 31,660
Post #9
Killer,
Would you really want to take someone through those complexities on a web site. Your courage and fortitude amaze and impress me ;)
Jan 8 '07 #12

Expert 5K+
P: 8,434
Killer,
Would you really want to take someone through those complexities on a web site. Your courage and fortitude amaze and impress me ;)
Of course not - that's what we have Access experts for. :D
Jan 8 '07 #13

NeoPa
Expert Mod 15k+
P: 31,660
Killer,
Would you really want to take someone through those complexities on a web site. Your courage and fortitude amaze and impress me ;)
Of course not - that's what we have Access experts for. :D
Oops - I feel I've just been hit for six.
Jan 8 '07 #14

Post your reply

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