Access: Appending Related Tables

0
votes

I am working on a small side project. Below is an image of all my relations. I only have the tables set up so far, and a couple Queries that sorted my a couple of my tables a certain way to be used as lookup columns.

Table Relations

Is there a way to append new records to multiple tables at once?

I have experimented with this a few different ways. The thing that stumps me is when I create a form and corresponding sub forms (I can make sub form after sub form that has the entire string of things that could be input across multiple tables, which is not very aesthetically pleasing or easy to use), I have to enter data multiple times. Referring to appending the invoice item as well as the item list tables. I would have to enter the following;

  • Invoice Number (From invoice item)
  • Item Code (From invoice item)
  • Item Code (From item list)

So I end up having to type the item code multiple times, this occurs across the entire chain of tables I need to update in order to create an order.

Also in case you are wondering, all the tables seen in the diagram are update at time a new order is created. (This is a resale business so inventory is never held and item (in the Item List table) are added new at time of order.

So the Invoice table needs to be appended which starts the chain that I am trying to accomplish. Invoice number gets entered which I would like to automatically carry over to Invoice Item in wait for the Item Code to be entered to complete the composite key but without having to type the Invoice number again, and so on and so forth.

1
ms-access
you can create stored procedures for each operation and with executing this procedure you can update multiple tables with given set of parameters. - ctumturk
Do you think you could give me a pointer in the right direction, searching around for things like after insert have resulted in not much progress. - jBry562
Here is a nice document for procedure use. After CREATE PROCEDURE ProcedureName (Parameter1 datatype, Parameter2 datatype) AS you can use BEGIN and END clause. And between these you can use multiple insert or update command. Then you just need to execute procedure with the given parameters. - ctumturk
Have you set your link child and link master fields correctly? Are you aware that link fields can be set to the name of a control? - Fionnuala
I will look into both of those things. Like I said I am pretty new to data base. I understand it conceptually, and on a theoretical planning and ERD scope. But am pretty new to the actual implementation. Thank you - jBry562

1 Answers

0
votes

This is referred to as a Cascading Update and is essential to enforcing Referential Integrity.

What you want to do is:

  1. Click the relationship line for the relationship that you want to change. The relationship line appears thicker when it is selected.
  2. Double-click the relationship line. –or– On the Design tab, in the Tools group, click Edit Relationships.
  3. The Edit Relationships dialog box appears.
  4. Select the Enforce Referential Integrity check box.
  5. Select either the Cascade Update Related Fields or Cascade Delete Related Records check box, or select both.