none
Generate alphanumeric invoice no on access report and reset number every month

    Question

  • I need to create an invoice report and generate alphanumeric invoice no using Access 2007.

    I am now in a stage whereby I trying to run a month of invoices out but the invoice number do not generate correctly.

    I want to generate Jan Invoices. Invoice no should be increment on every page of the invoices instead just assign a number on every page only. And will increment when the next round I generate invoices.

    Example: When I click on “Generate”, invoice no on every page will just assign by an invoice no e.g.” ABC2011/01/001” instead of “ABC2011/01/001, ABC2011/01/002, ABC2011/01/003 etc.”

    ABC2011/01/001 = ABCyyyy/mm/seqNo

    And hope to reset the seqNo every month.

    Example:

    For Jan; ABC2011/01/001, ABC2011/01/002, ABC2011/01/003. Then when comes to Feb; ABC2011/02/001, ABC2011/02/002, ABC2011/02/003.

     

    Thank you very much.

     

    Wednesday, February 23, 2011 6:44 AM

Answers

  • You are doing real good.

     

    Step 5) I didn't know it you did anything in excel before before printing.  You can add additional formating if you like.  You also said the following and not sure what was the best way of accomplishing this

    "I want to generate Jan Invoices. Invoice no should be increment on every page of the invoices instead just assign a number on every page only. And will increment when the next round I generate invoices."

     

    Step 11 ) From VBA menu make sure you have the project window opened.  From VBA menu

    View - Project Explorer

    In the project explorer look for VBA Project - Module - Excel objects - Module 1.  The macro should be in Module 1.  You may need to double click Module 1 before you can see the code.

     


    jdweng
    • Marked as answer by Bruce Song Tuesday, March 08, 2011 3:47 AM
    Friday, February 25, 2011 11:20 AM

