DLookup???

[kingsgambit]

I am trying to use Dlookup.
In my text box I have put =DLookUp("Price","Prices","[Product]=[forms]![Instruct].[Product]").
I put in After update in my combobox Requery "Price" and this works, but this form is a subform on my main form when I open my main form I get errors in the text box and an error saying you can not use this filter.
Where am I going wrong

 

[Jack Cowley]

You lost me. What does the combo box have to do with the DLookup(). Where is the DLookup() code (form or subform)? Where is the control with the Product value? Is the Product value Text or a Number?

Jack

 

[kingsgambit]

Sorry about this I will try to explain
The combo box contains a list a products, when the user selects one the text box should put the price of the product in it.
The Lookup code is on the subform in the rowsource of the text box "Price".

 

[Jack Cowley]

In the source for your combo box add the price field as well. For example the data might be ProductID, ProductName, Price. If you have that then in the After Update event of your combo box you can use code like this:

Me.NameOfTextBoxOnForm = Me.ComboBoxName.Column(2)

In the case above the price from the 3rd column (combo boxes are zero based) in the combo box will be put into the text box on the form. This is faster than using DLookup().

hth,
Jack

 

[kingsgambit]

Thanks for that solution.
I need to produce a report, would I have to use Dlookup, or could I use this in the query the report will be produced from

 

[Jack Cowley]

From your question I assume that Price is not a field in the Products table. Therefore it must be in a related table so just base the query on the two tables and add the price field to the query.

Have I understood what it is you are trying to do?

Jack

 

[kingsgambit]

Yes, thanks for that.
I have another question if you don't mind
Now I have my sub form working I want to put on the main form a text box that shows the total for the products sold to the client.
I have done this with a query attached to a list box, but can not get the subform to refresh the main form. i.e if the client buys another item the main form need to refreshed so the textbox with show the correct amount.
If you are wondering why a listbox this is because I could not find away to attached a query to a textbox

 

[Jack Cowley]

I don't mind... Take a look at this article as I think it will answer your question... If not, let us know....

Jack

 

[kingsgambit]

Thanks for your help
I worked it out before I had chance to let you know