# Access 2007 Database - Invoicing form



## davin_x (Apr 5, 2010)

Greetings,

I'm trying to develop / design a very simple Access 2007 database that will allow me to create invoices.

Invoices:
1. Auto numbered invoices.
2. Ability to select the customer code (and then display the customer name / balance of 'open' invoices).
3. Able to select inventory info (rows) with item unit price, quantity, row total etc.
4. Invoice info (and total) should be able to be viewed under a tab in the Customer Master.
5. Able to key in a payment receipt details in a seperate tab under each invoice (multiple rows) which will allow for printing of a Receipt document (vs total invoice, if there is an example of partial payments). Payment receipt can not exceed the invoice total.
6. All invoices in total should be able to be updated backwards in regards to $ total on the Customer Master.

I'm able to create the 'master data' like Customer Master, Employee Master, Inventory Master, but I have no idea on where and how to start with the Invoice area.

Any help would be great.


----------



## OBP (Mar 8, 2005)

davin_x, welcome to the forum.
I would help you, but I do not have Access 2007, so to see your database it would need to be re-formatted in Access 2000-2003.
You could have a look at the database that I posted here http://forums.techguy.org/business-applications/913990-solved-suggestions-computer-repair-shop.html
on post #11 which shows how to create the Invoice Report. It also uses VBA to generate the Invoice Number in the Format YYYY - 000 where YYYY is the Current Year and 000 is incremented for each year and resets back to 001 for a new year.


----------



## davin_x (Apr 5, 2010)

Greetings,

Well I can just save a sample into Access 2003, so we can review / discuss the tables / function and features?

Thank you in advance!

Dav


----------



## OBP (Mar 8, 2005)

Dav, I assume that you will want an Invoice per Sales Transaction?
In which case you need a "Sales" Table which will join your Customers and Inventory Tables using One to Many relationships. You will need fields something like
SalesID - Autonumber - Key Field
CustomerID - Number type Long
InventoryID - Number type Long
SalesDate - type Date/Time
InvoiceNumber - type Text
Comments - type Text?
Quantity - type Number Integer


It would be best if you changed your current table ID fields to CustomerID and InventoryID to avoid Confusion later in queries and reports.


----------



## davin_x (Apr 5, 2010)

Ok good call on the CustID and InventoryID.

Changed those accordingly, also create a table for the Invoice.

Since the Invoice table is the "header data", should I create another table called InvoiceRows that will contain the fields / values:


Item Code
Item Details
Unit Price (sales)
Quantity
Row total (Unit price x Quantity)
Remarks
Updated the Access file with v1_1.

Thank you.

Dav


----------



## OBP (Mar 8, 2005)

If you want a separate table for the Items then it needs the InvoiceID in it to link to the Invoice record, but does not need Unit Price or usually the Row Total either. The Row Total can be calculated at any time.
Your Inventory Prices can be a problem as well, if they change do you want to record their changes?


----------



## davin_x (Apr 5, 2010)

I think to be on the safe side, data updated into each individual invoice should remain 'individual' and any changes on the master data (IE: Customer master or inventory master) should not roll back and reflect Invoices created before the changes were made.

I would not want a case where I've posted Invoice #1 for $100 and then amend an Item's Price, then 6 months later realise that Invoice #1 has changed to $110


----------



## davin_x (Apr 5, 2010)

I've been searching online for a sample of how to do an invoice with Access (2003 / 2007) but I cant seem to find a database to review / look at.

Of course, if I had a basic DB to look at, I can then add-on to that in regards to the Customer/Inventory Table.

