Updating "one-to-many" related tables from Form

[Guest]

I have two tables: (1) Items (2) Invoice. These are related in One-to-Many
relationship with Cascading Update option selected. I want to create a Form
where user input InvoiceNo, InvoiceDate and pick Items from a drop down box.
This way 5-7 Items will become part of one Invoice. Preferably, once an Item
is selected under an Invoice, it should not appear again in the drop down
list for subsequent Invoice.

The structure of my tables is:

ItemID
InvoiceID ---------> InvoiceID
InvoiceNo ItemAmount
InvoiceAmount

InvoiceAmount field on the Form will be updated by sum of ItemAmount when
records from Item table are selected from drop down box.

Can someone please guide me how to proceed. I have only limited knowledge of
Access. I do not know Visual Basic and Store Procedure but I use Macro
Builder. Thanks in advance.

Naresh Kumar Saini

 

[Roger Carlson]

The easiest way to implement an one-to-many relationship in Access is to use
a Form/Subform setup. The table (or query) on the "one" side goes into the
main form and the table on the "many" side goes into the subform. At its
simplest, the form/subform requires little or no coding.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "FormSubform.mdb" which illustrates this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

I tried using the wizard to create a Form and Subform, but there are
limitations:

The user open the Invoice (parent) Form and input InvoiceNo for Record 1.
After that, on the Subform he select a record from Items from the drop down.
He thus select 4-5 records. Then he move to the next record (Record 2) in the
Invoice (main) Form. Here, all the items of Items are still available for
selection under new Invoice record. Thus the user is likely to make a
mistake. For example, if the user choose Record 1,2,3 of Items under Record 1
of Invoice, the three records should not be available for selection under
Record 2 of Invoice. In the instant situation, the user can still
(mistakenly) select any of these three records under Record 2 of Invoice as
well.

I guess my present object can be achieved on the following lines. Perhaps
with some help and guidance I can do it:

(1) User inputs open the Invoice Form and input details for Record 1.
(2) Then he click on a button called "Choose an Item".
(3) A macro associated with above button make the current Form invisible and
open another form "ChooseItems".
(4) The record source of ChooseItems restrict data to where InvoiceID field
is blank or Null.
(5) User select one (or perhaps more) records in this "ChooseItems" Form.
(6) The user click on "OK" button on "ChooseItems" Form.
(7) The macro associated with "OK" button set the value of InvoiceID field
of the selected record(s) (in Items table) equal to InvoiceID field on the
parent Form (i.e. Invoice).
(8) Then the macro further close the ChooseItems and make the parent Form
visible.
(9) Next, the Requery (???) command display the currently linked record(s)
from Items for the current record of Invoice. The field property of controls
do not let the user change the content of Items under this (parent) Form.

Things looks a bit complicated to me as I am a new user. Please suggest
whether the above is possible and/or how to proceed further.

Thanks for help.

Regards

Naresh Kumar Saini