Entering data in one form while updates 2 or more tables

[Guest]

Hi, there

I constructed a form! [Purchase] based on a query. The form is based on one
query, which was built from two queries and one table. In this form, by
entering data and saving record, I am able to update the form's underlying
tables and queries upon which the form was built.

However, there're a few fields entered that I'd like to also undate another
table, e.g. [Stock], which is not the underlying table of this form. I am
wondering how I am able to do that.

I suppose maybe some code building or macro is needed to make this
possible? I am totally stuck here, any help or guidance is highly
appreciated.

Many thanks.
qvqv

 

[Steve]

I would guess that your tables are not set up correctly and this is causing
you a problem. Tables for purchasing typically look like:

TblPurchase
PurchaseID
PONum
PurchaseDate
VendorID
ExpectedDeliveryDate
ActualDeliveryDate

TblPurchaseDetail
PurchaseDetailID
PurchaseID
ItemID
Quantity
PriceEach

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[StopThisAdvertising]

I would guess that your tables are not set up correctly and this is causing
you a problem. Tables for purchasing typically look like:

TblPurchase
PurchaseID
PONum
PurchaseDate
VendorID
ExpectedDeliveryDate
ActualDeliveryDate

TblPurchaseDetail
PurchaseDetailID
PurchaseID
ItemID
Quantity
PriceEach

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

--
Steve, you have one of two choices:
-- Tell us you will stop advertising here, or...
-- get lost for another year or so...
http://home.tiscali.nl/arracom/whoissteve.html
(Until now 2495 pageloads, 1837 first-time visitors)

FYI: (this is also to inform those who do not understand...)
This is *not* about the sigline...although we do not 'love' it, we don't mind the current sigline.
But we will simply continue to hunt down *each and every* of your posts.

It is not relevant whether you advertised in *this* particular post or not...
Your pattern is: You post a few 'good' answers and then start to advertise again.

These groups are *not* your private hunting grounds!
You should know this by now.

ArnoR

 

[Guest]

I think probably I didn't make myself clear about my question. In fact, it
may not be the problem of table, it's just that I don't want to enter data
twice to 2 different tables for the same information, I hope that while
entering date in one form, I will be able to update 2 different tables.

The problem is the form was made from one table and query while the other
table is unrelated. Pls refer to my original question for detail. Please,
anyone out there please give me some clue. Many many thanks!

 

[John W. Vinson]

Hi, there

I constructed a form! [Purchase] based on a query. The form is based on one
query, which was built from two queries and one table. In this form, by
entering data and saving record, I am able to update the form's underlying
tables and queries upon which the form was built.

However, there're a few fields entered that I'd like to also undate another
table, e.g. [Stock], which is not the underlying table of this form. I am
wondering how I am able to do that.

I suppose maybe some code building or macro is needed to make this
possible? I am totally stuck here, any help or guidance is highly
appreciated.

Many thanks.
qvqv

Well, you can certainly use VBA code in the form's AfterUpdate event to open a
Recordset based on the other table and update it. You have not posted anywhere
near enough information for anyone to write sample code, of course!

HOWEVER... the assertion in your other message that you would otherwise be
entering the same data twice raises some big red flags. The whole POINT of a
relational database architecture is that you should store data once, and once
only; the additional point that the other table is unrelated to this one
strongly suggests a problem with the design!

What are these tables?
What real-life entities (things, persons, events) do they represent?
What needs to be updated in [Stock], and why?

John W. Vinson [MVP]