Data from one table and write it to another table

[Loggical via AccessMonster.com]

How do I take the Date from one table and use it in another table.

I have a form frmPurchaseOrders that has the following table fields and a
subForm subProductsInventory.

POID
CompanyID
PODate

The subForm subProductsInventory has the following fields.

POID
InventoryDate
ModelID
UnitPrice
UnitsOrdered
UnitTotal

What I want to do is obtain the value of the PODate in the frmPurchaseOrders
and place it into the InventoryDate of the subProductsInventory. I can do
this in the form but can not get it to write to the underlying table
tblProductsPurchased.

The subForm subProductsInventory underlying table is tblProductsPurchased.

So when I open up the Form frmProductsInventory the subForm
subProductsInventory shows the date the item was purchased.


Thanks in advance.

 

[Al Campagna]

Logical,
If every subform record's InventoryDate ALWAYS has the same date as the
main PODate, then there's no need for that subform table field at all. The
key
POID relationship between the Main table and the Sub table can associate
that
PODate with it's related subform records... through any subsequent query for
another form, or report.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Tom van Stiphout]

On Mon, 16 Nov 2009 07:12:22 GMT, "Loggical via AccessMonster.com"

You seem to have some database design problems.
If there is a hard rule that says that tblPP.InventoryDate is always
equal to tblPO.PODate, then the field should be removed altogether,
per relational database design rules.
Same goes for UnitTotal. Since it is always Price*Units, it does not
belong in a relational db. If you ever need this value, you can
calculate it in an expression or a query.
Also it appears you are mixing two concerns in one table: tblPP has
both line items for a PO as well as inventory information. More than
likely those two should be in separate tables.

-Tom.
Microsoft Access MVP

 

[Loggical via AccessMonster.com]

The UnitTotal is a calculation only, its not written to the table.
Yes the PODate will never change, but the InventoryDate will always change as
items are delivered, Used and Disposed.

I need to re-think the way i have explained my original quetsion.

You seem to have some database design problems.
If there is a hard rule that says that tblPP.InventoryDate is always
equal to tblPO.PODate, then the field should be removed altogether,
per relational database design rules.
Same goes for UnitTotal. Since it is always Price*Units, it does not
belong in a relational db. If you ever need this value, you can
calculate it in an expression or a query.
Also it appears you are mixing two concerns in one table: tblPP has
both line items for a PO as well as inventory information. More than
likely those two should be in separate tables.

-Tom.
Microsoft Access MVP

How do I take the Date from one table and use it in another table.

[quoted text clipped - 25 lines]

Thanks in advance.