Sumform SUM doesn't calculate on Load Event

[Curious]

Hi All, I have a subform which has a field in the footer to calculate the sum of a field =Sum([SaleAmount]).

On the main form, I have a textbox that references the subform's [SaleAmount], Main form control =[Forms]![MainForm].[MainForm_subfrm].[Form]![SaleAmount]

I have a combo box which looks up a list of reports and then requeries the recordsource for the subform by the selection.

On the AfterUpdate even of the Combo box on the main form, I requery the subform recordsource, and the SaleAmount on the main form, recalculates itself without any problems.

However, when the main form loads, I have put in the OnLoad event to set the recordsource of the subform to the first value in the combo box. This displays the correct records on loading.

But, the Sum([SalesAmount]) = 0, on the Main form, no matter how many records are showing.

I think I'm missing something with the OnLoad event.

I checked the subform on it's own, and it had no problems showing the Sum([SalesAmount]). I even used the same code that works in my AfterUpdate event of the combo box, and put it in the OnLoad event, but still no success.

I'm a bit lost as to why it works fine for my combo box but just doesn't work when the form is first loaded up?

 

[boblarson]

Instead of this:

=[Forms]![MainForm].[MainForm_subfrm].[Form]![SaleAmount]


Use this in the main form's text box to refer to the subform:

=[MainForm_subfrm].[Form].[YourSumTextBoxNameHere]

 

[Curious]

I made the change, but I'm still experiencing the same behaviour as before.

The total calculates correctly after changing the recordsource query through my combo box, but doesn't display the correct total when the main form first loads.

I should also mention, I'm using a piece of code (which I found on this forum) to stop displaying an Error# in the Main form's field when the subform has no records to display:

'***************** Code Start ***************
'This code was originally written by Keri Hardwick.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Keri Hardwick
'
Public Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
    If Not (IsNumeric(testvalue)) Then
        nnz = 0
    Else
        nnz = testvalue
    End If
End Function
'***************** Code End  ****************

So my Main form control =nzz([MainForm_subfrm].[Form]![SaleAmount])

Not sure how important that is, but I'm really lost at the moment.

 

[boblarson]

Can you post your database?

 

[Curious]

Hi Bob,

In attempting to post a stripped down version of the database I discovered the problem.

I have two categories of reports I'm using, Category 1, Category 2.

Each Category has some different field names. So I used VB code on the AfterUpdate event of my combo box and on the OnLoad event to change the relevant Field Names and Control Sources values.

However, I'd also set my my form to select the first option from the combo box list OnLoad. Which meant that the field names should have been set to Category 1 report. They weren't, the default setting was for Category 2 reports, and my fields were set that way also, so the SUM([SalesAmount]) in the footer, wasn't finding the field name that it needed, thus returning $0.00 no matter how many records I tried.

I fixed the problem by setting the default Field Names and Control Sources to that of Category 1 reports, as this was the first category of report in the list.

I would post the stripped down version of the database, but now I can't get that working, and don't have a lot of time to work on it right now. Got a deadline looming! Maybe I'll post it on the weekend.

Thanks for your support, I'm impressed with how quickly you responded.

 

[Curious]

TODAY I LEARNT A VERY IMPORTANT LESSON.

The reason why I was having the problems mentioned above is because I did NOT realise that -

Subform [EVENTS] load BEFORE the Main Form [EVENTS].

Once I placed the code that was in the OnLoad Event of my Main Form, into the OnLoad Event of my Subform, the problem fixed it self.