Sub form and main form issue - totals on sub form and updating main form problem. (1 Viewer)

[klsblues]

I have a weird issue with a database I’ve written. I have a table called tblQuote which contains the Quote details (Header info) and a tblQuickQuote which contains the individual lines for each quote (The tables are linked by QuoteId in the tblQuote).

I have a form with the main quote details which contains a subform for the individual lines, linked by the QuoteID. As the individual line is added in the subform, there are 3 fields in the subform footer that totals the different costs and there is a bit of VBA on the After Update property of the subform that puts the total values from the subform footer, into the main form fields:

Private Sub Form_AfterUpdate()
Me.Recalc
Me.Parent.QuotePrice1 = Me.TtlCostVatInc
Me.Parent.QuotePrice2 = Me.TtlCostNoVat
Me.Parent.QuotePrice3 = Me.TtlCostNoCharge
End Sub

It has all been working well but an end user called me to say that he reached a certain number of lines and the main quote shows £0.00.

After investigation, I have established that if you add any lines to the subform that requires more screen space than is shown (i.e. you need t use the scroll bar on the subform), the calculation works in the subform footer, but it changes the QuotePrice1, 2 & 3 in the main form to £0.00.

To test this, I originally had the subform showing 10 lines before the scroller was needed and if you added an 11th line, the QuotePrice1 field on the main form went to £0.00. I changed the subform to show 23 lines and it worked fine until you added a 24th line, again , when the scroller was needed.

Thanks in advance for any ideas!

 

[CJ_London]

Why are you using code?

in quoteprice1 you can set the controlsource to

=mysubformctrlname.form.ttlcostvatinc

 

[klsblues]

Hi CJ_London

Thanks for this suggestion, but QuotePrice1, QuotePrice2 and QuotePrice3 are fields that needs the controlsource set to themselves, as they contain the prices of the subtotalled fields on the subform.

The strange thing is, everything works as it should, until the subform uses the next line that is out of the subform window size. The calculations on the subform still work, but once you need to scroll on the subform, the QutoePrice fields on the man form return a £0.00 value.

 

[Mike Krailo]

Can you post a copy of you database? This looks like an interesting problem to me and it's not yet clear to me why only the visible records in the sub form are correctly updating the totals on the main form and then when the scroll bar appears, it zero's out the totals. I would step through the code examining exactly what is happening just before the scroll bar appears on the sub form.

While adding new records in the sub form, do you just start entering unsaved data on the New Record line of the sub form (i.e. dirty the form)? Or do you use a Add New button to create a new record? There is a big difference. I like to show records in the sub form, but not let anyone update the data directly. Instead, use an Add New Quote Line button on the main form that pops up an appropriate form to enter and validate all of the fields before saving. The new record is not created until the user clicks Save or presses Enter while the Save button has the focus. You can do it the other way as long as you validate that required fields have been filled out so you don't accidentally create empty or sparse records.

Again, I'm interested in seeing your original problem up close, so if you could post the database, that would great.

 

[klsblues]

Can you post a copy of you database? This looks like an interesting problem to me and it's not yet clear to me why only the visible records in the sub form are correctly updating the totals on the main form and then when the scroll bar appears, it zero's out the totals. I would step through the code examining exactly what is happening just before the scroll bar appears on the sub form.

While adding new records in the sub form, do you just start entering unsaved data on the New Record line of the sub form (i.e. dirty the form)? Or do you use a Add New button to create a new record? There is a big difference. I like to show records in the sub form, but not let anyone update the data directly. Instead, use an Add New Quote Line button on the main form that pops up an appropriate form to enter and validate all of the fields before saving. The new record is not created until the user clicks Save or presses Enter while the Save button has the focus. You can do it the other way as long as you validate that required fields have been filled out so you don't accidentally create empty or sparse records.

Again, I'm interested in seeing your original problem up close, so if you could post the database, that would great.

Hi Mike,

My DB has a frontend, back end and a third DB which holds the licence info and i'm hesitant to put the full DB up to a public post. Is there a way to send it privately or if you like, i could let you TeamViewer on to my PC and I'll show you the issue?

 

[CJ_London]

Doesn’t need the whole db - just the forms and related tables/queries. Populate the tables with dummy data

also considered poor practice to store calculated values- so much can go wrong

 

[Mike Krailo]

I suspect you are trying to update what should be a calculated field, but is really a bound field. If you have the default value of the field set to zero, then any time the code does not properly update the field for whatever reason, you just get the zero value in your main record. This might be one of those strange cases where you wish to store calculated values in the main form.

