Access Invoice Help

[adamdistefano1]

Hello,

I am a total noob when it comes to access and VBA, i was wondering if you guys could help me with a few things.

Firstly, im creating an invoice application in access. I need some help with the following problem.
I have a 'Customer Database', and a 'Product database' named respectivly, i have developed both of these fine. What my plan is, is to have a sheet that has a selection for the data in the invoice required to be generated and for the user to enter the required data, such as which company its going to the products etc.

First issue...I have a combo box with the current Customers in the database, but what i would like to do is to have the address details and such automatically enter themselves in labels when i select the appropiate customer, like a vlookup function in excel.

Second, i would like an easy way to have a 'excel style spreadsheet' function to enter the products that are to be invoiced. I know of a Subform, Subreports control. I dont know how to use this very well. Any help with this would be greatly appreciated.

Thanks for any help you can give me, some links to tutorials with these would be great!

Regards, Adam

[MMcCarthy]

Hello,

I am a total noob when it comes to access and VBA, i was wondering if you guys could help me with a few things.

Firstly, im creating an invoice application in access. I need some help with the following problem.
I have a 'Customer Database', and a 'Product database' named respectivly, i have developed both of these fine.

Do you mean tables?

What my plan is, is to have a sheet that has a selection for the data in the invoice required to be generated and for the user to enter the required data, such as which company its going to the products etc.

First issue...I have a combo box with the current Customers in the database, but what i would like to do is to have the address details and such automatically enter themselves in labels when i select the appropiate customer, like a vlookup function in excel.

Is your invoice a report?

Second, i would like an easy way to have a 'excel style spreadsheet' function to enter the products that are to be invoiced. I know of a Subform, Subreports control. I dont know how to use this very well. Any help with this would be greatly appreciated.

Thanks for any help you can give me, some links to tutorials with these would be great!

Regards, Adam

Adam

We need a lot more information about what your are doing. If your invoice is a report what is the query which you are using as the record source of your report

How you add your products to an order will depend on how you need to keep those records.

e.g. A standard invoicing system would be as follows.

tblCustomer
custID (primary key)
custName
custAddress

tblProduct
prodID (primary key)
prodName

tblOrder
orderID (primary key)
custID (foreign key referencing tblCustomer)

tblOrderDetail
orderDetailID (primary key)
orderID (foreign key referencing tblOrder)
prodID (foreign key referencing tblProduct)
qty

tblInvoice
InvoiceNum (Primary key)
orderID (foreign key referencing tblOrder)

A typical query for an Invoice report would be.

SELECT InvoiceNum, tblOrder.OrderID, tblCustomer.custID, custName, custAddress
FROM ((tblInvoice INNER JOIN tblOrder
ON tblInvoice.OrderID = tblOrder.OrderID)
INNER JOIN tblCustomer
ON tblOrder.custID = tblCustomer.custID)
 

Then create a subReport based on the tblOrderDetail table. Join this to the main report based on orderID