Access 2007 Database - Invoicing form

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

davin_x

Thread Starter
Joined
Apr 5, 2010
Messages
20
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.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Apr 5, 2010
Messages
20
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
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Apr 5, 2010
Messages
20
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

Thread Starter
Joined
Apr 5, 2010
Messages
20
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! :D )
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

davin_x

Thread Starter
Joined
Apr 5, 2010
Messages
20
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

Joined
Mar 8, 2005
Messages
19,895
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

Joined
Mar 8, 2005
Messages
19,895
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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top