How to prevent users opening a form that is already in use

[ArviLaanemets]

I've never heard of anyone using multiple BE's. How on earth would you sync them?

I have used them as data sources for different apps. E.g. a calendary DB, or the DB of all countries with country codes, names, etc.

I would not put queries in the back end as a general rule.

You will if you use SQL Server DB as BE. Honestly, MS Access SQL is a woefull thing really!

[Micron]

I have used them as data sources for different apps.

Then they''re not really back ends if you're not adding/editing data through the FE - they're libraries?

[orange]

Multiple BEs is/was quite common. Each BE had different data or data in a different status eg Prod/Maint/Test/Dev
I often had Access BE and 1 or more Oracle BE, even a MySQL BE.

Based on the OP's posts it seems to me he has misunderstood proper usage of individual copies of FE for each user that can talk to a common BE(s) - As ArviLaanemets points out in post #14.

[Micron]

Multiple BEs is/was quite common. Each BE had different data or data in a different status eg Prod/Maint/Test/Dev

Seems I need to clarify, but I thought syncing comment would have sufficed.
I meant multiple copies of the same tables used for appending/editing the same data sets. I guess that is not what was originally meant.

[Alocin]

Thank you everyone for all of your thoughts.
Just FYI and I am not trying to be funny but we have a single FE for all users to use.
We have been using this only one FE shared on a network drive for a little over 20 years with I guess much luck.
We have not had any problems. Thank you for the concerns but this has been my experience.

So to be sure I have answered the question: "Each user has their own copy of the FE on their individual PC as CJ has mentioned in #10"? The answer is no. We only have a single FE for all users to share.
The next information I want to clear up is that the five BEs DON'T have ANY queries or forms or reports or modules or etc. These BEs only have tables that the single FE is linked to for its use. Also, I have been using "Record Lock" in "Edited Record" mode with page locking. I guess it has prevented disastrous corruptions.

Please note that I am asking for your support on whether there is a way to prevent another user opening a form that is already opened by someone else. Anyhow, if anyone can suggest a VBA script to detect an open form PLEASE let me know.

Thank you all so very much for all the support thus far.

Ciao!
Alocin

[davegri]

I think the 2nd paragraph in post #10 is germane. It depends on how your users open the form where the code should be located.

[Micron]

In my experience with single shared fe, each person creates an instance of a form when they open it with their own copy of Access, so no, there is no way to detect that anyone else has a form open. These instances have no awareness of each other. IMO the issue isn't really whether or not someone has the same form open. The issue is whether or not 2 simultaneously opened identical forms load the same records and edit the same set/page of records and whether or not record locking comes in to play

[ArviLaanemets]

In addition to Micron's last post, when an user opens your FE, then really it creates the copy of this FE in memory of is/her computer (what we call the instance), or in memory of Terminal Server in case the user opened the FE from there. All the work is done in user's computer. And when another user opens the same FE, another instance of this FE is created in memory of another computer. The only changes happening with FE itself is that the lock file is created in same directory (I haven't a clue what hapens with lock file when several users open the same FE though).

As follows, to get any info which forms are open in another user's instance of same FE is to somehow have access to computer memory of all other users, and somehow to read this info from there!

[CJ_London]

The only changes happening with FE itself is that the lock file is created in same directory

not quite true, when you open the FE, the FE file is updated. To demonstrate, using explorer, navigate to a .accdb. Note the date modified value. Open the file - the date modified will change to the current time. May take a few seconds to update the time - will be faster if an object (form/report/etc) is opened.

With regards the OP's situation, suspect just luck, over the years I've had 3 clients who shared the FE and came to me when it became corrupted. One used the same file for development as well and had no backups.

And with regards the OP's question, the only way I can see it could be done would be to have a 'flag' table in the BE that is checked before opening the form, if the form is not flagged, add a flag and open the form. Then remove the flag when the form is closed. Flag table might contain form name, username and perhaps a timestamp.

you could use dcount("8","tblFlags","formname='" & nameOfFormToOpen & "'")=0 to check if form is available

[Welshgasman]

@CJ_London

Typo there I think? and for the benefit of the O/P

dcount("*","tblFlags","formname='" & nameOfFormToOpen & "'")=0