Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    150

    Unanswered: Running a Query based on a textbox value

    Hi,

    I have an extended price qry that calculates the unitprice times the quantity, minus the discount.

    SELECT tblOrderDetails.intOrderID, tblProductInfo.intProductID, tblProductInfo.strProductName, tblProductInfo.curUnitRetail, tblOrderDetails.intQuantity, tblOrderDetails.intDiscount, CCur(tblProductInfo.curUnitRetail*[intQuantity]*(100-[intDiscount])/100) AS curExtendedPrice
    FROM tblProductInfo, tblOrderDetails
    ORDER BY tblOrderDetails.OrderID;


    This results of this query are shown in a form ( Orders ). The problem is that when loading the form, i want the query to run and only display the results for the OrderID in the main form. I'm not quite sure how to do this.
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  2. #2
    Join Date
    May 2002
    Location
    Chicago
    Posts
    22

    Re: Running a Query based on a textbox value

    If I understood this right, you want to filter your records based on the selected OrderID?

    The easiest is to make a combo box or list box that is linked to the original table (not your query) for orders. Then go to your query and and add the criteria for order ID selection like;

    [forms].[frm_1].[cmb_OrderID]

    If you try to link back to the same query it won't work.

  3. #3
    Join Date
    Nov 2003
    Posts
    150
    my Main form is called frmOrders , and the name of the text field i want to link to the query is intOrderID. What do i need to replace in the statement you wrote?


    thanks!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try:

    SELECT tblOrderDetails.intOrderID, tblProductInfo.intProductID, tblProductInfo.strProductName, tblProductInfo.curUnitRetail, tblOrderDetails.intQuantity, tblOrderDetails.intDiscount, CCur(tblProductInfo.curUnitRetail*[intQuantity]*(100-[intDiscount])/100) AS curExtendedPrice
    FROM tblProductInfo, tblOrderDetails
    WHERE tblOrderDetails.OrderID = forms!frmOrders.intOrderID
    ORDER BY tblProductInfo.intProductID;


    the
    ORDER BY tblOrderDetails.OrderID
    doesn't seem helpful if you have only one .OrderID, either sort by something else or drop the ORDER BY clause completely

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Nov 2003
    Posts
    150
    When loading my main form, i still get prompted to enter the OrderID.

    Can you take a look at my db?
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by MrCrud
    When loading my main form, i still get prompted to enter the OrderID.

    Can you take a look at my db?
    Simple spelling error, it should have been:

    WHERE tblOrderDetails.intOrderID = forms!frmOrders.intOrderID

    FYI:

    Generally when it asks you to enter a value for a field, that's because it cannot find the specified field in the dataset. Most often this is due to a typo.

  7. #7
    Join Date
    Nov 2003
    Posts
    150
    great!

    works like a charm now!

    Cheers!
    Cheers!

    Mr.Crud

    There are 10 kinds of people, ones that understand digital, others that dont

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Teddy: thanks for the correction

    i copy/pasted from the original post using
    ORDER BY tblOrderDetails.OrderID
    rather than
    SELECT tblOrderDetails.intOrderID
    ho hum


    MrCrud: the original query will also bomb for the same reason. if you are still using that original query somewhere else, edit the ORDER BY to .intOrderID

    izy
    currently using SS 2008R2

Posting Permissions

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