HowTo?:Entry in one table to refer to another and autoupdate other fields?

[Pam]

I'm using Access 2002 with Windows 2000 Professional, and
aside from one basic class in Access, I'm self-taught on
using it. I'm trying to do something that instinct tells
me should be possible (perhaps with a macro?), but I can't
get it to work.

We have several linked tables. One is a set
of "maintenance
activities", with info of "activity", "unit", and "cost
per
unit". Another table is "maintenance records".

When you select "fix gate" from the "maintenance activity"
listbox on the "maintenance records" table, we want
the "units" field there to automatically switch to "EA",
and the cost per unit to be filled in, based on the
corresponding value of "units" and "cost" in
the "maintenance activities" table. Then when
the "Amount" field is filled in, it should update the
estimated cost (amount x cost per unit). If you
selected "pour concrete" it should show "CY", and the
related info for pouring concrete should pop up to be
calculated.

I've tried setting up a macro but must be doing something
wrong. We've had a couple of people working on this and we
aren't getting anywhere. Any ideas? Thanks!

Pam

 

[Tom Wickerath]

Hi Pam,

Check out the Orders form in the Northwind sample database (Northwind.mdb). When you select a
product, the unit price, quantity and discount fields are populated with data, and the extended
price textbox shows the results of a calculation. I think if you reverse-engineer this sample,
you should have a good start on accomplishing your goal.

Good Luck,

Tom

PS.
You likely have this sample database installed by default on your hard drive. If not, it is
available on your Office installation media, or by downloading it from Microsoft.
_____________________________________


I'm using Access 2002 with Windows 2000 Professional, and
aside from one basic class in Access, I'm self-taught on
using it. I'm trying to do something that instinct tells
me should be possible (perhaps with a macro?), but I can't
get it to work.

We have several linked tables. One is a set
of "maintenance
activities", with info of "activity", "unit", and "cost
per
unit". Another table is "maintenance records".

When you select "fix gate" from the "maintenance activity"
listbox on the "maintenance records" table, we want
the "units" field there to automatically switch to "EA",
and the cost per unit to be filled in, based on the
corresponding value of "units" and "cost" in
the "maintenance activities" table. Then when
the "Amount" field is filled in, it should update the
estimated cost (amount x cost per unit). If you
selected "pour concrete" it should show "CY", and the
related info for pouring concrete should pop up to be
calculated.

I've tried setting up a macro but must be doing something
wrong. We've had a couple of people working on this and we
aren't getting anywhere. Any ideas? Thanks!

Pam