combo box to return a valve in a different field

[losinghair]

HELP PLEASE

I need to create a combo box to (which has item descriptions in it)show the item retail price of that particular item description selected in the combo box.

for example I need to create two fields on a form. one a combo box with a list of item descriptions that can be selected. and the other a item retail price that once an item description is selected from the combo box
it returns a value. the item description and item retail price values are on the same table.

I am trying to use the create new wizard form but I am having no luck


Cheers.

 

[shacket]

Use the DLookUp function in the AfterUpdate event of the combo box.

I encourage you to read through other posts also. This question is one that has been answered a lot lately. You can often find what you are looking for in other people's posts or by doing a search.

I hope that helps.

 

[DJBummy]

Create a form using the item table as your record source.
Use the combo box wizard.
1. Look up values in a table or query
2. Select your Items table
3. Highlight the Item Description field and click the > to move it to the selected fields
4. Click next until you get to Remember this value for later use and make sure its selected.
5. Click next and Finish

In the properties of your combo box give it a name such as ItemSelect

In the OnClick event for the combo box enter this into the code

DoCmd.Runcommand acCmdRefresh

Place a text box on your form and enter this into the control source.

DLookUp("[The Name Of The Table]","[Item Price]","[Item Description]=Form![ItemSelect]")

I believe thats it.

Hope this helps you out.

D.J.

 

[Rich]

Why not use a query that joins the product to it's retail price. Base the combo on the query and you will then have both items in your combo without having to use DLookUp.

 

[shacket]

That's right, Rich! I gave that answer last week and forgot it this week!

The two items are in the same table so simply have both columns load into the combo box, show one and then refer to the other combo box column in the text box. In other words, your text box ControlSource would look like this:

Me.ComboBoxName.Column(1)

(columns are zero based, so the first column is column 0, the second column 1 and so on)

 

[DJBummy]

Thats a better way. I stand corrected.

Once again another learning experience on this informative sight.

[This message has been edited by DJBummy (edited 08-27-2001).]

 

[Pat Hartman]

You also need to store the retail price. This is one of those pieces of data that needs to be stored redundantly because it changes over time. So if you order today at $1.98 and the price changes to $2.10 tomorrow, you still want the $1.98 price to show in the order even though the new price associated with the product is different. So, in the AfterUpdate event of the combobox -
Me.RetailPrice = Me.YourCombo.Column(?)
with the ? being replaced by whatever column number refers to the price field. Remember that comboboxes are 0 based arrays so the first column is referred to as .Column(0), second column is referred to as .Column(1), etc.

 

[losinghair]

guys I still could not get it to work.
please explain in much simpler terms