Lookup product price

[Mik]

I have a table in MS Access which has two columns, one column
containing a product name, and the other contains the product price.
I have also created a form, and have a product cell set as a combo box
(amongst others) for easy selection.
When the user selects a particular product type from the combo box, i
would like the product price to automatically display in the adjacent
cell.

Can anybody please advise?
I am a complete beginner, so be gentle with me.

Thanks Mik

 

[Brian]

It depends on the purpose of the form. If the form is the form through which
you enter products & prices for use elsewhere, then you want just a bound
text box for each (product & price) on a form bound to the table.

However, I suspect that you are at the next stage, where you may want to
select the product on a sales form. What you need to do is look up the price
matching the product from the table.

In the After_Update event of the product combo box, put this:

Product_AfterUpdate
If IsNull(Product) Then
Price = Null 'clear the price if the product is blank
Else
Price = DLookup("[Price]","[ProductPriceTable]","[Product] = '" &
[Product] & "'")
End Sub

The quotes & apostrophes can be a little tricky at first. In Visual Basic,
you look up the help for DLookup to get some more clarification.

 

[Mik]

It depends on the purpose of the form. If the form is the form through which
you enter products & prices for use elsewhere, then you want just a bound
text box for each (product & price) on a form bound to the table.

However, I suspect that you are at the next stage, where you may want to
select the product on a sales form. What you need to do is look up the price
matching the product from the table.

In the After_Update event of the product combo box, put this:

Product_AfterUpdate
If IsNull(Product) Then
  Price = Null 'clear the price if the product is blank
Else
  Price = DLookup("[Price]","[ProductPriceTable]","[Product] = '" &
[Product] & "'")
End Sub

The quotes & apostrophes can be a little tricky at first. In Visual Basic,
you look up the help for DLookup to get some more clarification.

I have a table in MS Access which has two columns, one column
containing a product name, and the other contains the product price.
I have also created a form, and have a product cell set as a combo box
(amongst others) for easy selection.
When the user selects a particular product type from the combo box, i
would like the product price to automatically display in the adjacent
cell.

Can anybody please advise?
I am a complete beginner, so be gentle with me.

Thanks Mik- Hide quoted text -

- Show quoted text -

Brian,

Thanks for your assistance.
It worked great.

Mik

 

[Brian]

You are very welcome. I spent a lot of time looking for answers in the Help
files, not even knowing what to search for, until I found these forums.

By the way, don't forget to disable and/or lock the control containing the
product price if you do not want the user to be able to override the price.
The code I gave you looks up the price but does not prevent the user from
typing another value in there.

It depends on the purpose of the form. If the form is the form through which
you enter products & prices for use elsewhere, then you want just a bound
text box for each (product & price) on a form bound to the table.

However, I suspect that you are at the next stage, where you may want to
select the product on a sales form. What you need to do is look up the price
matching the product from the table.

In the After_Update event of the product combo box, put this:

Product_AfterUpdate
If IsNull(Product) Then
Price = Null 'clear the price if the product is blank
Else
Price = DLookup("[Price]","[ProductPriceTable]","[Product] = '" &
[Product] & "'")
End Sub

The quotes & apostrophes can be a little tricky at first. In Visual Basic,
you look up the help for DLookup to get some more clarification.

I have a table in MS Access which has two columns, one column
containing a product name, and the other contains the product price.
I have also created a form, and have a product cell set as a combo box
(amongst others) for easy selection.
When the user selects a particular product type from the combo box, i
would like the product price to automatically display in the adjacent
cell.

Can anybody please advise?
I am a complete beginner, so be gentle with me.

Thanks Mik- Hide quoted text -

- Show quoted text -

Brian,

Thanks for your assistance.
It worked great.

Mik