subform total showing on form correctly but £0.00 on vba

[dtdukes86]

hi all ive used this site b4 with some success so im hoping i can get some advise with a small issue im having.

the problem seems to not work on my pc only but does work on slower systems like the ones at work , very strange!!

so basically ive got a subform with some loan repayment payments in. things like date. amount paid etc. and at the the bottom of the subform is a sum field that totals the payments , easy! i hear u say. The sum field refreshes automatically when new payments are added. Great i thought

here the issue.. im using vba to calculate [new balance] by using something like [total due] - [total paid] (the sum value discussed above) however my VBA returns the sum total as £0.00 therefore the loan balance never decreases. It did work on other machines that are slower but it almost like my pc proccess the VBA code b4 access automatically updates the sum field in the sub form :-( Any thoughts advise or suggestions please.

and no its no wind up lol >< . i'd be interested in using a sum method and a qry. Just to provide a total of the payments for the current laon but i dont know how to get vba to sum a column in a qry as this might work better long run ?

heres the code im using in vba after a new payment is added which works on slower pcs

Forms("frmactiveloan").Balance = Forms("frmactiveloan").totaltopay - [Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

where total to pay is the initial loan amount and totalin is the name of the sum field on the subform . see photo

i hope somebody can follow what I mean , questions welcome

ps i did try posting to vba variables but still shows as £0.00 just like the vba runs before access totals the subreport data

 

[June7]

Are you trying to save calculated balance into table field? Why? Just calculate for display when needed.

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[dtdukes86]

hi june7 the balance field is used in several places and works well. i can make it work as a unbound field as you say but would prefer it to store on a table and update when changed as it was doing on the slower systems at work.

 

[dtdukes86]

also the database is quite large so i didnt want to upload and remove all data its just a big task i can screen shot anything you think might help of coruse

 

[The_Doc_Man]

First MAYBE we can simplify something.

Forms("frmactiveloan").Balance = Forms("frmactiveloan").totaltopay - [Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

Is this being computed from VBA in the Form Class Module of frmactiveloan ? If so, you can simplify this to

Me.Balance = Me.totalpay - Me.subfrmPayments.Form.totalin

In particular, I'm a little bit leery of this construct:

Forms("frmactiveloan").Balance = Forms("frmactiveloan").totaltopay - [Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

If the name of the sub-form control that holds the subform is subfrmPayments then that parenthetical item isn't needed and may confuse the issue. See my simple-minded substitute.

Now, here is another factor... "works on slower PCs" - which is a totally meaningless consideration if the ONLY difference is "slower." Computers are entirely deterministic. Give them the same instructions, they will do the same things at whatever rate they work. HOWEVER, the fact that it works selectively on some PCs offers another place to look. You see, each system has a unique registry. Each machine potentially has different configurations.

Look for these things...

Open to the VBA window, look at Tools >> References on two machines - one that works and one that doesn't. Compare the ORDER of references and look for any MISSING references.

From the Access ribbon, choose File >> Help and look to the right. You will see Access version numbers and "bitness" of 32 or 64 bit. Compare these on the working vs. non-working system.

Click on the window icon at lower left, select Settings >> System >> About. In the middle of the page you will see the O/S build version and a few other items. Compare differences between working and non-working systems.

A more tedious comparison is again, from the Access app window, File >> Options >> Current Database ( which is literally dozens of options ) and look for differences in settings there, too. Though to be honest, stuff here is UNLIKELY to affect the SUM operation.

If there is a common theme here, it is that "X works on A, X fails on B" means you are not really looking for something wrong with X itself, but rather you are looking at differences in A and B. Then you work backwards to figure out how that difference would affect your program that way.

 

[dtdukes86]

First MAYBE we can simplify something.

Forms("frmactiveloan").Balance = Forms("frmactiveloan").totaltopay - [Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

Is this being computed from VBA in the Form Class Module of frmactiveloan ? If so, you can simplify this to

Me.Balance = Me.totalpay - Me.subfrmPayments.Form.totalin

In particular, I'm a little bit leery of this construct:

Forms("frmactiveloan").Balance = Forms("frmactiveloan").totaltopay - [Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

If the name of the sub-form control that holds the subform is subfrmPayments then that parenthetical item isn't needed and may confuse the issue. See my simple-minded substitute.

Now, here is another factor... "works on slower PCs" - which is a totally meaningless consideration if the ONLY difference is "slower." Computers are entirely deterministic. Give them the same instructions, they will do the same things at whatever rate they work. HOWEVER, the fact that it works selectively on some PCs offers another place to look. You see, each system has a unique registry. Each machine potentially has different configurations.

Look for these things...

Open to the VBA window, look at Tools >> References on two machines - one that works and one that doesn't. Compare the ORDER of references and look for any MISSING references.

From the Access ribbon, choose File >> Help and look to the right. You will see Access version numbers and "bitness" of 32 or 64 bit. Compare these on the working vs. non-working system.

Click on the window icon at lower left, select Settings >> System >> About. In the middle of the page you will see the O/S build version and a few other items. Compare differences between working and non-working systems.

A more tedious comparison is again, from the Access app window, File >> Options >> Current Database ( which is literally dozens of options ) and look for differences in settings there, too. Though to be honest, stuff here is UNLIKELY to affect the SUM operation.

If there is a common theme here, it is that "X works on A, X fails on B" means you are not really looking for something wrong with X itself, but rather you are looking at differences in A and B. Then you work backwards to figure out how that difference would affect your program that way.

hi again doc thanks again for the reply.

unfortunately the code isn't ran from the same form its ran after a new payment is entered so its event is when the save payment button is processed on the enter payment form therefore the me. command wont work here at present.

do u know another event on the main form that i could use after the payment is added?


however do i share your thoughts with the code itself and funny you should pickup that part in red as this is the exact part which works here and there.

[Forms]![frmactiveloan]![subfrmPayments(foractiveloanfrm)].[Form]![totalin]

do you have a better way of referencing that particular field. the part in ( xxx ) is part of the subform name , so maybe step one is to rename it ? i do use the ( ) alot because the project i build are usually very big and remembering sections later down the line is difficult for me.

i will of course follow your recommendations regarding looking at differences between pc A and B . i might even crack my laptop out and try it on there also .