Functions getting interupted and not completing their purpose.

Billxxxx
Billxxxx used Ask the Experts™
on
I have a problem that has been Happening for years. It certainly
not alarming but I just want to clean up our program.

In a part that deals with a/r and check amounts I am running many
functions and I believe that one or more of them is getting interrupted
and thus produces incorrect results. This program is networked and
approximately used by 10 people at a time. Because there are many data
bases that are modified by these functions I was wondering if someone else could
unknowingly  be interrupting the functions?

I know that Access has a function to deal with this but I can not remember is name.
I am hoping some one could give me some guidance on a solution.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
I'm afraid we'd need a lot more information about the setup, your code, ... to be able to help.

Is there any difference in results if you do it at a time when no users are in the system?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Why do you "believe that one or more of them is getting interrupted"

Are you getting error messages when they run?

Do they write to tables which might be locked by other users at the time they are run?

Do the values they save or return differ given the same input parameters at different times of the day?

Do they have error handlers in the functions?  If not, you should, and it should write data to a table (tbl_Error_Log).
I would recommend that the error handler should write, as a minimum, the name of the procedure being run, the time the error occurred, the err.Number and err.Description, and the values of the parameters passed to the procedure (probably in a comments field).

This would allow you to pin-point where errors are occurring, how frequently, and would allow you to test those procedures with the precise input parameters that raised the error.
Hamed NasrRetired IT Professional

Commented:
I know that Access has a function to deal with this
What I think in is the error handlers, as Dale suggested, although interruption may occur apart from errors.  Also you need to log specific values at entry and exit points of a function.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Top Expert 2014

Commented:
Anytime you have functions or processes that must be completed as a unit, you should consider using transactions to ensure that all those processes complete, or they all fail. Access can work with Transactions through the DAO or ADO engines, but you have to be careful how these are done, and you also have to ensure these are extremely long running processes since it will lock the database during those times.

Here's one tutorial on the process:

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=how-to-access-transaction

There are plenty of others out there.
John TsioumprisSoftware & Systems Engineer

Commented:
I think the 1st thing to do is to get familiarized with your function what they do..what records they modify ..when..how..criteria...what manipulation it applies to the records..just write down the complete "workflow" to a Document...i prefer Excel coloring the Cells to show the progress...of course there are better alternatives..but.
Error Handling while utilizes the "forbidden" Goto will give a good insight when something happens..if they are nested then you probably you have to set some flags to check the actual stage of the whole process.
Last but not least..just log everything...pick a subset of code that does a specific subwork and log it....

Author

Commented:
First of all I would like to thank everyone for their ideas and efforts.
I probable should have explained more but didn't think and hoped
there was something that would have just jumped out. Please understand
that this part of our program I wrote probably 20 years ago. The problem I
know showed up a few years after.

This function deals with our accounts receivables and occurs when
we are applying payments that we receive from customers.  The process starts
from our aging report and when you want to apply a payments for a particular
customer you simply select a customer and all open invoices will then appear.
You then select the invoices being paid. a number of things then happens,
The total amount noted on check must match the total of the invoices being
paid. If it doesn't warnings pop up and tells you exactly what may had been entered
incorrectly and gives you the opportunity to make corrections.  Once everything matches
it reduces the amount owed by the amount paid and writes this to a specific table.
it also writes to another table as a single payment and amount. It will also write
to our work order table that reduces the value of the related work order and then
opens another table that prepares the actual daily deposit that we provide to the bank.
All of these trans actions are recorder with a unique ID etc.

Every now and then something is left out or recorded incorrectly. The total of payments
does not match. A balance in the customer A/R does not reflect the correct amount etc. What's
very strange is that everything usually will work as planned except every now and then
it doesn't.  When this happens I keep saying I will get around to fixing it. I've been saying that
for 20 plus years. Since we are the only company that uses this program and everything can
be traced and then corrected it has no real material effect.

Over the years I have added and removed code to make things work better and be more
efficient and added new features. I know I could re right the entire module and drastically
improve everything. I don't want to do that because I know it's a simple problem with a
simple solution.  At least I think.

Thanks
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Well, I think for starters, the previous comment regarding implementing transactions is very applicable to such a process flow comprised of multiple updates.

An error handler should be part any VBA procedure.  It is even better to log such information to a table so you can review them easily and not have to rely on end-users making you aware of issues.

I also believe that perhaps you might be having issues with locks should a user being editing a records relating to a client as you try and push your update.

Over the years, have you seen any patterns to the issue?  Is it always the same table entries that are erroneous?  Anything standout?

Author

Commented:
I have never seen a pattern other then some one coming in my office and telling me it's happened again.

I have put in error handling in numerous places and never received any error notice. Maybe the problem
is that i never left it in for more then a month. I learnt my lesson a long time ago about leaving unused forms
and poorly documented code within applications. Years later you wonder what you were trying to do.

I think what I should do is re-right all the error handlers and just leave it there forever and wait again.

It could be a locking issues. I suppose that never thinking of it because it never happened is probably not a good reason
for not checking it out.  Just thinking about controlling the lock and unlock function through code brings up a lot of
interesting questions and potential problems.

Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial