Access 2007 Database - Invoicing form

[davin_x]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

 

[OBP]

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]

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

 

[davin_x]

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]

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]

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.