Form Load Calculation not working (1 Viewer)

HI,

As my first post, please excuse me if this is in the wrong place or I get things wrong.

I have a bound form to an invoices table. The form details all invoices raised, all payments made and an outstanding balance. The outstanding balance control is just a basic text box.

On form load, I go to the payment table and select all payments into a recordset, I then add up all payments and use the following calculation:-

Balance = InvTotal - paytotal

The Invtotal is the control on the form that has it's control source set as sum(invoicetotal)

The calculation used to work and the correct balance was displayed, indeed, for one particular client, the calculation works, but for all others, the summed value is blank and so the balance is effectively set to the paytotal value.

I know this is to do with the timing as the form loads and it's records/data are made available, as I have an update button that does exactly the same things as the form load and when pressed, all numbers work.....primarily because the summed invoicetotal value is now available in the form control.

I can see what is happening, but not sure why...I thought that form load gave you all records and would have populated the summed control before triggering the load event....obviously not.....any help, advice, guidance very much welcome.

Thanks
Lee

What event loads the form? I ask because it occurred to me that this might be a solution:-

YouTube Plat List:- Form Load, Activate, Open Issue...

Place the code in the OnCurrent event.

HI Uncle Gizmo,

The form is loaded when a user clicks a button on previous form and the two forms are linked by client ID.

As suggested by JHB, I have placed the code in the OnCurrent event handler, but this does not seem to address the issue, I am just going to try that again to make sure.

Thanks
Lee

HI,

Quick update. I replaced the onLoad handler and used the oncurrent handler but still the problem persists. I also have an onActivate handler that does exactly the same thing.

I use onActivate because the user can navigate away from the invoices form to add a new payment and on returning from that form, I wanted the numbers of the invoices form to auto update rather than the user having to click an update button.

Thanks
Lee

HI all,

Ok, continuing saga.

I decide to try and calculate the sum of invoice totals within the onload handler using

me.recordset.movefirst
do Until me.recordset.EOF = True
IntTotal = intTotal + me.[Inv_total]
me.recordset.movenext
loop
me.Recordset.movefirst

I then used a msgbox to display intTotal and the value is correct, so I thought the calculation:-

balance = abs(inttotal - PayTotal)

would now work but it doesn't!! the calculation still misses the inttotal and so the balance ends up being the paytotal value.

Lee

Ok, so after a few more message boxs to extend debug mode, here is the state of play. The calculations within the onload handler now work and the correct amounts are calculated and end up in the right variables.

The issue I am now facing is that the form control is not updating to the result of the calculation, it remains as the payTotal amount!

Lee

ok, finally found a workable solution.

Using the manual sum of invoice totals, I was then able to update the required form text boxes and all calculations worked properly.

The last issue was event handlers interfereing with each other. As the onload and Onactivate handlers effectively had the same code, I was modifying the onload handler while I worked through the problem....but when I came to check the form control for the right value, the onActivate handler had been triggered and so overwrote the correct value.

I have no removed the onLoad and use the onActivate exclusively and everything appears to function correctly....still bugged by the fact that the form control that sums the invoice totals doesn't appear to be populated prior to the onload or onactivate handler firing.

This used to work but maybe I was just being luckly

Lee

leeby said:

Ok, so after a few more message boxs to extend debug mode, here is the state of play. The calculations within the onload handler now work and the correct amounts are calculated and end up in the right variables.

The issue I am now facing is that the form control is not updating to the result of the calculation, it remains as the payTotal amount!

Lee

Click to expand...

I strongly suspect your problem has to do with an known issue with the recordcount. See here, section 4.

Best,
Jiri

Thanks Jiri, that is a good set of tips

Lee