Normally, you display calculated values on the form (unbound control) just so you can see the data. Then when all relevant data has been entered, you do your final quote by generating a report showing all the final calculations. I had a client that wanted to store these calculations and I could not convince him otherwise. The truth is, the report or even your form will always show the correct calculations as long as you do not store the calculations. The desire to do this is strong, but it just isn't necessary. Store only the raw data (the stuff in your sub form), and display the calculations from that data on the forms and reports.

 

[klsblues]

Hi guys,

Thanks for your responses - very much appreciated.

I have attached a stripped out DB for you to look at and linked the backed to C:\Temp\

If you select quote number 159, you will see the subform I have pre-populated to 'fill' the size of the subform. If you add a line or 2 (just select any product and add a qty), you will see the Quote1 box on the main form go to £0.00. If you then delete any line (use the DEL button at the end of the line), it returns the correct calculated value on the main form.

If you then edit the subform to a larger height and enter more lines, it will work crrectly until the subform window is fully completed and then return a zero value in the main form.

Hope this makes sense and again, thanks in advance for your help

 

[Pat Hartman]

To test this, I originally had the subform showing 10 lines before the scroller was needed and if you added an 11th line, the QuotePrice1 field on the main form went to £0.00. I changed the subform to show 23 lines and it worked fine until you added a 24th line, again , when the scroller was needed.

This is clearly a bug. Use the Feedback link in the Access backstage to report it.

HOWEVER, you should NOT be storing the calculated totals anyway so use @CJ_London 's suggestion on how to show the totals without attempting to save them. In other places in the application where you need the totals, calculate them in a query or in a report. Storing calculated values violates second normal form and unless you are building a data warehouse - which you are not - you should not store calculated values.

 

[Mike Krailo]

I'm unable duplicate your problem. It must be something local to your version of access or computer. I added two lines on that Test quote 159 and the Quote box 1 populated perfectly.

 

[Mike Krailo]

Spoke too soon. I was able to duplicate the problem after more testing. There is a timing issue with the recalc doing the recalculations and then immediately trying to populate the values on the main form. If I step through the code, it works every time. I tried putting a DoEvents after the Recalc but that did not help. There is more to it.

 

[klsblues]

Hi Mike,

The end user reported this and I have the same issue. i am on the version below - can you let me know what version you have please.

Also, could you try adding another couple of lines please, as i can only see 10 lines on the attachment and it is happening once the line goes past the window size.

Thanks again

 

[klsblues]

Hi Mike,

Ignore my last post - I posted at exactly the same time as you came back to me!

 

[klsblues]

Hi Mike,

The end user reported this and I have the same issue. i am on the version below - can you let me know what version you have please.

Also, could you try adding another couple of lines please, as i can only see 10 lines on the attachment and it is happening once the line goes past the window size.

View attachment 109220

Thanks again

Could I put a timer event somewhere and if so, where and how?

 

[Mike Krailo]

I'm not sure this is related to the subform size yet as much as it is simply a timing issue. The calculations have to completely finish before you can copy the results to your main form controls. I'm trying to figure it out at the moment. I've run into this in the past and can't remember what I did to fix it.

 

[klsblues]

Hi Mike,

Thank you very much for working on this - I await any suggestions you may have.
Kind regards.

 

[Pat Hartman]

Thank you very much for working on this - I await any suggestions you may have.

@CJ_London gave you the solution. You don't need to wait

 

[Mike Krailo]

OK, I attempted to find a working solution for your original setup that updates the bound fields, but it was going nowhere. I opted to do exactly as C.J. London suggested and put a button the the form to manually update your bound fields in the table as needed. I know it is not ideal to match what you had originally wanted, but this is much better and to be honest, I wouldn't even store those totals in the table anyway. I just gave you that option just incase you have other reasons for doing so.

 

[klsblues]

Downloaded and incorporated into my DB.

Thanks everyone - good to have such great knowledge and support from the access community!

 

[Pat Hartman]

I forget to say welcome earlier so Welcome

All three people who contributed suggestions told you that what you are doing is wrong. @Mike Krailo did provide a working code option that allows you to save the calculated fields. Just because you can save this type of calculated value doesn't make it right and you leave yourself open to creating data anomalies where the detail data sums deviate from the saved value and that is why experts do not save calculated values. I hope you will take this instruction to heart and change the application's queries to calculate the sums as needed so they will always be fresh.