UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

 
   Reply to this topicStart new topic
> Creating a Ticket(Invoice) Number - automated    
 
   
paperman
post Apr 24 2006, 05:01 PM
Post#1



Posts: 57
Joined: 15-April 06



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
Go to the top of the page
 
adamsherring
post Apr 24 2006, 06:26 PM
Post#2


VIP Emeritus
Posts: 1,750
Joined: 16-June 05
From: The Great White North


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,
Adam
Go to the top of the page
 
Alan_G
post Apr 24 2006, 06:38 PM
Post#3


Utterly Yorkshire and Forum/Wiki Editor
Posts: 16,302
Joined: 12-January 03
From: Devon UK


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..............
HTH
Alan
Go to the top of the page
 
paperman
post Apr 25 2006, 07:20 AM
Post#4



Posts: 57
Joined: 15-April 06



Thanks
Go to the top of the page
 
paperman
post Apr 25 2006, 07:27 AM
Post#5



Posts: 57
Joined: 15-April 06



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.
Go to the top of the page
 
paperman
post Apr 25 2006, 03:47 PM
Post#6



Posts: 57
Joined: 15-April 06



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?
Go to the top of the page
 
adamsherring
post Apr 26 2006, 09:08 AM
Post#7


VIP Emeritus
Posts: 1,750
Joined: 16-June 05
From: The Great White North


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,
Adam
Go to the top of the page
 
paperman
post Apr 26 2006, 10:03 AM
Post#8



Posts: 57
Joined: 15-April 06



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.
Go to the top of the page
 
paperman
post Apr 26 2006, 10:35 AM
Post#9



Posts: 57
Joined: 15-April 06



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.
Go to the top of the page
 
adamsherring
post Apr 26 2006, 11:41 AM
Post#10


VIP Emeritus
Posts: 1,750
Joined: 16-June 05
From: The Great White North


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.
Adam
Go to the top of the page
 
paperman
post Apr 26 2006, 02:28 PM
Post#11



Posts: 57
Joined: 15-April 06



I tried and cannot get it to work. I am forwarding my database with hopes you can place the code and link it. Thanks
Attached File(s)
Attached File  TicketDataDatabase.zip ( 154.23K )Number of downloads: 14
 
Go to the top of the page
 
adamsherring
post Apr 26 2006, 02:45 PM
Post#12


VIP Emeritus
Posts: 1,750
Joined: 16-June 05
From: The Great White North


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"))
with
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
Adam
Attached File(s)
Attached File  TicketDataDatabase.zip ( 158.46K )Number of downloads: 55
 
Go to the top of the page
 
paperman
post Apr 26 2006, 02:59 PM
Post#13



Posts: 57
Joined: 15-April 06



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.
Go to the top of the page
 
paperman
post Apr 26 2006, 03:02 PM
Post#14



Posts: 57
Joined: 15-April 06



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.
Go to the top of the page
 
ScottGem
post Apr 26 2006, 03:07 PM
Post#15


UtterAccess VIP / UA Clown
Posts: 28,934
Joined: 21-January 04
From: LI, NY


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:
6-111-SBD
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
Go to the top of the page
 


RSSSearch   Top   Lo-Fi    25th May 2015 - 10:33 PM