Updating multiple fields in more than one recordset

[Guest]

Initial data is entered in a Shipments form, reality here is that any given
po number can ship in multiple contianers. So far so good.

Now as these various containers are sailing towards the USA which are
critical to the business and need to be tracked by logisitics (and here is
where it gets tricky for me). I created the Tracking form for those folks to
use and have a combo box for them to use to select a container from. (NOTE:
the combo box displays individual container numbers by using SELECT
DISTINCTROW, the individual recordsets may contain the same container number
many times because one of our purchase orders can be shipped in several
containers over time).

Once they select the container they are tracking they will update the
pertinent fields and save that info. I would like for them to click the
update records button and have the current info in that form pass to all
record sets containing the specific container number they have just
researched.

I believe this is do-able but requires programming beyond my knowledge at
this time.
Can you are anyone please help me through this?

 

[Jeff Boyce]

Steve

I'm not entirely clear on your underlying data model, but it sounds like you
have data associated with purchase orders that belongs to the container.

If you are identifying a container (there may be many, but you only want to
see each once), then updating information that is related to that container,
then why would you need to "distribute" to the multiple instances? I mean,
I understand if that is your current data structure, but if I am
understanding what you have, you could further normalize your data and
simplify your task.

Here's a wild stab, based on incomplete understanding. Feel free to set me
straight:

You have items
You have purchase orders
You have containers

A purchase order can have 1:m items
A container can hold (0) 1:m items
A specific item associated with a specific purchase order will be in one

and only one container (at one point in time).

Over time, an item may "move" from container to container

If all this is reasonably correct, a well-normalized design (ok, so MY idea
of well-normalized <g>) would be:

tblItem
ItemID
ItemName
ItemPrice
...

tblPurchaseOrder
PurchaseOrderID
CustomerID (we haven't talked about this table)
PurchaseDate
... (?shipping instructions?)

trelPurchaseDetail (NOTE: one row per item purchased per purchase order)
PurchaseDetailID
PurchaseOrderID (foreign key from tblPurchaseOrder)
ItemID (foreign key from tblItem)
Quantity
... (?salespersonID?, ...)

tblContainer
ContainerID
???what facts about a container do you need?

trelContainerContents
ContainerContentsID
ContainerID (foreign key...)
PurchaseDetailID (foreign key ...)
StartDate (when did this purchase detail item first "appear" in this
container?)
EndDate (when did ... last ...?)

I'm sure I'm leaving out details you are much more familiar with -- please
clarify.