As mentioned, I'm trying to create an Invoice that will allow me to select a customer, then in the row level, select a number of stock items (drawing the current price etc of the stock) and maybe having a few formula's to calculate the row total (lets say there's tax on the row level, maybe even calculate that) and then another formula to calculate the entire Invoice total (if there are discounts, to deduct that value as well).

I'm not concerned with stock quantity (like in a warehouse etc) since to me all my items are 'non-stock' and if they are stock, I'll manage the availability manually anyway.

Thank you for responding, looking at how we can work this out together (so at least I learn something!  )


----------



## OBP (Mar 8, 2005)

dav, I posted a link to a Database containing Invoices in post #2, although it doesn't contain a separate Invoice Table the principle is the same.
If you have created your InvoiceRows Table with an InvoiceID field to link it to the Invoice table, post the new database and I will show you how to create what you want.
.


----------



## davin_x (Apr 5, 2010)

Here we go, think i have the InvoiceRow table (not too sure if its right though)


----------



## OBP (Mar 8, 2005)

dav, do you always supply Companies, or do you also supply individuals?
I am trying to define a "Customer" in terms of data entry and then Selection.
What is the "Row Details" field in the Invoice Table for?
Which "Price" would you use for the Invoice, Unit or Default?

ps you are up early.


----------



## OBP (Mar 8, 2005)

Anyway, here is a preliminary version for you to look at.


----------



## davin_x (Apr 5, 2010)

Woah, this is looking pretty close to what I was aiming for.
I deal with individual / companies but I'm pretty ok with what I have right now. What I could do is that when we're able to link the Customer code to the Invoice, we could have a button that says 'Print Invoice' (for the current selected invoice).


Only a few things:

1. I'd like to be able to select the Customer code (maybe a drop down that show the Customer Code and Customer Name?).
2. For the Invoice rows, is it possible for the Item Sales Price (Unit Price) to show up automatically when I select the Item Code, and then have the Quantity x Unit Price = Row Total?
3. The individual Row Total's would then be totaled up for Invoice Total.


----------



## OBP (Mar 8, 2005)

Re question 2, that is why I asked the questions about Unit or Default prices as I din't know which one to use in the calculations.
The Customer code is automatically entered in to the Invoice due to the Master/Child Links of the Mainform/Subform set up.
The Find a company Combo selects the customer for you, you can have the same thing for the Customer's names
It all happens on the Tabbed Customer form, have a look at the Invoice Tab.
I will post an updated version.


----------



## OBP (Mar 8, 2005)

dav, I have added the calculations and a Find a Customer Combo, an Inventory Tab and moved the Invoices Tab to the first one. The total is updated when you enter a Quantity or select a different Item if the Quantity has already been entered.
I have made the Customers form the "opening" form.
I have not spent too much time on formatting etc.
Note that the "ID" fields are currently not "Enabled" (greyed out), in fact the users should not even see them, so they should be set to "Invisible = No".
Also the Record Selectors on the Row Details sub-subform do not need the Navigation Buttons showing.


----------



## davin_x (Apr 5, 2010)

I downloaded the last zip file, but I cant seem to have the Unit Price auto appear and the row total to calculate accordingly.

Also, I would require some form of InvoiceTotal value to be saved back to the Invoice table (ie the total of every row, per invoice).

No worries on the formatting, as I'll spend time on that.

Thank you.


----------



## OBP (Mar 8, 2005)

dav, sorry, that was not the latest version, I seem to have lost that somewhere between finishing and zipping it.
I will have to recreate it.


----------



## OBP (Mar 8, 2005)

This is what it should have looked like.


----------



## davin_x (Apr 5, 2010)

I cant see an attachment link? Unless you've updated the previous attachment? Will look at either way


----------



## OBP (Mar 8, 2005)

dav, you were right it didn't take the attachment, I have added it again.


----------



## davin_x (Apr 5, 2010)

Cool, just looked at it.

In terms of the invoice, i think it should be (on the rows):
Quantity x ItemDefaultSalesPrice (from the Inventory table).

For the row level tax, I think leaving it as a text field (with no calculation - I believe I can do that when I create the Invoice Report / Layout).

How do we get the Invoice total then?

In terms of the Invoice total, is it possible to have these fields as well:

A discount field (in $ total)
Invoice total (total of all the row totals - discount field)

I will then add additional info like mailing address, ship to address etc to the Invoice table for printing use, and draw additional info from the Customer table as well just in case.

Thank you


----------



## OBP (Mar 8, 2005)

RE - "I will then add additional info like mailing address, ship to address etc to the Invoice table for printing use, and draw additional info from the Customer table as well just in case."
That is not the way that it should be done, all "Customer" data should be in that table and picked up from the CustomerID in a Report Query.

The original Combo had the ItemDefaultSalesPrice which is why I asked you which one you wanted to use.


----------



## davin_x (Apr 5, 2010)

I can understand why it would make sense to pickup all "customer" data from the Customer table via a report query, but in most cases, I may want to send the Bill To Invoice to a different address, the same as the Ship To address.

If the Bill To or Ship To address changes in the Customer table, I would not want it 'flowing' back and changing all my previous historical invoices as well.


----------



## OBP (Mar 8, 2005)

dav, you can do it that way if you want, but duplication is not normally recommended.
I normally recommend using a .snp version of the Printed Invoice as a separate storage of the Invoice.
Do yuo need to see the Total on the Form, or just on the Report?


----------



## davin_x (Apr 5, 2010)

Would be best if the total was on the form (discount field and total)


----------



## OBP (Mar 8, 2005)

If you are going to have a discount field and use it in the Invoice it will need to go in the Invoice Table.


----------



## OBP (Mar 8, 2005)

Here is the Subform with total Cost.


----------



## davin_x (Apr 5, 2010)

Oh man that is looking really good. I would still need the ItemDefaultSalesPrice to be used on the Invoice though.

In terms of the Discount (on the Invoice) I'll add the field and then use a formula / query to deduct the Invoice total (from the Rows).

Thank you


----------



## OBP (Mar 8, 2005)

dav, are you going to create the query and Invoice Report, or shall I do it?


----------



## davin_x (Apr 5, 2010)

It would be a great help if you could assist in creating the query and the Invoice Layout (report). I believe that with Access 2003, you're able to create a command button that will print the invoice, but how would you then set the Invoice Layout report to print the current on screen invoice?


----------



## OBP (Mar 8, 2005)

dav, there is a Report Wizard that creates most of the Report. I am busy on another database right now, but I will post something a bit later.


----------



## davin_x (Apr 5, 2010)

Ok, I'll muddle my way around the report writer / builder, and post it here for you to review accordingly


----------



## OBP (Mar 8, 2005)

dav, I will do it for you in a while.


----------



## davin_x (Apr 5, 2010)

Thank you sir


----------



## OBP (Mar 8, 2005)

dav, one thing that you will need to do.
In the InvoiceRows Table the cash fields are Number fields and they should be Currency fields.
If I set them they will be in GB £s, so you could "practice" it on your current copy.


----------



## davin_x (Apr 5, 2010)

Noted. I can always practice and re-create them to be the local currency over here (Kuala Lumpur, Malaysia)


----------



## OBP (Mar 8, 2005)

dav, I thought as you had quoted $ that you were in the USA.


----------



## OBP (Mar 8, 2005)

dav, how do you want to handle the Invoice Number, enter them yourself or have the System generate them?


----------



## davin_x (Apr 5, 2010)

I use $ in forums, saves the time of explaining what MYR is (those yanks!)


----------



## OBP (Mar 8, 2005)

In case you didn't see my post as we cross posted.
dav, how do you want to handle the Invoice Number, enter them yourself or have the System generate them?


----------



## davin_x (Apr 5, 2010)

I'd like to have invoice number auto number (generated) and have an Invoice Ref field (but this would be alphanumeric).


----------



## OBP (Mar 8, 2005)

Do you need both, what format is the Invoice Ref?
The other thing you will need to add to the Invoice Report is your Name/Company name & Address etc.


----------



## davin_x (Apr 5, 2010)

The Invoice Ref has to be Alphanumeric (characters / words / numbers) since some people use them accordingly. It would be best to be 200 char long i guess (longer would be good as long as people dont write an essay in it.

I can come up with a "CompanyMaster" table of some sort that will have the company name, vat info etc later.


----------



## OBP (Mar 8, 2005)

dav, here is the version with
the DefaultPrice on the form
a Print Invoice Button on the form
a query called Customers Invoices
a report called Customer Invoice, it is not very Formatted as that can take hours & hours, so I will leave that up to you to play with.
You can also setup the Page type that you use, it is curently A4.

Re the Invoice Number, the only problem with Autonumbers is if you cancel a record before finishing it increments the number and deleting the last Invoice also leaves gaps that can look suspicious to an Auditor or Tax man.
Do you want to store a .SNP version of the Invoice?


----------



## bretta91 (May 22, 2010)

Hello OBP ! I saw the Form u have created and it looks great :up: bcz also im having the same problem. Im doing a database project on inv oiceing and inventory. mostly includes same tables as u hav created for davin_x. I have created all tables and relationships but im not able to do the invoice form so that the uder can 

1. chose the item from a pull down list 
2. automatically after chosing the item, the price apearing in the price text box 
3. calculating the total amount to be paid


Can u give me some tips of how to do these? I really apreciate it ! thanks waiting ur reply pls.


----------



## OBP (Mar 8, 2005)

bretta, welcome to the forum.
1. the Item List is created using the Toolbox Combo Box Wizard.
2. & 3 both use VBA code like this
Me.UnitPrice = Me.Combo18.Column(3)
Me.TotalPrice = Me.Quantity * Me.UnitPrice


----------



## bretta91 (May 22, 2010)

hi OBP thns for the fast reply! 
i know how to insert the drop down list but how will i create a table that will add a new line once a table is filled ?

I still didnt learn VBA so will google it soon

thnx


----------



## OBP (Mar 8, 2005)

I am not sure what you mean by "but how will i create a table that will add a new line once a table is filled".
Access automatically puts new data in to the table and on the Forms the Record Selectors on the bottom of the form are for moving between records and the one with the arrowhead and "*" opens a new Record to enter new data.


----------



## bretta91 (May 22, 2010)

Im inserting the drop down list for item and textbox for price and quantity (in design view). In the form view ( when i click the form to make the invoice) i could chose the item from the drop down list inser the price and quantity BUT i can only insert 1 item, a new line is not beeing created to add another item (many items in 1 invoice). clear now ?


----------



## OBP (Mar 8, 2005)

What have you got your forms "Default View" set to?
If it Single Form change it to Continuous Forms


----------



## bretta91 (May 22, 2010)

hey OBP do u have an IM email can u send me ? if u dont mind please im not getting it


----------



## OBP (Mar 8, 2005)

I have private mailed you my email address.


----------

