Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Calculated Field (A2K)

    First, no lectures. In this instance, it is a necessity and not a frivolity.

    Back in Post 278740, Hans made the following suggestion:

    Let's say that the calculated control is named txtCalculated, and that the field to be updated is named StoredValue. Make sure that StoredValue is part of the record source of the form. The code could look like this:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.StoredValue = Me.txtCalculated
    End Sub

    Which is the way that I thought should work.

    So, my code looks like this:

    Private Sub Form_BeforeUpdate()
    Me.WB_Inv_Mst_AvgCost = Me.txtRealAvgCst
    End Sub

    But it doesn't work. I also tried BeforeUpdate, OnChange for the specific field, etc.

    I'd appreciate any suggestions,
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Calculated Field (A2K)

    In what sense doesn't it work? Do you get an error message, or doesn't WB_Inv_Mst_AvgCost get updated, or ...?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Calculated Field (A2K)

    Hi Hans,

    No error messages of any sort, it just does not update. You can probably guess where this problem is buried, but I decided to attack it alone as opposed to burying it at the back of the Inventory post. As I mentioned, earlier, once this little @#$!% is working, I'll post the finished product. But, in the meantime, I've got to get past this because the whole Average Costing scenario is dependent on this calculation being written back each time.

    Thanks for looking in so quickly,
    Cheers,
    Andy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Calculated Field (A2K)

    The Before Update event of a form only fires if one or more bound controls have changed. It doesn't fire if you just move from record to record.

    To populate the WB_Inv_Mst_AvgCost field in existing records, you will have to create an update query that updates this field with the calculated value.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Calculated Field (A2K)

    Hans,

    I think what you are describing is an after the fact updating of the field. In my particular instance I need to have the field updated "on the fly" as it is possible to be entering any number of receipts, each of which will change the Average Cost , thereby necessitating "immediate" write-backs to the table in order to pick up the previous Average Cost.

    You said:

    The Before Update event of a form only fires if one or more bound controls have changed. It doesn't fire if you just move from record to record.

    As I'm creating the quantity in each new record, I thought that this would trigger the update.

    And, to add injury to insult, the field that expect to trigger the updating is referring back to a field in a subform ( and it does work ) and looks like this:

    =[subFormInput].Form!txtInv_Received_Qty

    I've tried the: Me.WB_Inv_Mst_AvgCost = Me.txtRealAvgCst in both the Before and After Update of this field.

    Thanks,
    Cheers,
    Andy

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Calculated Field (A2K)

    Andy,
    You can limit the criteria in the update query so that Access isn't wasting time updating *all* of your records...only the one with the current focus. Depending on when your calculated text box field actually derives the "true" value, that is - the one you want written to your table -, that will pretty much decide when your code needs to fire, running the update query.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Calculated Field (A2K)

    Calculated expressions won't trigger the Before Update or After Update event of a form, since a control with a calculated expression as control source is not bound to a field.
    You will have to use the Before Update or After Update event of the subFormInput subform, but whether that will be successful I cannot guarantee. If you want to refer to controls on the main form from the subform, use Me.Parent.ControlName, e.g.

    Me.Parent.WB_Inv_Mst_AvgCost =Me.Parent.txtRealAvgCst

    (You may run into problems with the order in which controls are updated)

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Calculated Field (A2K)

    Hans & Jeremy,

    thanks again for your help. This will have to wait until morning for me, because apparently running back and forth to the computer when you have guests is not considered socially acceptable. Go figure!

    Catch you tomorrow,
    Cheers,
    Andy

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Calculated Field (A2K)

    Hans,

    Sorry for the delay in responding, our Internet has been down for a day or so. I attempted your changes as best I could with less than desirable results. So....
    I reworked the whole Average Cost Inventory example and have gone back to that thread ( 504148 - Inventory Calculation ) as, obviously, these two issues were connected.

    Hope the revised example will clarify,
    Cheers,
    Andy

Posting Permissions

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