Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: stop auto updating sell price field

    i have a invoicing form that has a sub form that referrs to productid, quantity, sellprice etc..

    when i insert my product id it automatically places the current sell price for that item, the problem is when i go and change the current sell price for that item the database changes all my old invoices that have that item to the new price.

    how can i stop this?

    Regards Gwoods.

    PS sellprice is a calculated field.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It is typical to store the selling price with along with the other invoice details. It can seem like a normalization error, but you can think of the price as an attribute of the sale. Your alternative is to store a history of selling prices for each item with effective dates, but personally I would only do that in a situation where prices did not change very often.
    Paul

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    So i should be storing sellprice in my concatnerated table, not in my partdetails one?
    sorry if i have missunderstood

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, typically a parts table would include info about the part, including its current selling price. An invoice details table would also include a field for the selling price applicable to that sale. So in effect you'd have a price field in both. I think the Northwind sample db uses this method. In your form for invoice details, when you choose a product you get the current selling price from the parts table and populate the field in the invoice table.
    Paul

  5. #5
    Join Date
    Nov 2010
    Posts
    4
    Thanks Paul really appreciated, i now think i know how to do this, i'll just use the dlookup function to get the price out of the part details table.

    i owe you a beer.

    kindly Gwoods.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, and welcome to the site by the way. Rather than a DLookup(), the easier method is to use a combo box to let the user pick the product. Include the price field in the combo's row source (it can be hidden if you want). Then in the after update event of the combo, this will populate the price field:

    Me.SellingPrice = Me.ProductCombo.Column(x)

    where x is the number of the column containing the price. You'd have to change to the appropriate names of course.
    Paul

Posting Permissions

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