Want to print an invoice..

H

Hank Stalica

MY SKILL LEVEL:
Novice.

WHAT I HAVE:
I have a database with two tables: one has items for sale and the other
database has customer info. Two forms: one for entering data for the
sales items and one to enter data for the customer info.

WHAT I WANT TO DO:
Be able to run a report that combines info from both tables onto one report.

THOUGHTS:
I'm thinking what I have to do is run a query that pulls the info from
both tables and then base the report off that query. Then, I call the
report from the Preview Report button from the sales form.

I'm thinking my stumbling block is that I'm not doing the query right.
I was using the DLookup function to grab the info from the customer
table, but it placed the same customer table record on the report for
every sales item I previewed.

WHAT HAPPENS NOW:
When I click on the Preview Report button, it runs my query, but no
matter which Sales item record I'm on, I get the same customer.

The sales Item info shows up correctly on the report.

Can someone please point me in the right direction?

Thanks,

--Hank
 
A

Allen Browne

You have two tables:
- a Customer table, with CustomerID primary key (autonumber).
- a Sale table, with SaleID primary key (autonumber) and also a CustomerID
foreign key (i.e. it is a Number field, and matches a value in
Customer.CustomerID).

If you have not yet done so, create a relation between the two tables:
1. Choose Relationships on the Tools menu.
2. Add both tables to the relationships view.
3. Drag Sale.CustomerID onto Customer.CustomerID. Access opens a dialog.
4. In the dialog, check the box for Referential Integerity. Ok. Close.

Now create a query that contains both tables. In the upper pane of query
design, you will see a line joining the 2 tables. Drag the fields you want
from both tables into the grid. Save the query.

Now create a report based on the query, and design it to look like an
invoice: address panel at the top (fields from the Customer table), etc.

If you want to to put a command button on your sales form to print the
invoice for just one SaleID, see:
Print the record in the form
at:
http://members.iinet.net.au/~allenbrowne/casu-15.html

BTW, an invoice may need several line items. This means you need another
table related to the Sale table, containing the sale items. For an example
of how to do that, open the Northwind sample database, and look at their
Orders and Order Details tables. Tools | Relationships gives the overview.
 
Top