trying to pull 2 fields from another table into this table

I have a database which has multiple tables.

There is a table for products which has various columns
including ProductID, CategoryID, Product_Name and
Product_Description.

There is a table called Inventry Transactions which has
the fields TransactionID, TransactionDate, Product_Name
(from the other table) and a few other coloumns.

I have a form which links to an inventry transactions
table, so when a product leaves our 'store' we record
what is going out etc. On the form, you pull up the
Product_Name field and select the correct item. Some of
the items are very similar and it would be really useful
if the Product_Description field (from the products table)
could be shown on the form (and if necessary the inventry
transactions table), so that when an item is selected,
you see the description. But whilst I know how to add
fields that are tied to the inventry transactions table,
I can't seem to add fields for other tables, or pull this
enty over from the product table to the inventry
transaction table in a manner where the data entered also
shows.

Hi -

Try using the DLookup function. On your form, add an unbound text
control for the product description. Then you can use the DLookup
function to (re)populate that text box whenever the value in the
Product_Name field changes. You will need a little VBA code attached to
the "After Update" event of the Product_Name field, something like this:

searchstring = "[ProductName] = '" & me![Product_Name] & "'"
Me![ProductDescriptionBox]].Value = DLookup("[ProductDescription]",
"ProductTable", searchstring)

I know, the syntax is tricky!
I use this technique myself and find it very useful.

HTH
John

E Taylor wrote:

I have a database which has multiple tables.

There is a table for products which has various columns
including ProductID, CategoryID, Product_Name and
Product_Description.

There is a table called Inventry Transactions which has
the fields TransactionID, TransactionDate, Product_Name
(from the other table) and a few other coloumns.

I have a form which links to an inventry transactions
table, so when a product leaves our 'store' we record
what is going out etc. On the form, you pull up the
Product_Name field and select the correct item. Some of
the items are very similar and it would be really useful
if the Product_Description field (from the products table)
could be shown on the form (and if necessary the inventry
transactions table), so that when an item is selected,
you see the description. But whilst I know how to add
fields that are tied to the inventry transactions table,
I can't seem to add fields for other tables, or pull this
enty over from the product table to the inventry
transaction table in a manner where the data entered also
shows.

Thanks, I have tried this but can't seem to get it to
work. visual basic is nt showing the phrases as red, so
I think I have the terminology correct, but the field
does not update at all, it remains empty (no errors etc)
as though it is not connected to anything.
Any ideas please.

I forgot to mention I'm in access 2000

-----Original Message-----
Hi -

Try using the DLookup function. On your form, add an
unbound text
control for the product description. Then you can use
the DLookup
function to (re)populate that text box whenever the
value in the
Product_Name field changes. You will need a little VBA
code attached to
the "After Update" event of the Product_Name field,
something like this:

searchstring = "[ProductName] = '" & me!
[Product_Name] & "'"
Me![ProductDescriptionBox]].Value = DLookup
("[ProductDescription]",
"ProductTable", searchstring)

I know, the syntax is tricky!
I use this technique myself and find it very useful.

HTH
John

E Taylor wrote:

I have a database which has multiple tables.

There is a table for products which has various
columns
including ProductID, CategoryID, Product_Name and
Product_Description.

There is a table called Inventry Transactions which
has
the fields TransactionID, TransactionDate,
Product_Name
(from the other table) and a few other coloumns.

I have a form which links to an inventry transactions
table, so when a product leaves our 'store' we record
what is going out etc. On the form, you pull up the
Product_Name field and select the correct item. Some
of
the items are very similar and it would be really
useful
if the Product_Description field (from the products
table)
could be shown on the form (and if necessary the
inventry
transactions table), so that when an item is selected,
you see the description. But whilst I know how to add
fields that are tied to the inventry transactions
table,
I can't seem to add fields for other tables, or pull
this
enty over from the product table to the inventry
transaction table in a manner where the data entered
also
shows.

.

On Wed, 19 May 2004 02:35:18 -0700,
wrote:

Thanks, I have tried this but can't seem to get it to
work. visual basic is nt showing the phrases as red, so
I think I have the terminology correct, but the field
does not update at all, it remains empty (no errors etc)
as though it is not connected to anything.
Any ideas please.

Please post your actual code.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public