Help Creating an Order Form/Invoice

[firefighter17103]

Hi All,

I am new to MS Access 07, & do not know any VB, on a new business adventure. I am running Office07 on Vista Home Premium.

I am in the process of creating a database that in the end I will be able to create quotes, bill out customers for security services, cctv products and installs,create purchase orders to my distributors being that I keep no inventory on hand, and track payments such as deposits on equipment.

What I need help with is creating an invoice I have tried and been looking everywhere for help. I know I need 2 more tables; OrderMaster, & OrderDetails; What I would like to know is how exactly do I need in the columns labled and how do I get it transfered to an invoice. In the Details part I would like to have the Product#, Quanty, Descript, OurPrice, Line Total.

Below is the tables I have already created. Any help or examples that anyone is able to provide would be greatly appreciated. Thank you in advance for help.

CUSTOMERS
CustomerID(PrimaryKey[AutoNumber]); CompanyName; Prefix(Look-up from "Prefix" Table); FirstName; LastName; Address; City; State(Look-up from "State" Table); Zip; PhoneNumber; Ext; CellNumber; FaxNumber; E-MailAddress; NewsLetter; Notes; CustomerSince; Referred; CustomerType(Look-up list); TaxExempt(Look up from Yes/No Table)

DISTRIBUTORS
Distributor(PrimaryKey); Contact Name; Address; City; State(Look-up from "State" table); PhoenNumber; Ext; FaxNumber; CellNumber; E-MailAddress; Website; Notes(Memo field); Terms(Look-up from "Terms" table); Limit

PRODUCTS
Prouct#(PrimaryKey); Manufacturer(Look-up from "Manufacturer" table); Distributor(Look-up from "Distributor" table); Category(Look-up from "Category" table); CCD; Lens; IR(Look-up from "Yes/No" table); #ofLED's; MinLux[Color]; MinLux[B/W]; MinLux[Sens-up]; Resolution[Color]; Resoltion[B/W]; OpticalZoom; DisgitalZoom; Style(Look-up from "Style" table); Control; AutoIris(Look-up from "Yes/No" table); Varifocal(Look-up from "Yes/No" table); Motorized(Look-up from "Yes/No" table); Day/Night(Look-up from "Yes/No" table); OutDoor(Look-up from "Yes/No" table); Heater(Look-up from "Yes/No" table); Fan(Look-up from "Yes/No" table); VandalProof(Look-up from "Yes/No" table); Mount(Table contained look-up list); CH; HDD; FPSRec; Format(Look-up from "Format" table); Media(Look-up from "Media" table); LAN(Look-up from "Yes/No" table); USB(Look-up from "Yes/No" table); AddtionalInfo(Memo field); SpecSheet(Attachment); Disontinued(Yes/No check box); MSRP; OurPrice; Cost; WorksWith(Look-up from "Product#" query); Desript

Employees
UnitID(PrimaryKey); FirstName; LastName; CellNumber; DC#; NYSecID#; ExpDate

I also have the following tables which are pretty much self-explanatory:
"State", "Prefix", "Yes/No", "PayType", "Terms", "Category", "Style", "Format", "Media", "Mount"

I apologize if this is to much info posted; if anyone would like to see my database please let me know I will be more than happy to send it. Once again I would like to thank everyone in advance for any help you can provide.

Joe M

[mshmyob]

Ok big job but assuming you have everything else working your two tables called Order and OrderDetails hold all the data about a specific order (invoice).

The Order table would have at a minimum the fields of:
OrderID (PK)
OrderDate
CustomerID (FK)

Your OrderDetails table would hold the detail of each line item for the Order:
OrderDetailID (PK) - make this an autonumber
OrderID (FK)
CustomerID (FK)
ProductID (FK)
OrderDetailSalePrice (picked up from product table - must store here in case of price changes in future)
OrderDetailQTY
OrderDetailDESC (your choice - again in case you change descriptions in future - picked up from product table)

Depending on how you do taxes that would have to be taken into effect also but I don't know enough of your business rules.

really before you start you need to identify ALL you business rules. You should also read up on normalization.

cheers,

[NeoPa]

Normalisation and Table structures should help with that.

[firefighter17103]

OK... I have created the "Orders" table w/ the following fields
OrderID(PK)
OrderDate
CustomerID(FK)
Salesman(FK)
ProjectedInstall

Sales Tax is 8.375% for services and goods.

For the "OrderDetails" table how do I get it to pull up the info such as product price and descript??

I want to thank you again for your help.

Cheers
Joe

[mshmyob]

A number of ways to get the price and description. A simple way is to make a combo box that looks to your ProductID in your product table and include the price and description fields. In the combo box hide the price and description and then in the AFTER UPDATE event of the combo box you can just refer to the combo box columns for the price and description and store in your OrdersDetail table.

or

You could just create the combo box to the productid in the product table and in the AFTER UPDATE event you could do a DLOOKUP in the product table for the price and description.

As for the tax if you only have one tax rate for both goods and services and it is always applied to all goods and services the percent value can be stored in the Order table. Again store it for the order because it may change in the future.

cheers,

Sales Tax is 8.375% for services and goods.

For the "OrderDetails" table how do I get it to pull up the info such as product price and descript??

I want to thank you again for your help.

Cheers
Joe

[firefighter17103]

I don't fully understand how to set up the AFTER UPDATE event and how to do a DLOOKUP. Can you please explain a little more.

As far as the tax rate you want me to create another field on my "Orders" table, correct??

Once again thank you.

Joe

[mshmyob]

For the tax rate yes you would create a new field in the Order table and store the tax rate for there. That way you can calculate the taxes paid for that order.

In the properties of your combo box you will fnd an EVENT tab and in that you will find an AFTER UPDATE event. In that event go to the code builder (press the 3 little dot button) then your code would be something like so:

dim dblProductPrice as Double
dim strProductDesc as String
 
dblProductPrice = DLookup("[ProductPrice]", "tblProduct", "[ProductID] = " & Forms!frmYourForm.cboProductID.Column(0))
strProductDesc = DLookup("[ProductDesc]", "tblProduct", "[ProductID] = " & Forms!frmYourForm.cboProductID.Column(0))
 
 

Where
ProductPrice and ProductDesc are the names of the fields in your Product table. tblProduct is the name of your Product table. frmYourForm is the name of the form where the combobox control is. cboProductID is the name of your combobox control.

cheers,

I don't fully understand how to set up the AFTER UPDATE event and how to do a DLOOKUP. Can you please explain a little more.

As far as the tax rate you want me to create another field on my "Orders" table, correct??

Once again thank you.

Joe