lock records in subform - multi-user

[DJ]

My DB is in a multi-user environment. I have a form with a subform. On the
main form is an edit button. When a user clicks the edit button, I want to
disable editing of the main form for other users (no problem with this), but
also disable editing of the related records in the subform for all users. The
subform contains multiple records.

Ho would I best acheive this?

 

[strive4peace]

Hi DJ,

do you have a separate FE (Front-End) for each user with all the Access
objects except tables? Do you have a common BE (Back-End) with the
tables? If you are using a version of Access above Access 97, you should...

If that is the case, you can create a table in the BE that everybody is
linked to so you can pass this information to other FEs. Are you
wanting to lock just one main record and its related records? Please
tell us more and we can help you further.

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[DJ]

Hi Crystal,

Yes I do have a separate BE for the tables.

I have a table containing general location information and a further table
containing related but unique detail information (1 location, many detail)
The means of editing/adding detail information is through a subform on the
main form for the master record (not on a separate form)

When a user edits the main record (by clicking the edit button) I want to be
abe to programatically lock all the related records showing in the subform at
that time (if it's possible)

Thanks

DJ

 

[strive4peace]

Hi DJ,

I suggest two tables in the BE that all the FEs are linked to. One will
assign a numeric ID to all tables you will use for this feature
(allowing for expansion to other tables that may need this capability);
and the other will contain the primary key field that is being
edited, the table it is for, and the user who has it 'open'

since you did not give me specifics about your tables, I will call the
PK field 'SomeID', which corresponds to RecordID in usys_OpenRecords

when I set up multi-user databases, I use database properties in the FE
to store the UserID for that FE. Since I do not know how you are
tracking the FE's, I will assume you have something similar.

so, assuming this to be the structure of the common tables

usys_OpenRecords
- OpenRecordID, autonumber
- UserID, long -- identifies the user who has record open
- RecordID, long -- corresponds to PK of table being edited
- TID, long -- FK to usys_Tables

usys_Tables
- TID, long -- identifies each table
- Tblname, text -- name of table

any object that is prefaced with "Msys" is a Microsoft System object
any object that is prefaced with "Usys" is a User System object

You can choose to display or hide system objects.
from the menu --> Tools, Options...
and check or uncheck --> System Objects

you can probably use the form and subform Dirty event to 1. check to see
if the record is okay to edit possibly with a dLookup and, if not, give
the user a message and Cancel the Dirty event which will rollback
changes and 2. if the record is ok to edit, place a record into
usys_OpenRecords with the userID, someID, and TID using SQL to append a
record

On the form AfterUpdate event, you can delete the record from this
common table using SQL to delete a record

hopefully this gives you some ideas. Without more specifics for your
database, I cannot give more specific information.

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*