How to setup a query that when a item from a combobox is selected it

[ArtistRhetta]

How can I setup a query that when a item from a combobox is selected it will automatically throw a unitprice that is corresponding to that item in the unitprice field? Would it be a SELECT statement of some sort and where would it go? I have been reading a lot of books but they are scarce when it comes to creating expressions or finding a similar that will give me the answer. Another question is it possible to have a combobox in a query to popup when you run the query to pick an item instead of a textbox poping up to enter the item as the criteria that you want to query. The items are kind of lengthy, they can copy and paste but that takes more time and some even have trouble trying to do that. Someone wanted to know if I could change it to a combobox. For example, when I click on the macro, it opens the query with a popup that ask then to enter the product name, they want to be able to select from a combobox. Any ideas would be appreciated. I am really new at Access and would appreciate the help.

 

[l3reacher]

I'm not sure if this is what you mean, here is the thread that might have the answer you're looking for.

thread701-832485

I Hate Spammers and Flammers!!!

 

[ArtistRhetta]

l3reacher,

Thanks I will give it a try. This is a Lookup Table will that make a difference. I will need to create a form that has a combobox, when I select an item from the combobox it acts like a filter is that correct? Does the statement go in the sort field?

 

[l3reacher]

You will need to create a form that will have a combo box with a button that will open up the query. The combo box is will filter the the things you will need to look up. You don't put the statement in the sort field, but you put it in the criteria field.

I Hate Spammers and Flammers!!!

 

[scriverb]

This is how you would accomplish this. The combobox has a RowSource which identifies the items to be selected. These items come from a table usually in your example(i.e. Items ). The Items table would have a field called Item_Name and a field called Item_Price. The following SQL can then be used as the RowSource for the ComboBox.

Select A.Item_Name, A.Unit_Price FROM Items as A ORDER BY A.Item_Name;

The combobox should have its Bound Column set to 1 and have its columns widths property set to 2;0 .

Now in the AfterUpdate event procedure of the ComboBox put the following VBA code:

Me![UnitPrice] = Me![[i]comboboxname[/i]].Column(1)

You see there are two columns in the combobox even through we are only displaying the Item_Name column. The second column, which is referenced as .column(1), is then assigned to the forms control Me![UnitPrice]. Enumeration of the columns begin with 0 rather than 1.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]