Form add new record - help please

[Mike]

Hi,

I have a Transaction form, called frmTransaction bounded
to tblTransaction. Users use this form to enter records
of items moving out of our shop. At this time, One record
at a time must be entetred and then, by clicking on a
command button to add a new record.

I was owndering if I can set up the form so that user can
enter as many records as needed in at the same time and
at the end, create an invoice?

Such as:

Item #1
Item #2
..
..
..
Item#n

The form has the following feilds:

Part Number
Part Name
Quantity sold
Date
Cost

Upon entering a Part Number, the rest fills in
automatically except for the date and the quantity.

Thank you in advance for your help.

Regards,

Mike

 

[Steve Schapel]

Mike,

It sounds like the form is in Single Form view. This is governed by
the Default View property of the form. If you change this property
setting to Continuous, you will be able to enter multiple records.
However, if you want a set of records entered to be part of an
invioce, I expect you will need to make some modifications to your
table design... specifically you will need to have an invoice table,
and an InvoiceID (or equivalent) field in the Transaction table to
identify which invoice each transaction belongs to.

- Steve Schapel, Microsoft Access MVP

 

[Mike]

Steve,

You got it right. what you suggested is what I have been
trying to do, so please explain a little more of how this
Invoice table should be as far as its feilds are concern?

Should the InvoiceID, be added to the tblTransaction as
an additional field and if yes, is this InvoiceID is a
feild in the tblInvoice, a prime key or just a feild?

Thanks very much for you help.

Regards,

Mike

 

[Steve Schapel]

Mike,

The scenario you describe is a classic example of a one-to-many
relationship. Therefore, you need two tables, to represent the data
on both sides of this relationship. Your invoice table should have
fields for the data where there is only one piece of information for
each invoice, for example InvoiceDate, Customer, and an InvoiceID
primary key field which I suppose would be either an Autonumber or
reflect an invoice numbering system. And then the Transactions table,
for which there can obviously be more than one record for any given
invoice, will need to also have an InvoiceID field which can be used
to indicate which invoice the transaction belongs to. The standard
basic way of managing this data would be to make a form based on the
Invoice table, and another form, in continuous view, based on the
Transactions table, and this second form is placed on the Invoice form
as a subform.

Hopefully this will give an overview of the concept. If you are not
sure about any of these ideas, for example how to set up subforms, I
suggest you have a look in the Access Help, and/or get hold of a good
overview type book about Access. You may also find this article
useful:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;209534

- Steve Schapel, Microsoft Access MVP

 

[Mike]

Steve,

Thanks for the insight. I think I can manage from here. I
do have the book and will try to implement. I'll post
questions should I have problems.

Regards,

Mike