Textbox Query Result (2000 SR1)

Topic

New Reply

I will base my problem in terms of the Northwind database. In the [Orders] form, I want to add a new textbox that displays the [UnitPrice] from the [Order Details] table – for the specified current [Order ID] and a specified [ProductName].

I have made a query that will return the correct [UnitPrice], but when I set that query as the Control Source for the textbox, and then view the form, the textbox displays “#Name?”. If I set the Control Source to the SQL statement it does the same.
Here’s the query:
SELECT [Order Details].UnitPrice, [Order Details].ProductID, [Order Details].OrderID, Products.ProductName FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID WHERE ((([Order Details].OrderID)=[Forms]![Orders]![OrderID]) AND ((Products.ProductName)=”Tofu”));

How can I achieve this sub-table look-up from the main form?

Reply | Quote

Replies

Try this as your Control Source for your Unbound Field. I have used the Northwind analogy here too…

=DLookUp(“[UnitPrice]”,”Order Details”,”[OrderID] = ” & [Forms]![Orders]![OrderID] & ” And [ProductID] = ” & [Forms]![Orders]![Orders Subform].[Form]![ProductID])

Be sure and set the controls Format to Currency.

hth,
Jack

Reply | Quote

Very good! That seems to work (at least in NorthWind). I’ll try it in my real application, now.

Thanks for your help! I can always count on the great people at Woody’s Lounge!

Reply | Quote