1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Database - Invoicing form

Discussion in 'Business Applications' started by davin_x, Apr 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. davin_x

    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.
     

    Attached Files:

  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  3. davin_x

    davin_x Thread Starter

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

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  5. davin_x

    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
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  7. davin_x

    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
     
  8. davin_x

    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 )
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
    .
     
  10. davin_x

    davin_x Thread Starter

    Joined:
    Apr 5, 2010
    Messages:
    20
    Here we go, think i have the InvoiceRow table (not too sure if its right though)
     

    Attached Files:

  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Anyway, here is a preliminary version for you to look at.
     

    Attached Files:

  13. davin_x

    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.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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:

  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/914788