Form with subform doesn't show totals unless keyed

[RoyinForest]

I have an application that has two tables, linked on a common key. The main data entry form is for invoices, and has a sub-form for parts used. A similar report/sub-report is used for reporting.

Everything is fine for records that are entered via the form. My problem is that when I import data from other sources ( consolidation process ) the calculations are not performed. So when those records are displayed no totals appear until I click on a control that drives the calculations. I don't want to store totals in the database but I need to have them reflected on the viewed form without operator intervention. Otherwise I'm open to incorrect decisions when the operator doesn't notice that an invoice doesn't reflect the cost of parts used.

I'm looking for a "one-time" process that I can invoke after loading new data that would "touch" each record (ideally each "new" record but there's no impact to re-calculating existing ones) and drive the calculations so that the viewed form is correct.

It doesn't need to be "easy" or automated since the end users won't do it, only myself as part of the data consolidation.

Any ideas?

Thanks

 

[Rabbie]

WhYou shouldn't have calculated values in your table. The form should have fileds which have formulas like =Quantity*cost which will calculate the total price of that item.

You can use the same technique on the report as well. Then the operators will see the costs regardless of how the information was obtained.

 

[RuralGuy]

What control "drives" the calculations and what code are you using to get the totals? Properly done, the totals should appear without any user intervention as long as the data is in the recordset.

 

[RoyinForest]

I agree with not storing calculated values in tables. And I don't have them. I DO have calculated totals for such things as taxes, subtotals, grandtotals, on the FORMS and REPORTS. They are calculated whenever the item they pertain to is entered via the form. EG subtotal is the sum from the sub-form containing parts sold. Tax is a % of subtotal. Grandtotal is the sum of tax plus subtotal. These fields are not in the tables.
So when data is entered on the form, access processes the fields as they are entered and the afterupdate events trigger the calculations.

