Full Version: Creating a Ticket(Invoice) Number - automated
UtterAccess Forums > Microsoft® Access > Access Forms
I am trying to create a ticket number to be automatically updated on a form. I will be using letters and numbers. Is this possible?
lso, when I typed my macro on VB it seems ok. The macro is: MetxtTicketNumber=Nz(DMax("TicketNumber),"Ticket Data"),0)+1 TicketNumber is column and Ticket Data is data sheet. But when I use the form to input data, it has invalid syntax.
Do I need to setup and name macro first? Also how do I get my form box to use the macro?
Thanks for the help
Check out Glen Kruger's site, site, about halfway down the Access 2000 page there is a link to Increment Alphanumeric Field (Earl D. Brightup) which should help you out.
Hope that works for ya,
First of all, you'b be far better not using macros. Use VBA instead. Macros are very limited in what they can do and importantly you can't include error handling...............
ow, using VBA you use the Before Insert event of your form to generate your sequential invoice number like this
Me.txtTicketNumber = Nz(DMax("TicketNumber","YourTableName"),0) + 1
HAs for the text part of your invoice number, how are you determining what text to add and where (before the number, after it, in the middle of it) is it to be added. There's a strong chance that, depending where you're getting your text from, that you don't need to store it along with the number. If you can give an example of what you're trying to achieve..............
All tickets given to me have a ticket number already. The text will be before the number always. An example of a ticket number would be "C1858900". I would like to enter the first ticket number and then my program recognize the next ticket number should be "C1858901".
I am storing scaled weights. This will help with an inventory process. Included on the form also are vendors, inventory types, inventory sections.
Hope I have been clear enough.
I now have what you suggested above installed as a VBA but it is not updating the TicketNumber. I have even tried just using numbers with no letters and I get no updates either. Could you suggest something?
You downloaded the link above that I suggested? Why is it not working? Are you calling the IncrAlphaField function?
When I used it, I had no problem incrementing the field. You can call it like this :
Me.txtTicketNumber = IncrAlphaField(Nz(DMax("TicketNumber","YourTableName"),0))
Hope that helps,
I downloaded the IncrAlphaField function and saved it as a form. How do I use that in my database form? It is saving IncrAlphaNumeric in a seperate database. I think that there lies the problem. Hope you can pull me through this.
I now have frmIncrementalAlphaField in my database under forms. How do I get them linked so that it uses the Incremental Alpha Numeric to increase the field for the Ticket Number on the Ticket Data Form.
What the database you downloaded contains is the main function, IncrAlphaField, and a form, which is a driver. The driver provides you with a demonstration on how to use the function. What you need to do is import the code found in the form contained within that database, and use the function to increment your field.
opy all the code found in that form into your form which is going to increment the field. If you need it on multiple forms, place the code into a new module, and change the function headers to say "public" instead of "private"
Remove all the code that has to do with a form event (command buttons, detail section, etc).
Then, the part where you need to increment the field, use :
nameOfMyControl = IncrAlphaField((DMax("TicketNumber","YourTableName"))
and that will do it. If you're *still* having problems, upload your db, and i'll put the code in place.
I tried and cannot get it to work. I am forwarding my database with hopes you can place the code and link it. Thanks
Ok, done and done.
irst thing i'd do is suggest normalizing your tables a little more. You have several lookup tables that are text only. they should have a primary key (autonumber). You can still display things as text, but use the number as a reference instead of the text itself. This will save you a lot of hassels (it will also cut down on database size and repeating data).
Now, whenever you go to a new record, the ticket number is incremented by one. I noticed that there was no letters present in the ticket number. If that is the way that it is going to stay, you don't need that bulky function. I was under the impression that the ticket number contained both letters and numbers. If that is the case (no letters), you can replace the
nameOfMyControl = IncrAlphaField((DMax("TicketNumber","YourTableName"))
Nz(DMax("TicketNumber","YourTableName"),0) + 1
and that will do the trick.
I would also place some error-checking in to prevent half finished records.
Attached is a copy of your db
Can you make the first ticket number C0000000 so that it will increment with the letter first? You can let me know what I need to change to make that happen if you don't mind. I appreciate all of your help.
Sorry Adam. Just noticed all I had to do was put the letter before the number on ticket one and it worked great. I really appreciate all of your help. You rock. Thanks again.
Let me pop in here with some background that might help you understand the process. Generally, the reason someone does this is because they are planning on using coded number that has meaning to the user. For example, the public number may include a date element and a user element. So lets say you wanted the "ticket" to indicate the year and responsibile person it might look like:
The point is that the incremented number is only a part of the coded number. The rest of the coded number is picked up from other data in the record. Because of these facts, the only thing that is stored is the incremented number. This number is then incremented for new records using a DMax. The rest of the coded number is derived from existing data and displayed, not stored. For example:
Format(Year(DateField),"yy") & "-" & Format(Increment,"000") & "-" & Left([First], 1), & [MI] & Left([Last],1)
Often such a coded number is reset at the beginning of a period. So you would use:
=Nz(DMax("[Increment]","table","Year([datefield]) = " & Year([txtDatefield])),0)+1
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.