UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

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

UtterAccess Enthusiast
Posts: 57



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
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,206
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

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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: 13
 
Go to the top of the page
 
+
adamsherring
post Apr 26 2006, 02:45 PM
Post #12

VIP Emeritus
Posts: 1,750
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: 47
 
Go to the top of the page
 
+
paperman
post Apr 26 2006, 02:59 PM
Post #13

UtterAccess Enthusiast
Posts: 57



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

UtterAccess Enthusiast
Posts: 57



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: 27,540
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
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 30th September 2014 - 04:46 AM