Recalculating a control in all records

[BeallDon]

I have a subform called [frm Debts] located on the main form [frm Clients] and within the subform, i have 2 controls: Debt (which is a currency) and Percent. I have a control in the form footer which sums the Debt. The subform is displayed as continuous. The percent control should calculate the ratio of the record's debt amount compared to the total debt amount. I am trying to get the entire form to recalculate each Percent for ALL RECORDS after each Debt control is updated so that the Percent fields are recalculated based on the updated sum after a new debt is entered or an existing debt is changed. I have tried many different combinations of code and just can't figure this out. I have researched "recalc", "refresh", "on dirty", etc etc etc. Maybe i've got too much stuff floating around now and it's getting all jumbled up. I am still a bit of a novice at this so please don't presume that i know the obvious, such as what to put in a module or whether it should be public or private, etc.

Thanks for any assistance.

 

[Remou]

It seems that you are storing calculations in a table. This is usually considered to be against the rules of normalization.

You may wish to read

http://www.r937.com/relational.html

 

[BeallDon]

so are you saying this can't be done? Access can't "behave" similar to a spreadsheet to recalculate all existing records?

 

[BeallDon]

my calculations are in code. The only calculation on the form is the =sum([Debt]).

 

[Remou]

If the subform is continuous, all unbound controls will have the same value, so row calculations for subforms are generally done with queries.

It is considered unwise to treat a database like a spreadsheet.

 

[BeallDon]

Look, all i want to know is:

When I enter a number in [Debt] (bound control - no calculations), on AfterUpdate, it saves the record and then me.percent = me.debt/me.sum.

Next record, same thing, make entry in [Debt], now [Sum] has changed and therefore [percent] in record 1 is now incorrect, so I would like it to re-adjust or recalculate to the new [sum] value.

It's not a difficult thing to fathom, it's like what Excel does. If i need to make [percent] code to be GotFocus and then have more code that when [debt] changes it sets focus on each record one at a time, that's ok. Any suggestions would be appreciated. I'm only expecting average 6 records per client anyways, so it's not like it will be going through thousands of records.

 

[TheAceMan1]

How are ya Jedody69 . . .

[blue] I am trying to get the entire form to recalculate each Percent for ALL RECORDS after each Debt control is updated . . .[/blue]

If you don't have to save [blue]percent[/blue] in the underlying table try the following:
[ol][li]In the [blue]Control Source[/blue] of an [blue]unbound[/blue] textbox, copy/paste the following:

[blue]=IIf(Sum(Nz([Debt]))=0,0,[Debt]/Sum(Nz([Debt])))[/blue]

[/li]
[li]In the subforms [blue]On Current[/blue] event and the [blue]AfterUpdate[/blue] event of [blue]Debt[/blue], copy/paste the following line:

[blue]   Me.ReCalc[/blue]

[/li][/ol]
If you are saving [blue]percent[/blue]:
[ol][li]Copy paste the following routine to the subforms code module:

[blue]Public Sub RedoCalc()
   Dim rst As DAO.Recordset, qSum As Currency
   
   DoCmd.RunCommand acCmdSaveRecord
   Set rst = Me.RecordsetClone
   
   If rst.RecordCount > 0 Then
      rst.MoveFirst
      
      Do
         qSum = qSum + rst!Debt
         rst.MoveNext
      Loop Until rst.EOF
   
      rst.MoveFirst
      
      Do
         rst.Edit
            If qSum = 0 Then
               rst!Percent = 0
            Else
               rst!Percent = rst!Debt / qSum
            End If
         rst.Update
         
         rst.MoveNext
      Loop Until rst.EOF
   End If
      
   Set rst = Nothing
   
End Sub[/blue]

[/li]
[li]In the subforms [blue]On Current[/blue] event and the [blue]AfterUpdate[/blue] event of [blue]Debt[/blue], copy/paste the following line:

[blue]   Call RedoCalc[/blue]

[/li][/ol]
Don't forget to disable your code that performs the calculations to prevent interaction.

In parallel with [blue]Remou[/blue], although saving calculations to a table can be done its not recommended.

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[MajP]

Public Sub redoCalc()
  Dim strSql As String
  DoCmd.SetWarnings (False)
  strSql = "UPDATE tblDebt SET tblDebt.dblPercentDebt = [currDebt]/DSum('currDebt','tblDebt','clientID = ' & [clientID])"
  DoCmd.RunSQL strSql
  DoCmd.SetWarnings (True)
End Sub

Names might need to be changed

 

[BeallDon]

Thanks for all your input AceMan and MajP.

I tried TheAceMan1's suggestion and THAT'S PERFECT!! the only mod i had to make was to Round(rst!debt / qsum,4).

Thanks again all!