Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Textbox Query Result (2000 SR1)

    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?

  2. #2
    Star Lounger
    Join Date
    Nov 2001
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Query Result (2000 SR1)

    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

  3. #3
    Star Lounger
    Join Date
    May 2001
    Location
    Ventura, California, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Textbox Query Result (2000 SR1)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •