Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: Set value of field based on another field

    I have a table QuotationTransactions with fields ProductID, SellingPrice1 and SellingPrice2. I have created 2 fields on a form (ProductName and SellingPrice)
    I need to allow the user to chose the Product and the SellingPrice should be filled based criteria

    I have done the following which is not working
    The source of ProductName is select productid, productname, sellingprice1, sellingprice2 from tblproducts
    i set the source of sellingprice to products.column(2)

    Can Someone recomenned me a better way to do this as I have to enter quantity for the product and then compute the sum total

    Thank you

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    When you say
    "products.column(2)"
    I hope you really mean
    "ProductName.Column(2)"

    Also, I assume that ProductName is a combo box, and that SellingPrice is a text box.

    Since this obviously represents an Order Detail form that's bound to an Order Detail table, why not just add the quantity field right there on the form, since it's going to be saved to the table as well. No need to save the total price to the detail table, save that info for the Main Order table. But I'm getting ahead of the game.

    Sam

  3. #3
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    Yeah you are right I have been thinking in those terms too.

    Product Quantity Unit SellingPrice Total
    --- --- --- --- ---

    Sum Total

    So the user would use a drop down to select a product. Enter the Quantity and then Choose the unit for that product (eg: meter, boxes, rolls) Based on the unit chosen, total should be quanity*sellingprice1 or quantity*sellingprice2
    And then Sum Total is sum of all totals.

    When I use productname.column(2) , the prices show up at #Name# so I am unable to get Totals

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    OK. Now I think I understand, although an Access attachment would have been more enlightening.

    Most of what I wrote here needs re-thinking. sorry I was so hasty.

    Good luck,

    Sam
    Last edited by Sam Landy; 11-29-06 at 15:23.

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Please attach an Access upload that includes the form and the tables that feed the form, including the table that feeds the combo box. I think I see something 'interesting' but I can't be sure.

    Sam

  6. #6
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Attachment

    Here is the attachment. I am trying to make a quotation software. Quotation Subform is actually a subform that I will use in a main Quotation form, which I will use to create a Quotation.

    UnitPrice should be set to SellingPrice1 by default
    But if The Unit is selected sqm or meter then UnitPrice should be set to SellingPrice2
    Then I need to find the Total for each product and then the grand total for the quotation
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Sorry, I can't see anything at all without the main form. All you sent me was a subform that has no controls on it. In order to allow me to help you, please re-upload the main form along with the subform.

    Sam

  8. #8
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    Hope this helps
    Attached Files Attached Files

  9. #9
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    -- bump --

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Red face

    I'm having unusual problems, and I'm not sure why. I worked on it Friday for a while, as well as today, and I just can't get it.

    By the way, what version of Access are you running? Are you using ADO or DAO?

    Sam

  11. #11
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    I am using Access 2002. Not sure about ADO or DOA. I would say whatever is default

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unhappy

    It defaults to ADO, which explains my problem. I'm simply not familiar enough with ADO objects; I've been using DAO for the past umpteen years.

    Sorry,

    Sam

Posting Permissions

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