# MS Access: Creating Invoice report



## Diane Herron (Dec 28, 2000)

I'm so close to completing the attached database but have run into a snag on the invoice report. When they select "Jobs by Customer" from the switchboard, they can then click the Invoice button to either preview or print the invoice for that particular job. The problem is this:
The current invoice date is set to Today() which of course will have a different date (for the same invoice) tomorrow and the day after, etc. I need the invoice date to always remain the same. In other words if they preview or print the invoice today, I would like the invoice date to always display today's date. I can't figure out how to do that.

The other problem is giving the invoice a unique number. Their current invoice numbering works like this:
If they are invoicing for April, their first invoice number will be 4 for the month, then 000. Their second invoice for the month will be 4 then 001. In other words, 4000 and 4001, and so on. The numbering then starts over in May with 5000, 5001, and so on. I'm unable to come up with an automatic numbering system for this also.

Thanks for any help you can offer!


----------



## OBP (Mar 8, 2005)

Diane, about the only way that you can "fix" the Invoice Date is to have an Invoice Table with a Print Invoice Date field.
This would make sense as you want to create Invoice numbers as well.
The best way to do what you want with the Invoice Number is with Visual Basic.
I am rather busy at the moment so if you can wait a day or so I can do it for you.
In the mean time if you could create an Invoice table with an InvoiceID field (Autonumber), an Invoice Number field (Number type Long) and an Invoice Print Date field (Date).
A query based on the new table and a simple Form based on the Query and then repost the database I will create you the VBA code.


----------



## Diane Herron (Dec 28, 2000)

Thank you so much! I have added InvoiceTbl, Invoice Query, and InvoiceFrm, with the fields you suggested.

I'm starting to think I'm in way over my head. 

The latest version with the new objects is attached.


----------



## OBP (Mar 8, 2005)

Diane, can I make sure that I am clear about the Invoice Date, do you want it

Once entered to stay fixed at that date?

or

When viewed always have the "Current" date showing?

I think that you might need to "Create an Invoice" before Printing or Previewing the Invoice to ensure that the users do not keep creating new Invoices for the same JobId.
I will need to add the Job ID to the new Invoice Tbl as well to ensure that you get the correct date and Invoice Number matched to the Job ID.


----------



## OBP (Mar 8, 2005)

Diane, I have added some VB code to your Print or Preview Invoice Form that checks if the JobID has an Invoice, if it doesn't already have one it opens a RecordSet based on a new query called "Last Invoice Number", which only loads the highest Invoice number from this Year's Invoices (as the numbers will be duplicated each year).
It then checks to see if the last invoice number has the first number matching the current Month Number, if it does it increments the number by 1.
If it doesn't match then it creates a new number with the current month plus "000".
It adds the Current date to the Invoice date field and the JobID number to the JobID field.

This data is held in the Invoicetbl table, I am not sure how you are going to put it in to your Report


----------



## Diane Herron (Dec 28, 2000)

Your question: do you want it once entered to stay fixed at that date? Answer: Yes.

I'm unsure as to whether the invoice numbering is working properly; on the InvoiceTbl, it has Invoice ID: 1, Invoice Date: 5/2/07, and the Invoice number is 4000. Based on the numbering system, if this is the first invoice of the month, it should have an invoice number of 5000 (month+000).

I'm also unsure now as to how to get this onto the Invoice report. I want it in the report header section. Maybe it's just too late (almost midnight here) but I'm drawing a blank as to how to get it in there.

Sorry it's taken so long for me to respond. I started a new job this week!

Thanks for all your help! You're amazing!


----------



## OBP (Mar 8, 2005)

Diane, the date doesn't match the Invoice Number because I was testing to ensure that you only get the last Invoice number of the current Year.
One of the problems that you have is that your "order" of opening the Form and Report are the wrong way around. The Print Review form should open first and then when you click it's button the Report should open.
Adding the Invoice data to the Report then becomes easy, just add the Invoice Table to the Query that the Report is based on.


----------

