FORMS

[JOE POLLOCK]

I am trying to create a sales order form that will allow multiple lines per
record. I currently have a subform that records that products, prices and
quantities, but each line creates a new record. When I print out the sales
order, a page prints out for each line because Access sees that as two
separate records. I need a way to allow multiple lines on the same record.
Any suggestions?

 

[George]

Hi Joe,

I beleive is a matter of the structure of your db (relationships actually).

You must have one table with the invoice (sale) details, e.g. invoice number
(long integer) which should be the primary key, invoicedate, customer etc.

In the second table - invoiceProducts you must have a primary key, or even
two (invoicenumber and productid - this will not allow the same product in
the same invoice), quantity, price etc.

You must link (create relationships) those tables using the invoice number
(One invoice with many products) and then to create a form using
tableInvoices and add in it as a subform the invoiceProducts.

Hope this helps,

GeorgeCY

Ο χÏήστης "JOE POLLOCK" έγγÏαψε:

 

[John W. Vinson]

I am trying to create a sales order form that will allow multiple lines per
record. I currently have a subform that records that products, prices and
quantities, but each line creates a new record. When I print out the sales
order, a page prints out for each line because Access sees that as two
separate records. I need a way to allow multiple lines on the same record.
Any suggestions?

Each line SHOULD create a new record. That's how relational databases work!

Don't confuse data *storage* - one Orders record per order, related
one-to-many to one OrderDetails record per item in that order - with data
*presentation*. You can easily create an Access Report using the Sorting and
Grouping dialog to create a sales order with the Orders information at the top
of the page (and/or the bottom of the page), with one line printed per
OrderDetails record.

See the Northwind sample database Orders form for an example, or check out
these tutorials and resources:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials