automatically update other fields based on input in another

[Guest]

Hi,

I am currently designing a database table for our order intake. I hjave a
field called ProductID, what I'm trying to do is after I select the correct
ProductID from the drop down menu, I then want the ProductName & ProductPrice
to automatically update. Being a ' newbie ' I have no experience of access.

thanks.

 

[Guest]

hi,
you could use the dlookup method

Me!txtProductName = DLookup
("[ProductName]", "orders", "[ProductID] ='" & Me!
txtProductID & "'")

this wrapped. should be one line
bacicly says - loopup the product name in table orders
where the product id is the same as the product id i type
in the product id combo box and put it in the product name
box.
use a dlookup for each data bit you want to know
put it in the combo box's before update envent
good luck

 

[Guest]

Hi,

Thanks for the help, although the terminology has blown me away, also where
do I input that formula. To give you a better idea: the table ' Orders ' is
the overall table where I can access everything, I have a second table called
' Products ' . this consits of 3 columns, ProductID, ProductName &
ProductPrice.

hi,
you could use the dlookup method

Me!txtProductName = DLookup
("[ProductName]", "orders", "[ProductID] ='" & Me!
txtProductID & "'")

this wrapped. should be one line
bacicly says - loopup the product name in table orders
where the product id is the same as the product id i type
in the product id combo box and put it in the product name
box.
use a dlookup for each data bit you want to know
put it in the combo box's before update envent
good luck

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

I am currently designing a database table for our order intake. I hjave a
field called ProductID, what I'm trying to do is after I select the correct
ProductID from the drop down menu, I then want the ProductName & ProductPrice
to automatically update. Being a ' newbie ' I have no experience of access.

thanks.
.

 

[John Vinson]

Hi,

I am currently designing a database table for our order intake. I hjave a
field called ProductID, what I'm trying to do is after I select the correct
ProductID from the drop down menu, I then want the ProductName & ProductPrice
to automatically update. Being a ' newbie ' I have no experience of access.

thanks.

Well, you're making a very typical "newbie" mistake.

Your Orders table should have the ProductID in it. You should
certainly NOT copy the ProductName field into the Orders table!

As a relational database, Access follows the "Grandmother's Pantry
Principle": "a place - ONE place! - for everything, everything in its
place". If you need the product name associated with an order, you
would use a *query* to link to the Products table to pick it up.
Storing the name in the order table is redundant and unnecessary.

The price is a somewhat different issue. Since prices can change, you
really do want to capture the price *as it existed at the time of the
order*. To do so you need to use a little bit of VBA code; in the
AfterUpdate event of the product combo box, you can "push" the product
price into a form control for the order price field:

Private Sub cboProductID_AfterUpdate()
Me!txtProductPrice = cboProductID.Column(2)
End Sub

where txtProductPrice is the name of the textbox bound to the order
price field, cboProductID the name of the Product dropdown box, and
the (2) means the *third* column in the combo box: it's zero based.

Have you looked at the Northwind sample database Orders form? It's not
perfect but it demonstrates quite a few worthwhile features.

John W. Vinson[MVP]