Results 1 to 4 of 4
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    Update discount value for each record based on the basic price

    Dear Gents,


    Um trying to make a form that contains the following:

    1- List of products ( Continuous form )
    2- Check boxes for each product
    3- text box for entering the value of discount.
    4- Update button

    Here is the work flow i want to do.
    1- User must enter value of discount that he needs to apply on several items using the checkboxes . Let's say it's 12.5 %
    2- Every item have different price , Let's say that we have to products 10$ and 100$ , So i need after pressing update button both products will be 8.5$ and 87.5 $

    The problem now that in my form say the first product it's price is 10$ the discount will be applied on both products based on the first item selected !
    My result in the form was 8.5$ and 8.5$ !! , Which means that both products became 10$ not 10 and 100 !

    Can anyone help please ?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My recommendation is for you to determine exactly WHAT you want to do in plain English. Then, determine HOW that could be done in Access.
    Set up a small test and confirm that your idea can be solved with your HOW option.

    Continuous forms have their place and their quirks.
    When you change discounts on Product prices, you may be changing information of existing invoices and Sales. But it's difficult to put your issue into a business context without some clarity.

    There may be, and usually are, options for How.

  3. #3
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by orange View Post
    When you change discounts on Product prices, you may be changing information of existing invoices and Sales.
    OMG , i didn't think about that before !! .
    Actually i wrote a piece of code that loops through the table and whatever product is check (True) with the checkbox only change it's price , And it works fine but as i said before it changes the products price based on the first product price !

    Code:
    Dim sql_text As String
    Dim NewValue As Double
    Dim ItemValue As Double
    Dim rst As DAO.Recordset
    
    
    Set rst = CurrentDb.OpenRecordset("Products")
    
    
        MsgBox "Are you sure you want to make discount "& me.discountValue.value & " % On these products ? ", vbInformation, "Easy Cash V. 1.0"
    Do Until rst.EOF
        ItemValue = DLookup("PRD_Price", "Products", "Check = -1")
        NewValue = (ItemValue - (Me.discountValue.value / 100) * ItemValue)
        sql_text = "UPDATE Products " & _
            "SET PRD_Price= " & Round(NewValue, 1) & _
            " WHERE Check = " & -1 & ""
            DoCmd.RunSQL (sql_text)
    rst.MoveNext
    Loop
    rst.Close
        DoCmd.RefreshRecord

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here is a link to an older thread where I describe AgreedTo Price. The concept is identifying the current Price of the Product vs the Price of the Product when it as sold.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-14-2015, 10:23 PM
  2. Update Price Based On % From Other Table
    By Auto in forum Queries
    Replies: 10
    Last Post: 07-07-2014, 01:35 PM
  3. Update Price based on multiple column
    By JustinC in forum Access
    Replies: 3
    Last Post: 03-31-2014, 08:37 AM
  4. Replies: 3
    Last Post: 06-28-2012, 09:36 AM
  5. Yearly Discount Price By 20%
    By vdanelia in forum Forms
    Replies: 1
    Last Post: 02-04-2011, 10:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums