trying to pull 2 fields from another table into this table

[E Taylor]

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.

 

[J. Goddard]

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

 

[Guest]

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

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.

.

 

[John Vinson]

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.