All replies

  • Do you have a macro ruuning on your database to print the invoice?  It looks like you have some sort of macro running theat is generating the report. Without seeing the macro I can't give an specific solution to the problem.

     

    the dates are string and can easily be generated using VBA functionss.  For eaxmple code like this would work

    MyDate = DateValue("2/1/2011")

    InvoicePrefix = "ABC"

    InvoiceNumber = 1

    Invoicename = InvoicePrefix & format(MyDate,"YYYY/MM") & "/" & format(InvoiceNumber,"000")

     

    If you don't generate the invoice all at the same time you may need to store the last invoice number some place so you can get the last invoice number.  here are some ways people store the number

    1. Create a new table in access that has the invoice number with a filed for month and number.  Then use an SQL to get latest number for the current month
    2. Store the Invoices in a folder on the PC.  Then check the files in the folder to determine what is the last file in the folder
    3. Store the Invoice numbers in a single file.  then open the file up to determine the next invoice number for the month.

     


    jdweng
    Wednesday, February 23, 2011 7:09 AM
  • No, I don't have a macro running on my database.

    If I would like to generate the invoices all the same time, is there a way to do so?

    Thank you.

    Wednesday, February 23, 2011 7:22 AM
  • There are ways of automatically generating invoices but I wouild like to start with the method you are presently using and create the automatic invoices so they appear the same as you old ones.  How are you presently generating you invoices?
    jdweng
    Wednesday, February 23, 2011 12:37 PM
  • Xiuing,
     
        My sample db named AppendSequence might be of interest to you. It is in access 2000 file format and is available at Rogers Access Library. Link:
     
    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    From: xiuying
    Newsgroups: Msdn.en-US.accessdev
    Sent: Wednesday, February 23, 2011 12:14
    Subject: Generate alphanumeric invoice no on access report and reset number every month

    I need to create an invoice report and generate alphanumeric invoice no using Access 2007.

    I am now in a stage whereby I trying to run a month of invoices out but the invoice number do not generate correctly.

    I want to generate Jan Invoices. Invoice no should be increment on every page of the invoices instead just assign a number on every page only. And will increment when the next round I generate invoices.

    Example: When I click on �??Generate�?�, invoice no on every page will just assign by an invoice no e.g.�?� ABC2011/01/001�?� instead of �??ABC2011/01/001, ABC2011/01/002, ABC2011/01/003 etc.�?�

    ABC2011/01/001 = ABCyyyy/mm/seqNo

    And hope to reset the seqNo every month.

    Example:

    For Jan; ABC2011/01/001, ABC2011/01/002, ABC2011/01/003. Then when comes to Feb; ABC2011/02/001, ABC2011/02/002, ABC2011/02/003.

     

    Thank you very much.


    A.D. Tejpal
    Wednesday, February 23, 2011 5:33 PM
  • Manually generate via Excel.

    It is a tedious task when we have a lot of orders per month.

    Thanks.

    Thursday, February 24, 2011 3:38 AM
  • Hi A.D. Tejpal,

    Thank you very much. The year month wise sequence is similar to what I want.

    Can I ask how you get it done?

    Many thanks.

    Thursday, February 24, 2011 3:44 AM
  • Hi Xiuing,
     
        For deriving optimum benefit, you could try to study and grasp the working of sample db thoroughly. Based upon that, once you are in a position to re-create the pertinent functionality on your own from scratch, you would be able to adapt it suitably.
     
    Best wishes,
    A.D. Tejpal
    ------------
     
    ----- Original Message -----
    From: xiuying
    Newsgroups: Msdn.en-US.accessdev
    Sent: Thursday, February 24, 2011 09:14
    Subject: Re: Generate alphanumeric invoice no on access report and reset number every month

    Hi A.D. Tejpal,

    Thank you very much. The year month wise sequence is similar to what I want.

    Can I ask how you get it done?

    Many thanks.


    A.D. Tejpal
    Thursday, February 24, 2011 4:05 AM
  • It looks like you will need a macro.  Do you want a macro to automatically create the excel workbook and generate the report or do you want the macro to run entirely from Access.  You can even run a VBA macro in excel that imports the data from access.  I need more information to determine the best method in creating the invoices.

     

    1. Are you preforning a query to get the invoice data?  If so is the query in Access or Excel.
    2. How are you moving the data from Access to Excel?
    3. What formating are you performing in excel before geerating the invoice?
    4. Are you saving the invoice from excel in a different format than an excel workbook.  Or are you just printing the invoice or sending the invoice as an email?

    Provide any more details if possible.

    One thing we can do is to record an excel macro while yoiu are performing the operatings in excel to make it easier for me to see what you are really doing.


    jdweng
    Thursday, February 24, 2011 12:17 PM
  • Dear xiuying

    Try This Code.

    1. Call Last Inv. No.. AS ALPHA_INV

    2. Declare a number AS LST_INV_NUM

    3. lets Name INV_NUMBER AS text.field

    Dim LST_INV_NUM As Integer

    LST_INV_NUM=Right(ALPHA_INV, 3)

    INV_NUMBER = "ABC" & Trim((Year(Date))) + "/" + Trim(Month(Date)) + "/00" + (LST_INV_NUM + 1)

     

    This should Work


    Inaocha
    • Proposed as answer by inaocha Thursday, February 24, 2011 5:22 PM
    Thursday, February 24, 2011 5:19 PM
  • Dear Inaocha,

    Can I confirm my understanding with you? Sorry about it. I was first time asked to create an Access Program.  All those lines of code, do I put it behind my “Generate” button? Or create a module? Do I need to create a table to store the invoice number?

    It will be great if you able to take some time to explain to me. Thank you.

    Many thanks.

    Best Regards,

    Xiu Ying.

    Friday, February 25, 2011 2:39 AM
  • Hi Joel Engineer,

    I will briefly explain what I have tried and what I want. I now perform a query to retrieve data from Orders table etc. After that I create a report to display the data. The report end result will be the invoices. After run out the reports, I would like to export out as PDF format. So that I can print out the entire invoices to keep a copy for tracking and hope can retrieve specify invoice that I would like to send via email based on the customer preferences.

    Currently, we manually copy and paste into the excel template that created by us. Then after that save in PDF format.

    Hope the details that I provided are useful.

    It will be great that if you are able to determine a way for me to create invoices.

    Many thanks.

    Best Regards,

    Xiu Ying
    Friday, February 25, 2011 3:21 AM
  • Dear xiuying

     

     1. create a table.

    2. Create a form

    3. Create a Query calling Last record

    4. Create a Button and put said code on click_event

     

    nb:- If this does not suffice to you. give me your Email:-/ Contact so det i can send sample to you

     

    Thank You


    Inaocha
    Friday, February 25, 2011 6:07 AM
  • Dear Inaocha,

    Does this able to apply to the report also?

    Ok. This is my email add: xy_hyc@hotmail.com.

    Thank you very much!

    Best Regards,

    Xiu Ying.

     

    Friday, February 25, 2011 7:01 AM
  • Hai

    Form is for real-time record edit/Update/delete. and Report is for viewing data/record which already there. having nominal function.

    therefore

    i will not suggest you to put funtion into report..

    and i ll be sending sample on said emai: ASAP

     


    Inaocha
    Friday, February 25, 2011 7:51 AM
  • Hai : I think the best way of getting started it to write the macros in excel because you are already using an excel template.  What I would like youi to do is to record a macro in excel and do the follwoing steps in a new excel workbook

     

    1) Open new Workbook.

    2) Start Recroding a macro

    3) Create a new database query from data menu. 

    4) Open Excel template

    5) Copy Query results to template as you would normally do

    6) Make any changes you would normall do to template

    7) Create PDF

    8) Stop Recording

    9) Press Alt-F11 to get to VBA window where record macro is located

    10) Copy recording macro to this forum posting.


    jdweng
    Friday, February 25, 2011 8:00 AM
  • Hi Joel Engineer,

    Sorry, I don’t understand.

     

     

    Friday, February 25, 2011 8:23 AM
  • What version of excel are you using
    jdweng
    Friday, February 25, 2011 8:31 AM
  • Excel 2010.

    Friday, February 25, 2011 8:36 AM
  • I don't have Access 2010 so i'm using the 2007 menu.  I might have some minor errors in the instructions.  You may want to display the Developer tab by Going to the window ICON (upper left button in excel) and select Options on the bottom of the window.  Then click the check box "view Developers Tab".  use the Developers Tab instead of the View tab in the instructions below.

     

    1) Open new Workbook.  Save the workbook as macro Enabled Workbook (xlsx)

    2) Go to view ribbon.  Click macro ICON and Start Recroding a macro

    3) Create a new database query from data menu: Go to Import Ribbon.  Click From other sources.  Select Databases and located your Access file.  Follow instructions in Wizrd to create the Query like you would normall do from Access.  At the end of the wizrd you will specify where in the workbook you want to put the query.  Select cell A1.

    4) Open Excel template from the excel worksheet menu File - Open

    5) Copy Query results to template as you would normally do from the query reuslts to the template

    6) Make any changes you would normall do to template

    7) Create PDF

    8) Stop Recording : Go to menu View - Macro - Stop Recording.

    9) Press Alt-F11 to get to VBA window where record macro is located

    10) Copy recording macro to this forum posting.


    jdweng
    Friday, February 25, 2011 8:50 AM
  • I have problems with step 3.  Cause previous records, is key in the excel spreadsheet.

    Friday, February 25, 2011 9:02 AM
  • Select any one Key.  We will modify the macro later to go through all the keys later.  This test is just to get one invoice working.


    jdweng

     

    You may have to delete a previous query if you stop in the middle.  either put the query on a new worksheet or or select the entire worksheet (cntl A) and delete.  you will be asked if you want to delete the previous query.

    Friday, February 25, 2011 9:11 AM
  • Ok. I key in one order into the database. When comes to step 3, I select “From other sources” -> “From Microsoft Query” -> then I select my database. Am I right?

    Then as for step 5, what do you mean I would normally do from the query results to the template?

    Comes to step 9, I Alt-F11, there is nothing inside the VBA window.

    Friday, February 25, 2011 9:44 AM
  • You are doing real good.

     

    Step 5) I didn't know it you did anything in excel before before printing.  You can add additional formating if you like.  You also said the following and not sure what was the best way of accomplishing this

    "I want to generate Jan Invoices. Invoice no should be increment on every page of the invoices instead just assign a number on every page only. And will increment when the next round I generate invoices."

     

    Step 11 ) From VBA menu make sure you have the project window opened.  From VBA menu

    View - Project Explorer

    In the project explorer look for VBA Project - Module - Excel objects - Module 1.  The macro should be in Module 1.  You may need to double click Module 1 before you can see the code.

     


    jdweng
    • Marked as answer by Bruce Song Tuesday, March 08, 2011 3:47 AM
    Friday, February 25, 2011 11:20 AM
  • Hi Umisha Shah,

    Thanks you.

    Is this program created using microsoft acces?

    Wednesday, March 09, 2011 4:21 AM
  • xiuying download is a visual studion project written in C#.  You can download a free copy of visual studio express from msdn if you want to go that approach.  You can get similar results using Access VBA or Excel VBA.  The visaul Studio forms are a little nicer than the VBA code and Visual Studio you get an executable file which will run faster and you don't have to deliver source code when distributing the software.

     

    If you are using are just using the application in house and have experiece with VBA doing it in access/excel is fine.  Especially if you already know Visual Basic (VBA) and don't want to learn C#.

    I'm still waiting to see your recorded excel macro.  If you prefer I will take this discussion off line and send you an email at the email you posted.


    jdweng
    Wednesday, March 09, 2011 5:53 AM
  • Hi Joel Engineer,

    Thank you for your reply. Sorry. Cause I have problem in following your instructions and I tried and couldn't get the result.

     

    Wednesday, March 09, 2011 6:46 AM