Alternative to using Autonumber

[jlf]

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

[Killer42]

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.

[NeoPa]

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.

[Killer42]

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.

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

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

[jlf]

Thanks for your help, but I got it working.

[NeoPa]

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] :

Private Sub cmdNewTicket_OnClick()
    [txtHelpdesk Ticket No] = DMax("[Ticket No]", "[tblHelp Desk Tickets]") + 1
End Sub

[Killer42]

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.

[Killer42]

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?

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

[NeoPa]

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 ;)

[Killer42]

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

[NeoPa]

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.