Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53

    Data update

    Hi, I have a data base for generating sales receipts. I have "product table" with "product name" and "cost". I generate receipts using a "Receipt form". When I select a product, the respective cost is generated, which can save and print the record. Now my problem is, when I go and change the cost in my product table of a particular product, all my previous record containing the same product, also gets the change. I want the previous records to maintain the old cost, not to update to the modified cost. Kindly give me a solution, I prefer to use macros or expressions..Thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you must have a COST field on the record. It gets filled from the tProduct table, thus getting the cost of the product 'at that moment'.
    This way it is unaffected by price changes.

  3. #3
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53
    Thanks for your response. Yes I have a "Cost" field on the record. Using a query I have related the tproduct and the main tcustomer. In this query created another calculated "Amount" field, picking the "cost" value. At the end created the receipt form from the query, with the "Amount" field, which is working fine. But still the value changes.

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    Can you upload a zipped copy of your database?

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53
    Dear Mike60smart, As you asked, I am attaching the sample database, which is being used. "Seva Details Table" is the main table, "Seva Items" is the table for entering the cost. "Seva Details Query" and Form "Seva details table" are the main query and form. I use the form to enter sale details and print. Now If I change the "cost" value in the table "Seva Items", the calculated field "Amount" filed value also changes in the previous forms. Kindly help. Thanks
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    The field Amount in Seva Details table is calculated field (has lookup to Seva Items table). Remove the lookup. Instead of using formula in table field, read the sale price in Seva Details form event (or in some of this form's control event), and insert it into control linked to Amount field in form. When the record is now saved, it is saved with price at record creating time. You can also let the user select the current price using combo in form, but this will leave your app prone to user errors (user forgets to select price).

  8. #8
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53
    Thanks verymuch, Arvil , can you let me know the kind of expression suitable for the cost to be added in Seva Details Table record.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Your app has setup I am not familiar with (start form imitating switchboard, use of macros, etc.), so I couldn't edit your app in short time. So I'm limited to explanations only.

    1. It will be better for you to avoid multi-word names for any objects. So e.g. instead 'Seva Details table' table use tblSevaDetails, instead 'Seva Details table' form use fSevaDetails, instead 'Seva item' as name for combobox in fSevaDetails use cbbSevaItem, and instead Amount as name of textbox control linked with Amount field in fSevaDetails use txtAmount (to avoid confusion are you referring to table field or to form control).

    2. For all tables, have primary key defined. The easiest way for this is to have an autonumeric field in every table as primary key. An example how this is essential: Currently your table 'Seva Items' table, or tblSevaItems as I adviced, has structure Seva Items, Cost. Let's assume after some time you have used your app, you realized you made a typo when entering a name for item, and now you have several hundred or thousand entries with faulty name in tblSevaDetails. When you correct the item name in tblSevaItems, you have to manually edit all those faulty entries in tblSevaDetails (otherwise there will be records referring to non-existing item). When you have for tSevaItems the structure like ItemID, ItemName, Cost, and in tSevaDetails ItemID is stored instead of it's name, this problem doesn't exist. (I have to continue later!)

    3. You have to remove the Lookup from tblSevaDetails. In Design mode, select field Amount, and in field properties Lookup tab, select Text Box. Save changes.

    4. Open form fSevaDetails in design mode. Activate combo box cbbSevaItem, (activate item properties when not activated jet,) and add AfterUpdate event for this combo. Enter the code for event - something like
    Code:
    Private Sub cbbSevaItem_AfterUpdate()
        Me.txtAmount = Me.cbbSevaItem.Column(1)
    End Sub
    Me.cbbSevaItem.Column(0) returns item name (with your current setup without autonumeric ID), Me.cbbSevaItem.Column(1) returns current item price. The updated price is saved to tblSevaDetails when the record is saved (i.e. when Save command is given, the app is closed, or when another record is selected).

    When you design items table as tblSevaItems: ItemID, ItemName, CurrPrice, then:
    a) you have to design item details table as tblSevaDetails: DetailsID, Date, ..., Time, ItemID, Amount, ...;
    b) combo's RowSource in fSevaDetails will be 'SELECT * FROM tblSevaItems;
    c) combo's Source = ItemID;
    d) combo's BoundColumn will be 1;
    e) combo's ColumnCount = 3
    f) combo's ColumnWidths = '0,2.5,0'
    g) in AfterUpdate event of combo in fSevaDetails, the row to store amount will be Me.txtAmount = Me.cbbSevaItem.Column(2).

    This all is on fly! I could edit your tables and forms, but I couldnt open the form in Forms view (the form was closed immediately) - so I couldn't check the code.
    Last edited by ArviLaanemets; 03-09-2020 at 05:49 AM.

  10. #10
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53
    Thanks and thanks a lot for your advice. Waiting for more. Regards

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    Raveen1609 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    53
    Again than you very much. I appreciate. I used Query "seva details table" to design the form seva details. So when I do the above steps it is not working. I have the Amount field in query as calculated. I am attaching the database again. Please check
    Attached Files Attached Files

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am with Arvi -- I don't use macros and I'm not understanding your tables, nor the application generally.
    I see 6 Yds Synth. Green Saree in an Address field??

    Let me ramble for a moment. And describe the logic issue I see.

    In your post #1 you said
    when I go and change the cost in my product table of a particular product, all my previous record containing the same product, also gets the change.
    The links I have suggested to you relate to AgreedTo Price. (you say cost, so I'll adapt cost for my description)

    When you have a Cost in your Product table, and you just refer to that cost field in your invoicing details or product details, your reference will be to the latest value in that cost field. So, your historic records that you recorded with a link to that cost field at the time of the transaction will now be reported with the latest value(cost) in the Product.cost field.
    There are 2 cost values at play here-- The current cost of that Product and the cost of that Product when it was sold/purchased. These are 2 separate things. You should record the Agreedto current cost of the product on the detail transaction (OrderDetail/PurchaseDetail) along with the number/quantity of units bought/sold -that is your historic record. It will not be affected by changes to the cost value in the Product table. And we all know Product.cost will change. The historic record should not change and will not change is recorded with the transaction.
    Also, if you have a loyalty program or a sale of any type, then recording the actual AgreedTo cost on the transaction will preserve the historic record data. This allows you some negotiation and agreement to an item's cost and means to record it for historic purposes (audit, repeat/create reports etc.)

    Good luck.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In addition, "Date", "Time" and "Name" are reserved words in access that shouldn't be used for object names.

  15. #15
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Raveen1609 View Post
    I used Query "seva details table" to design the form seva details. So when I do the above steps it is not working. I have the Amount field in query as calculated.
    You are reading Cost for 'Seva Details table' form from table 'Seva Items' (as the query is source of form). This means, that whenever the form 'Seva Items table' is refreshed, the Cost control value is overwritten with current Cost from table 'Seva Items'.

    When you want Cost in table 'Seva Details table' remain as it was originally, you must not overwrite it whenever the form is refreshed. For form source, you have to read Cost from table 'Seva Details table', not from 'Seva Items' table.

    Generally, there are 2 different solutions to get purchases/sales prices correct:
    1. You register current price for every product in products table, and save this price at moment the purchase/sale is registered into purchases/sales log. When there are any discounts, they must be taken into account when price is calculated, or they must be saved in separate field(s) into purchase/sales log too. This is the approach you are seemingly trying (without success so long). Downside of this approach is, that whenever you need history of price for any product, you have to get it from purchases/sales log using a query. at same time, whn all you need is viewing/reporting purchases/sales, it is fast as no calculations are needed;
    2. You have a table where you register price history for every product. You don't save price in purchases/sales log - instead you calculate it based on product and transaction date from price history table. This is the approach Orange is likely advicing. The downside is that you have a lot of calculations for practically any viewing or report. At same time, any historical data is easy to get.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-08-2019, 03:27 PM
  2. Replies: 1
    Last Post: 05-31-2015, 09:20 AM
  3. Replies: 6
    Last Post: 08-17-2014, 06:14 PM
  4. Replies: 4
    Last Post: 11-10-2011, 03:41 PM
  5. Replies: 1
    Last Post: 08-19-2011, 12:16 PM

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