Button Macro stops responding after several clicks

[LEX]

I have a form that we use to add new sales lead information into a MS
access 2003 database. The database is stoed on a Windows 2003 Server
running Terminal Services. There are 10 users that remotely login and
all open the same database. All The new lead data is entered on to
one form and at the bottom is a button titled "Process new Lead's
information" Button. The button runs a macro that opens and
transferes data into 3 different tables (main office, individual
contact info, and notes table) depending on the information on the
form. There is no code in this process as all transfers use existing
tools available in the macro section of the program.

After processing a few records I have been experiencing some very
strange problems as follows:

1. sometimes the Button stops responding. Everything else in the
database still works but the button will not call up the macro and
process the data. The only fix we have figured out to date is to
close the entire database and reopen it. It will then work for 3-10
new leads and then stop again.

2. sometimes it will transfer the information into tables 1&2 but not
table 3. No error will show up at all it just does not transfer the
information. We have then been going in manually to the associted
tables and rentering the missing info.

3. sometimes after processing a few records a HAULT screen comes up
stating that the ACTION FAILS. The action that appears to be failing
is a simple openform command.

Any assistance or idea on one or all of the above problems would
greatly be appreciated.

Thanks in advance.

 

[IT-1957]

Hello, this may be happening becouse different users are trying to
change/update the same records at the same time, specially if all have the
same form and can call the same action, I'll suggest you put a lock on the
form that will prevent other users for running the command when is already
running.

Create a yes/no box on your form and call it "lock" (set the default value to false and no visible)
then use a condition on the macro, something like :If "myform.lock"= true

(action) msgbox: "Please wait,someone else is running this process"

If "myform.lock"= true (action) STOPMACRO
(action)setvalue ="myform.lock"= true

This will check if someone is already running the process, and if it is,
will send the message and stop the macro, if not will mark the process as
"running"
at the end of the macro set the If "myform.lock" back to false.

So this is not the actual code or anything is just the instruccions on what
to do.

I hope this help.

 

[NTC]

is your db split into FE and BE??

if not; you need to split and put an FE into each user's Term Server profile
- so they each use their own FE. Each FE must have the tables linked to
the BE.

 

[LEX]

Hello, this may be happening becouse different users are trying to
change/update the same records at the same time, specially if all have the
same form and can call the same action, I'll suggest you put a lock on the
form that will prevent other users for running the command when is already
running.


(action) msgbox: "Please wait,someone else is running this process"


This will check if someone is already running the process, and if it is,
will send the message and stop the macro, if not will mark the process as
"running"
at the end of the macro set the If "myform.lock" back to false.

So this is not the actual code or anything is just the instruccions on what
to do.

I hope this help.

--
IT1957











- Show quoted text -

So, two people can not run the same macro at the same time in access?
Well that sucks. Thanks, I will try your solution.

 

[LEX]

is your db split into FE and BE??

if not; you need to split and put an FE into each user's Term Server profile
- so they each use their own FE.    Each FE must have the tables linked to
the BE.











- Show quoted text -

So, If I have a local version of the forms and macros on the front end
on each TS profile, it will run that local macro which will not be
lock by someone else? But, this problem has also been known to happen
when only one person is actually in the database working by herself on
a weekend.

 

[NTC]

multiple users sharing the same db will lead to unpredictable and unusual
behavior...and so one will always look at that situation and recommend that
it be changed.

your description definitely falls in the category of unusual.

admittedly it is bad news to hear that definitely a single user with
absolutely no one else having opened the same db (and you are sure no one
left it open on a running PC).. so you maybe could have a design issue too;
but I wouldn't let that excuse/prevent you from getting the db split and
everyone with their own FE...as that situation is still a problem magnet.

but on the other hand if a single person triggered this problem - then it is
not a multi user record locking issue.

the macro may need to be trouble shot; since one person can cause the
problem you should be able to stress test it.....copy the db; on that test
copy run that macro repeatedly with a variety of entries and see if you can
make it not function; generally they will throw an error message if there
are no records to process or a temp table is blank; but those error messages
maybe surpressed. try to keep track of what sequence works the last
time......... but overall one can not really say casually. but it is
possible that depending on some sequence of data that the same macro will
stop working because of a logic error. For instance I have seen one that
included the delete of a temp table presuming that temp table would always be
rebuilt...but in rare circumstances that temp table did not get rebuilt due
to user info and so the macro couldn't function.

one nice thing about having users with their own FE is that when that macro
breaks (until you trouble shoot it) - it only breaks for that one person, not
everyone.

hope this helps a little...

 

[LEX]

multiple users sharing the same db will lead to unpredictable and unusual
behavior...and so one will always look at that situation and recommend that
it be changed.

your description definitely falls in the category of unusual.

admittedly it is bad news to hear that definitely a single user with
absolutely no one else having opened the same db (and you are sure no one
left it open on a running PC)..  so you maybe could have a design issuetoo;  
but I wouldn't let that excuse/prevent you from getting the db split and
everyone with their own FE...as that situation is still a problem magnet.

but on the other hand if a single person triggered this problem - then itis
not a multi user record locking issue.

the macro may need to be trouble shot;  since one person can cause the
problem you should be able to stress test it.....copy the db; on that test
copy run that macro repeatedly with a variety of entries and see if you can
make it not function;  generally they will throw an error message if there
are no records to process or a temp table is blank; but those error messages
maybe surpressed.  try to keep track of what sequence works the last
time......... but overall one can not really say casually.  but it is
possible that depending on some sequence of data that the same macro will
stop working because of a logic error.  For instance I have seen one that
included the delete of a temp table presuming that temp table would always be
rebuilt...but in rare circumstances that temp table did not get rebuilt due
to user info and so the macro couldn't function.

one nice thing about having users with their own FE is that when that macro
breaks (until you trouble shoot it) - it only breaks for that one person,not
everyone.  

hope this helps a little...






- Show quoted text -

I have confirmed that she was the only user at night and it appears
that three lead imports is the magic number when the macro stops. In
fact it always works for the first three she tells me and now she just
closes the entire database and reopens it after three times. Very
strange indeed. I have created a seperate front end for her this
morning and am testing that now. I'll post the results shortly.
Thanks

 

[Big John]

What's going on dudes? LEX

Hello, this may be happening becouse different users are trying to
change/update the same records at the same time, specially if all have the
same form and can call the same action, I'll suggest you put a lock on the
form that will prevent other users for running the command when is already
running.


(action) msgbox: "Please wait,someone else is running this process"


This will check if someone is already running the process, and if it is,
will send the message and stop the macro, if not will mark the process as
"running"
at the end of the macro set the If "myform.lock" back to false.

So this is not the actual code or anything is just the instruccions on
what
to do.

I hope this help.

--
IT1957











- Show quoted text -

So, two people can not run the same macro at the same time in access?
Well that sucks. Thanks, I will try your solution.

 

[NTC]

my guess; macros all function at the front end so it seems very low
probability that anything on the backend is the cause....I would focus on
trouble shooting that macro logic; or simply deleting the button itself; and
possibly also the macro too...and simply rebuilding them both from scratch;

see if the button has any vb code around the ONClick event that could be
applying any conditional need to fire...

a macro is a sequence of tasks...step thru each one manually without using
the button itself...check that each action does its task ok


or simply deleting the button itself; and possibly also the macro too...and
simply rebuilding them both from scratch;

frustrating but should be fixable....