Functions getting interupted and not completing their purpose.

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.
BillxxxxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel PineaultPresident / Owner CARDA Consultants Inc.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 LLCCommented:
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 ProfessionalCommented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 EngineerCommented:
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....
BillxxxxAuthor 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.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?
BillxxxxAuthor 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.