A form and subform that works like an Invoice

R

Rtash

I need to figure out how to make a subform with several records, retain
information I input or change with in it and bind it to one record in the
form where the subform resides. Some thing like an invoice subform without
the sum and total needs. Instead of an invoice by customer, I would want a
print order by date or order #.
 
J

John Vinson

I need to figure out how to make a subform with several records, retain
information I input or change with in it and bind it to one record in the
form where the subform resides. Some thing like an invoice subform without
the sum and total needs. Instead of an invoice by customer, I would want a
print order by date or order #.
Click to expand...

If you're assuming that a Form or a Subform can contain or store data,
you're mistaken. Data is not stored in Forms, but only in Tables.

You will need an Order table related one-to-many to an OrderDetails
table; the subform would be based on the latter.

Your Form need not have any sums or totals unless you choose to put
them. You can base a Report on a query joining the Orders and
OrderDetails tables, grouping any way you choose.

See the Northwind sample database Orders form for an (imperfect)
example.

John W. Vinson[MVP]
 
R

Rtash

Thanks John,
Sorry For the vague post. What I have are three tables Products, Cardstock,
Envelopes they all are very detailed. I would like to take the Product code,
Product Name from the products table and combine it with the Envelope Name,
and Cardstock Name from the other two tables and be able to select them in
the subform with the end result of an order for example quantity 1 CC021A
“Product Name†White card white envelope. Now where I probably went wrong was
the relationships. I am able to achieve the above result but the second I
advance to the next record on the main form I loose all the order details I
selected in the subform. Returning to the previous record does not restore
these choices. I am assuming this is because it is not being recorded in any
of the tables. Can I store the selections made in the above subform in yet
another table?
 
J

John Vinson

Thanks John,
Sorry For the vague post. What I have are three tables Products, Cardstock,
Envelopes they all are very detailed. I would like to take the Product code,
Product Name from the products table and combine it with the Envelope Name,
and Cardstock Name from the other two tables and be able to select them in
the subform with the end result of an order for example quantity 1 CC021A
“Product Name” White card white envelope. Now where I probably went wrong was
the relationships. I am able to achieve the above result but the second I
advance to the next record on the main form I loose all the order details I
selected in the subform. Returning to the previous record does not restore
these choices. I am assuming this is because it is not being recorded in any
of the tables. Can I store the selections made in the above subform in yet
another table?
Click to expand...

Bear in mind, I know NOTHING about the structure of your database. I
was assuming (incorrectly, it turns out) based on your original post;
I did not realize that the subform was bound, so I was suggesting that
it should be. My assumption was wrong.

Without knowing just what you're doing - again - all I can suggest is
that you should almost certainly NOT copy data fields like the product
name, envelope name, and cardstock name into your other tables. That
would be redundant.

If you could please post the Recordsource query of your form and
subforms, and indicate where the product, envelope, and cardstock
names are stored; and what you mean by "select them in the subform" we
might be able to help. It seems that you perhaps have some unbound
combo boxes on the subform (so that you're "losing" the order details
because you've never actually stored them in the first place).

John W. Vinson[MVP]
 
Top