Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 Database - Invoicing form


(!)

davin_x's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
05-Apr-2010, 01:22 AM #1
Access 2007 Database - Invoicing form
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.
Attached Files
File Type: zip Invoicing Sample.zip (43.8 KB, 1272 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
05-Apr-2010, 06:27 AM #2
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-a...pair-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
I do not give up easily
davin_x's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
05-Apr-2010, 07:40 AM #3
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
Attached Files
File Type: rar Invoicing Sample Access 2003.rar (63.9 KB, 499 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
05-Apr-2010, 08:25 AM #4
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's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
05-Apr-2010, 10:14 AM #5
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
Attached Files
File Type: rar Invoicing Sample Access 2003 v1_1.rar (56.7 KB, 442 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
05-Apr-2010, 11:38 AM #6
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's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
05-Apr-2010, 11:04 PM #7
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's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
05-Apr-2010, 11:18 PM #8
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Apr-2010, 05:39 AM #9
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's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
06-Apr-2010, 05:57 AM #10
Here we go, think i have the InvoiceRow table (not too sure if its right though)
Attached Files
File Type: rar Invoicing Sample Access 2003 v1_2.rar (51.6 KB, 271 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Apr-2010, 06:33 AM #11
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.

Last edited by OBP; 06-Apr-2010 at 07:20 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Apr-2010, 07:26 AM #12
Anyway, here is a preliminary version for you to look at.
Attached Files
File Type: zip Invoicing Sample Access 2003 v1_3.zip (34.8 KB, 377 views)
davin_x's Avatar
davin_x davin_x is offline
Junior Member with 20 posts.
THREAD STARTER
 
Join Date: Apr 2010
06-Apr-2010, 09:12 AM #13
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Apr-2010, 09:27 AM #14
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,440 posts.
 
Join Date: Mar 2005
Location: UK
06-Apr-2010, 09:50 AM #15
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.
Attached Files
File Type: zip Invoicing Sample Access 2003 v1_3.zip (81.4 KB, 674 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