update tables and calculated fields without opening form

Question

I have an Access 20 database that has data linked to a SharePoint Library List. The Data is collected via an Infopath form.

In the database, the Form used to display data related to each project uses a query to draw from a table of clients and a table that tracks the costs for the project. The for contains a subform which tracks the time spent on a project calculating time elapsed between the time started and time stopped. Am umbound text box in the footer of the subform calculate the sum of the hours worked and a text box on the main form displays the total hours worked from the subfor.

Data entered via the infopath form does not get calculated in the mainform until the form is opened. As some users do no even go into the database and enter their data via the Infopath form only, the time spent on these one-off projects do not get tracked on the main form either - only the sub-form hours table.

Now here is the question: how can I code the data to calculate without opening each project in the form view?

Answer 1

You are going to have to write a query or perhaps a script / executable that duplicates the logic done on opening the form.

If that's way too difficult (a distinct possibility). Then other options are.

Write an application/script that opens the mainform for each thingy. Better still if you can detect those where it needs doing and just open them.

Again detect the scenario. Provide a report of affected clients, so as they are opened they drop off it. Block that functionality dependent on this data being correct until it is.

This is the not nice position, that you can end up with when separation of concerns is binned in favour of "this will be cheaper". You have my sympathy, my own specimen of this T-shirt is food spotted and badly worn.