Results 1 to 10 of 10
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Automatic calculation of table field (A2003)

    Given 3 table fields, Field1, Field2 and Field3. Is there a way to have Field3 updated automatically with the sum of the other two when either of field 1 or field 2 become NOT NULL?
    I know I could do the addition in an "On Change" event, but I seem to recall the facility of having such calculations done automatically.
    Thanks,
    Bill

  2. #2
    DepricatedZero's Avatar
    DepricatedZero is offline Cthulhu Fhtagn!
    Windows 8 Access 2007
    Join Date
    Apr 2013
    Location
    Cincinnati
    Posts
    65
    Not sure if it would work or not but if you put the calculation into the Default Value property, perhaps?

    I would do it in an AfterUpdate event, personally

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    If I understand your question correctly, in the Control Source for Field3, enter

    =Nz([Field1],0)+Nz([Field2],0)

    Having an Expression in the Control Source insures that the Calculated Field will not be stored, which is as it should be. Calculated Fields should simply be re-calculated, as needed.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    "AfterUpdate event" with each of Field1 and Field2? (Meaning if either of them get updated)

  5. #5
    DepricatedZero's Avatar
    DepricatedZero is offline Cthulhu Fhtagn!
    Windows 8 Access 2007
    Join Date
    Apr 2013
    Location
    Cincinnati
    Posts
    65
    Linq's is what you want, I'm sure of it

    Better than how I would have done it

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    While I won't argue with Linq's point about preferred practice with calculated fields, my case is one where it's preferable to store the total in consideration of its multiple use elsewhere. That being said, I'm still having problems with calculations being triggered and the display being updated accordingly. My code is as follows:


    Private Sub tbPayAmt_AfterUpdate()
    Call CalculateTotal
    End Sub


    Private Sub tbToSch_AfterUpdate()
    Call CalculateTotal
    End Sub

    Private Sub CalculateTotal()
    If Me.Dirty Then Me.Dirty = False
    If IsNull(Me.PayAmt) And IsNull(Me.ToScholarship) Then
    Me.Totals = Null
    Else
    If Not IsNull(Me.PayAmt) And IsNull(Me.ToScholarship) Then
    Me.Totals = Me.PayAmt
    Else
    If Not IsNull(Me.PayAmt) And Not IsNull(Me.ToScholarship) Then
    Me.Totals = Me.PayAmt + Me.ToScholarship
    Else
    If IsNull(Me.PayAmt) And Not IsNull(Me.ToScholarship) Then Me.Totals = Me.ToScholarship
    End If
    End If
    End If

    If Me.Dirty Then Me.Dirty = False
    End Sub

    What is NOT happening is the update to the text box bound to "Totals". That is, the changes made to either of "Me.ToScholarship" or "Me.PayAmt" do not reflect immediately in the text box bound to "Totals". I thought forcing updates to the record in the DB would accomplish a refreshed display of the record, but it does not.

    What am I missing here............seems really fundamental, but so far I'm not seeing it.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Such a simple calc, is it really that much trouble to create the expression when needed? Can build a query that has the calculation then use the query for those multiple elsewheres. Upgrade to Access 2010 and have Calculated field type available.

    Otherwise, I suggest expression in textbox referencing textbox names: tbxPayAmt + tbxToScholarship (use the Nz function on one or both fields if Null is possible, use an IIf if you don't want to save 0 if both Null)

    Then code in some event to commit the calculated result to record. Perhaps in the record BeforeUpdate:

    Me!Totals = Me.tbxTotal
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Thanks. I updated the multiple use query with: Total: IIf(IsNull([toscholarship]),0,[toscholarship])+[payamt]

    The only minor rub is when the user sweeps the amount shown in text box "tbSch" (ToScholarship) and presses the "Del Key" that it's then necessary to press enter before "Total" gets updated. Maybe do something in a "Key_Down" event when the KeyCode is vbKeyDelete?

    Bill

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe, not something I ever worried about. User sees new calc after edit is committed by Tab or Enter out of the control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    It really is minor, so I'll just drop the issue in favor of enjoying some beautiful Spring weather in the Sierra.

    Thanks for your help,
    Bill

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

Similar Threads

  1. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM
  2. Replies: 17
    Last Post: 10-18-2012, 03:45 PM
  3. Automatic form field fill in
    By tsn.s in forum Access
    Replies: 4
    Last Post: 11-07-2011, 01:21 PM
  4. A2007 Runtime on same machine as A2003
    By jhollingsh615 in forum Access
    Replies: 4
    Last Post: 05-24-2011, 12:17 PM
  5. Automatic Calculation in table
    By musicalogist in forum Access
    Replies: 4
    Last Post: 04-22-2010, 11:52 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