Invoice Number

[Jakeman]

Hi World: Still learning access 2007, but in only two weeks doing well. My question is: setting up a “Work Order” system for a friend. She wants an “Invoice Number” to automatically show up in form as entering data from customer. The number would increment by one (1) for each new customer. She would like it be the format: “10-0001, 10-0002, 10-0003, & etc”. The ten (10-) is for the year … 2010. Does anyone have ideas on how to accomplish this?
Thanks for any help given!!

 

[speakers_86]

I'm sure you can do this, but don't make it the primary key. You may want something like InvoiceID (autonumber primary key)
InvoiceNumber (number)

 

[Jakeman]

Thanks for the reply! Clicked on your link ... interresting.

 

[speakers_86]

It's not quite done yet.

 

[Mr. B]

Jakeman,

It is possible to do exactly what you want to do.

Take a look at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=188675&highlight=invoice+number

Be sure to read my posts in this thread, especially toward the end. It should help you get the idea of just how to handle this issue.

 

[Jakeman]

Thanks Mr. B for the reply. As I stated I've only been using acces for about two weeks, so this looks like &*#!%)& to me. Seems like there must be an easer way!

 

[Mr. B]

Well, there may be other ways (there ususally are) but the method that I mapped out in that other thread is the best way.

One word of caution: As had been previously stated, do not try to use the AutoNumber field as a way of creating the Invoice Number. I may appear to work for a while but it will not do the job over the long haul.

 

[Mr. B]

Jakeman.

I have attached a zip file that contains a demo of a simplified method for incrementing an Invoice Number. The zip file contains a small demo database file and a Word document that provides instructions of how to test and try the demo as well as how to implement this functionality in your own database.

Let me know if this is helpful to you. If it is then it might be useful to others who are needing to do the same thing.

 

[Jakeman]

Thanks so much for your help. Well check it out soon.

 

[Jakeman]

Mr. B: I located and imported "CreateInvoice NumberDemo.mdb" and loaded module "modUtilities". Added the two fielfs "InvVal & InvNumber" to my database. Added to two textbox's to my form (changed to ""Control Source for both to point to new table entries). When I loaded the form "Create Work Order" the "InvVal" field showed "0" ... "InvNumber" field showed " ". What did I miss or what am I doing wrong? I attacted zip file.

 

[Mr. B]

Jakeman,

I have attached an updated copy of your database that you and attached to your previous post.

You did not set the "Default" value for each of the new controls on your form to the correct user-defined function. Take a look at the properties for each of the new controls and you will see what I did.

Also, I forgot to tell you that you would need to change the Table Name in each of the Functions in the modUtilities module. I also made this change. I am going to modify the instructions that I sent to you to include everything you would need. I am also planning to see if I can find some way to fix the functions to where someone would not have to modify anything in the module. Modifying VBA code of any kink seems to intimidate those who are not comfortable with programming, so I need to find a better and more simple way to implement this.

Hope this helps.

 

[Jakeman]

Thanks so much for your time & help with this issue!!!

You don’t know how much I appreciate it.
Jake

 

[Mr. B]

Glad to help, Jake.

There have been other posts on the newsgroup asking about doing this same thing, so I decided to see if I could come up with a method that even a beginner could implement (with instructions).

I have modified my demo version so that now there will be no need for any change to the VBA code in the module. When the Default value of the Name of the function is entered, the Name of the Table where the "InvVal" and InvNumber" fields are located will need to be entered in the parentheses like:
=MakeNxtInvVal(“TheNameOfYourTable”) Of course the "TheNameOfYourTable" would be the actual name of the table.

 

[Jakeman]

I download the zip file & unzipped it, then opened and click "Create Work Order". The Invoice Number fields shows "#Name?'. What do I need to do?

 

[SOS]

Works fine for me. What Service Pack level are you on for Access 2007?

 

[Jakeman]

I don't Know - where or how do I find out?

 

[SOS]

I don't Know - where or how do I find out?

See if this helps.

 

[Mr. B]

I check the file again to be sure that there was not a problem and I did not find any problem. As SOS said, it works for me here.

I have recreated the zip file and attached the new zip file in hopes that it might help, but I really doubt it.

 

[Jakeman]

Found recreated zip fle ... same as before Invoice Number file shows "#Name?'.

 

[SOS]

Found recreated zip fle ... same as before Invoice Number file shows "#Name?'.

So, again I will ask what service pack you are on for Access 2007 and also what version of Windows?