BUT -- (and here is the problem), when I load data from other sources, and the event triggers don't apply, the resulting form for those records will not show the summed totals. Unless the operator notices the total isn't accurate (eg doesn't include tax) and tabs through a field, thereby triggering the afterupdate event.

What I need is a way of taking the manual operator intervention out of this process whenever I load new data. It isn't going to be a frequent activity, but as we consolidate the multiple data sources into this database, I don't want to manually tab my way through several thousand records.

Thanks again.

Roy

 

[Rabbie]

Now I see what your issue is. I hadnt realised that the button on your form just recalclated those fields. Is it possible for you to requery your form as part of the consolodation process.

It would help if you could let us know more about these updates and how often they occur etc.

 

[RuralGuy]

I believe something else is going on. Tabbing through a control without making any changes does *not* cause the AfterUpdate event to fire since no Update has taken place.

 

[RoyinForest]

I'm not sure I understand what is involved in "requery the form". The form is based on the table, not a queried subset. Having said that, "nothing is impossible" - "some things may not be viable". I'm game to consider any solution, and I do appreciate the insight of others with more understanding that I.

As for frequency, I expect to go through about 1 major import and 5 smaller ones over the next 2-3 months as we consolidate this client's records from its various laptops, etc. After that I don't forsee needing it again UNLESS I need to do another redesign of this database. (Which is unlikely since I've just completed one that (among other things) normalized the tables and brought it in line with Access vs Excel.

I believe the problem is a systemic one. IE - having controls implemented on FORMS and not using the form to add data (eg via import).

Thanks again for the added brain cells.

Regards,

Roy

 

[boblarson]

Roy -

What you can do is to put your calculations into functions which then can be set as the control source of the control (like =MyFunction() ) and that SHOULD get updated as data changes in the form.

 

[Rabbie]

by requerying I meant use the .requery method to refresh the form. Sorry if that was unclear. Bob seems to given you a good idea to try

 

[RoyinForest]

I believe something else is going on. Tabbing through a control without making any changes does *not* cause the AfterUpdate event to fire since no Update has taken place.

My apologies for my shorthand. By "tabbing" I meant "tab to the totals field, and hit enter. That field's afterupdate will perform the totalling calculations for the form.

I guess I tried to shorten the problem description too much.

Regards,
Roy

 

[RuralGuy]

I still believe the updates should happen automatically. Can you post the code you are using for all of the events in the "totals field" control? You must have something in the LostFocus or OnExit events.

 

[RoyinForest]

Roy -

What you can do is to put your calculations into functions which then can be set as the control source of the control (like =MyFunction() ) and that SHOULD get updated as data changes in the form.

That is the method I use now, and it works perfectly AS LONG AS SOMEONE ENTERS DATA in the form.

MY problem is that I'm IMPORTING new data into the tables and it is being VIEWED via the FORM. IF the operator doesn't notice the totals aren't correct when viewing the form, then no total calculations are re-done and the value can be incorrect.

Example:
initially all data is manually entered and all is well. Consider invoice #1 which has 5 parts used included in it (via sub-form to related table)

Now let's assume I import additional data which includes additional parts for invoice #1.

Now when I view invoice 1, the data will show ALL the parts (including new additions as it should). the sub-total from the sub-form will even correctly show on the main invoice form.

BUT, since I'm only VIEWING this invoice form, NOTHING has happened (YET) to drive the events to perfrom the total calculations. So the total, as displayed at this point, will reflect the total BEFORE the IMPORT.

The only way (I know of so far) to get the correct total is by driving the after-update event for the total field by tabbing to it and pressing ENTER.

I'm looking for a better way.

Hope this description helps describe my problem better.

Thans again to all for your assistance.

 

[Rabbie]

Can't you put the recalculation code in the on_current event of the form

 

[RoyinForest]

I still believe the updates should happen automatically. Can you post the code you are using for all of the events in the "totals field" control? You must have something in the LostFocus or OnExit events.

I must leave this now to go to onsite with a different client. I don't have any lostfocus or onexit events coded (that I know of but will certainly check on return)

IS the an event I can trigger (to do this calculation function) when the form LOADS each record. That'd still mean paging through the entire table, but at least I could just keep hitting the forward key instead of tabbing into each form and clicking a button.

Thanks again,
Roy

 

[RoyinForest]

Can't you put the recalculation code in the on_current event of the form

OUCH! I could have if I'd been smart enough to know such an event was available. I think I'd still need to page may way through each record in the table though. Is that correct?

At least I have something promising to try.

Thanks very much.

Roy

 

[RoyVidar]

There's Me.Requery to requery the data source of the form, then there's Me.Recalc which is intended to make calculated controls on a form recalculate.

Mayhaps issuing a Me.Recalc at the end of the code that imports? Or first a Me.Requery, then a Me.Recalc?

 

[RoyinForest]

Can't thank you enough for the WONDERFUL assistance

Can't you put the recalculation code in the on_current event of the form

Rabbie, your advice has given me an excellent way of preventing mis-understanding when viewing the customer invoices. I wish I'd known of
this forum much earlier.

Just as a precaution I can run quickly through the table after importing the files and make sure all is well before turning it over to the users.

Thanks again for being so forthcoming with excellent advice.

Roy

PS - while not a replacement for having "advisors" such as you available on this forum, I think it's time for me to buy another book on ACCESS. I'm somewhat embarassed at not knowing about FORM level events.

 

[boblarson]

I'm somewhat embarassed at not knowing about FORM level events.

Try not to let it bother you too much. There is a LOT to learn in Access and about how to use Access, so it just takes a lot of time, and doing, to really get it. Books are definitely helpful and good reference material, but actually getting in an doing it and making mistakes and all is one of the best ways to learn.

One key suggestion that I might make, as you've already noted about this forum being a good learning tool, is to scan through the posts and subscribe to the ones that people are asking questions that you would like to know the answer to as well. Then, when they get answered you can get notified of the posts and can go look to see how the question was answered. I did that a lot for about 8 years. I didn't post many questions but I used others and did searches to find the answers to questions I was interested in.