Is Form Open?

[Guest]

I have a networked DB that has multiple people accessing it. I have a form I
only want opened by one user at a time.

I tried to use the code found at:

http://www.mvps.org/access/forms/frm0002.htm

to do this, but it only seems to work if I try to open this form a 2nd time
from the same instance of Access. If I open the DB from a 2nd instance of
Access and open my form while the form is open in my 1st instance of Access,
it reads as if the form is not already open.

Can someone help with this?

Much thanks,
Clint

 

[Dirk Goldgar]

I have a networked DB that has multiple people accessing it. I have a
form I only want opened by one user at a time.

I tried to use the code found at:

http://www.mvps.org/access/forms/frm0002.htm

to do this, but it only seems to work if I try to open this form a
2nd time from the same instance of Access. If I open the DB from a
2nd instance of Access and open my form while the form is open in my
1st instance of Access, it reads as if the form is not already open.

It's not really a good idea to have a single.mdb file, containing both
data (tables) and user-interface elements such as forms and reports,
opened across the network by multiple people. It can work, but it's
prone to corruption and locking issues. The standard way is to split
your database into a "back-end" .mdb file containing only the tables,
and a "front-end" containing all the queries, forms, reports, and code.
For data, the front-end contains *linked* tables, linked to the
back-end. Then the back-end is placed on the networked server, while
each user gets his own copy of the front-end, stored on his own PC.

If you do it this way, do you still need to have your form opened only
by one user at a time? If so, you may be able to set up the form so
that it locks the table upon opening and denies even read permission to
anyone else, but I think that will take a bit of fancy code. (I could
be mistaken, and maybe you can do what you want with the Record Locks
property of the form.) Or you could update a record in some table when
the form is opened, and have code in the form's Open event that checks
that table to see if it's okay to open, cancelling the event if it
isn't.

What's going on in this form, if I may ask, that it must only be opened
by one user at a time? Maybe there's some other way to accomplish your
goal.

 

[Guest]

Thanks much for your reply.

I do have the DB split. The need to have the form open by one user at a time
is due to a design issue that cannot be rectified at the time. There just
isn't enough time right now to make the needed changes.

There is temp data stored in tables that is accessed, processed and deleted
when a user clicks a button. The temp data is added by the user on the form.
Specifically, there are 4 tables linked to this form, 1 on each of 4 tabs.

The main problem is that if more than 1 user is on this form, entering in
data in the subforms on my tabs and wanting to process the data, all data is
processed when the user clicks the button, no matter who has entered in the
records. Also, part of the processing is to empty the tables

As you can see, this is not good. Each user should be able to process their
own records while leaving records for any other user intact. Ha! I have a
couple of ideas as to how to rectify this, but I don't have the time. I
figured if I could limit access to 1 user at a time, this would be a good
temporary solution.

I do have an idea on how to limit access by using a login table, but the
process is more than I want to do if there is a real simple solution, like
similar to the code I referenced above.

 

[Dirk Goldgar]

Thanks much for your reply.

I do have the DB split. The need to have the form open by one user at
a time is due to a design issue that cannot be rectified at the time.
There just isn't enough time right now to make the needed changes.

There is temp data stored in tables that is accessed, processed and
deleted when a user clicks a button. The temp data is added by the
user on the form. Specifically, there are 4 tables linked to this
form, 1 on each of 4 tabs.

The main problem is that if more than 1 user is on this form,
entering in data in the subforms on my tabs and wanting to process
the data, all data is processed when the user clicks the button, no
matter who has entered in the records. Also, part of the processing
is to empty the tables

As you can see, this is not good. Each user should be able to process
their own records while leaving records for any other user intact.
Ha! I have a couple of ideas as to how to rectify this, but I don't
have the time. I figured if I could limit access to 1 user at a time,
this would be a good temporary solution.

I do have an idea on how to limit access by using a login table, but
the process is more than I want to do if there is a real simple
solution, like similar to the code I referenced above.

How about putting your temp tables in the front-end, so each user has
his own copy? Then each user's work in the form is restricted to only
his own set of records? Or, to avoid bloating the front-end, you could
create a temp database (for each user) on the fly, when the form is
opened, and then delete the temp database when the form closes.

 

[Guest]

Hummm, I like the temp DB idea. Don't think I ever thought of that. I'll have
to check into this. I've never created a DB from code and I don't think I've
ever referenced tables from a different DB from code.

Since the front end is networked, all users are accessing same DB file, so
non-linked tables will not work. There are too many users to give each a
separate FE, but again, the temp DB creation thing just might work.

Thanks for the suggestion!
Clint

 

[Dirk Goldgar]

Hummm, I like the temp DB idea. Don't think I ever thought of that.
I'll have to check into this. I've never created a DB from code and I
don't think I've ever referenced tables from a different DB from code.

Tony Toews has a page, on the subject with a link to an MDB example
here:

http://www.granite.ab.ca/access/temptables.htm

I have a fairly simple class module that handles this for me.

Since the front end is networked, all users are accessing same DB
file, so non-linked tables will not work. There are too many users to
give each a separate FE, but again, the temp DB creation thing just
might work.

I'll just warn you again that the front-end should not be networked. If
you insist on doing it that way anyway, on your own head be it.