Locked Form

I’m having frequent difficulty in getting locked out of a form. I’m new to
multiple user applications. I thought we were using Record-level locking, and
so would get locked out only if the same record were being edited at the same
time by another user. With a 15-person office, that seems unlikely.

In addition to this Timesheet application, we have another application
(which we use much more frequently) that uses a couple of the same
tables—Staff and Projects. To avoid either duplicating the effort for these
two tables or combining the two applications into one, I chose to create a
second backend file, Common.mdb, where the shared tables are stored once.
These are then linked into the front-end file. I understand that this means
the ability to enforce referential integrity is thus lost because I can’t
establish any relationships in the backend.

This may be a part of the explanation of why we get locked out of the form
when anyone has the 2nd application opened. I would appreciate any help on
record-locking in general, how to correct the current situation, and whether
it might make sense to combine these two applications into one.

Thank you.
Sprinks

Tables:

Staff
StaffID AutoNumber (PK)
FName Text
LName Text
…

TSHistory (One side of relationship w/TimeRecords)
TSID AutoNumber (PK)
PeriodBeginning Date
StaffID Integer (FK to Staff)


TimeRecords (Many side of relationship w/TSHistory)
TimeRecordID AutoNumber (PK)
TSID Integer (PK to TSHistory)
ProjectNumber Text (FK to Projects)
PeriodDay Integer (1-14)
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date

Projects
Project Text (PK)
ProjectName Text
…

Main Form RecordSource:
SELECT Staff.FName & " " & Staff.LName AS StaffName, TSHistory.TSID,
TSHistory.PeriodBeginning, Staff.StaffID
FROM TSHistory INNER JOIN Staff ON TSHistory.StaffID = Staff.StaffID
WHERE
(((TSHistory.PeriodBeginning)=DateValue([Forms]![EditTimeRecordsInit]![cboPeriodBeginning]))
AND ((TSHistory.StaffID)=[Forms]![EditTimeRecordsInit]![cboStaffID]));

Subform RecordSource
SELECT TimeRecords.PeriodDay, TimeRecords.TSID, TimeRecords.ProjectNumber,
TimeRecords.Hours, Projects.Project, TimeRecords.StatusID
FROM TimeRecords INNER JOIN Projects ON TimeRecords.ProjectNumber =
Projects.ProjectNumber
ORDER BY Left(Projects.ProjectNumber,1), TimeRecords.ProjectNumber,
TimeRecords.PeriodDay;

Subform Property:
LinkMasterFields = LinkChildFields = TSID

Hopefully you have a copy of the front end on each user's desktop and the
back end on a shared folder.

In Tools, Options, Advanced:
Default Open Mode - Shared

Default Record Locking - No Locks

Open databases using record level locking - Checked.

"Sprinks" wrote:

I’m having frequent difficulty in getting locked out of a form. I’m new to
multiple user applications. I thought we were using Record-level locking, and
so would get locked out only if the same record were being edited at the same
time by another user. With a 15-person office, that seems unlikely.

In addition to this Timesheet application, we have another application
(which we use much more frequently) that uses a couple of the same
tables—Staff and Projects. To avoid either duplicating the effort for these
two tables or combining the two applications into one, I chose to create a
second backend file, Common.mdb, where the shared tables are stored once.
These are then linked into the front-end file. I understand that this means
the ability to enforce referential integrity is thus lost because I can’t
establish any relationships in the backend.

This may be a part of the explanation of why we get locked out of the form
when anyone has the 2nd application opened. I would appreciate any help on
record-locking in general, how to correct the current situation, and whether
it might make sense to combine these two applications into one.

Thank you.
Sprinks

Tables:

Staff
StaffID AutoNumber (PK)
FName Text
LName Text
…

TSHistory (One side of relationship w/TimeRecords)
TSID AutoNumber (PK)
PeriodBeginning Date
StaffID Integer (FK to Staff)


TimeRecords (Many side of relationship w/TSHistory)
TimeRecordID AutoNumber (PK)
TSID Integer (PK to TSHistory)
ProjectNumber Text (FK to Projects)
PeriodDay Integer (1-14)
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date

Projects
Project Text (PK)
ProjectName Text
…

Main Form RecordSource:
SELECT Staff.FName & " " & Staff.LName AS StaffName, TSHistory.TSID,
TSHistory.PeriodBeginning, Staff.StaffID
FROM TSHistory INNER JOIN Staff ON TSHistory.StaffID = Staff.StaffID
WHERE
(((TSHistory.PeriodBeginning)=DateValue([Forms]![EditTimeRecordsInit]![cboPeriodBeginning]))
AND ((TSHistory.StaffID)=[Forms]![EditTimeRecordsInit]![cboStaffID]));

Subform RecordSource
SELECT TimeRecords.PeriodDay, TimeRecords.TSID, TimeRecords.ProjectNumber,
TimeRecords.Hours, Projects.Project, TimeRecords.StatusID
FROM TimeRecords INNER JOIN Projects ON TimeRecords.ProjectNumber =
Projects.ProjectNumber
ORDER BY Left(Projects.ProjectNumber,1), TimeRecords.ProjectNumber,
TimeRecords.PeriodDay;

Subform Property:
LinkMasterFields = LinkChildFields = TSID

Klatuu,

Yes, the front-end is on each user's local drive. My current settings a

Default Open Mode - Shared
Default Record Locking - Edited Record
Open databases using record level locking - Checked

Can you tell me how the behavior will differ by changing the Default Record
Locking?

Thank you.

Sprinks

"Klatuu" wrote:

Hopefully you have a copy of the front end on each user's desktop and the
back end on a shared folder.

In Tools, Options, Advanced:
Default Open Mode - Shared

Default Record Locking - No Locks

Open databases using record level locking - Checked.

"Sprinks" wrote:

I’m having frequent difficulty in getting locked out of a form. I’m new to
multiple user applications. I thought we were using Record-level locking, and
so would get locked out only if the same record were being edited at the same
time by another user. With a 15-person office, that seems unlikely.

In addition to this Timesheet application, we have another application
(which we use much more frequently) that uses a couple of the same
tables—Staff and Projects. To avoid either duplicating the effort for these
two tables or combining the two applications into one, I chose to create a
second backend file, Common.mdb, where the shared tables are stored once.
These are then linked into the front-end file. I understand that this means
the ability to enforce referential integrity is thus lost because I can’t
establish any relationships in the backend.

This may be a part of the explanation of why we get locked out of the form
when anyone has the 2nd application opened. I would appreciate any help on
record-locking in general, how to correct the current situation, and whether
it might make sense to combine these two applications into one.

Thank you.
Sprinks

Tables:

Staff
StaffID AutoNumber (PK)
FName Text
LName Text
…

TSHistory (One side of relationship w/TimeRecords)
TSID AutoNumber (PK)
PeriodBeginning Date
StaffID Integer (FK to Staff)


TimeRecords (Many side of relationship w/TSHistory)
TimeRecordID AutoNumber (PK)
TSID Integer (PK to TSHistory)
ProjectNumber Text (FK to Projects)
PeriodDay Integer (1-14)
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date

Projects
Project Text (PK)
ProjectName Text
…

Main Form RecordSource:
SELECT Staff.FName & " " & Staff.LName AS StaffName, TSHistory.TSID,
TSHistory.PeriodBeginning, Staff.StaffID
FROM TSHistory INNER JOIN Staff ON TSHistory.StaffID = Staff.StaffID
WHERE
(((TSHistory.PeriodBeginning)=DateValue([Forms]![EditTimeRecordsInit]![cboPeriodBeginning]))
AND ((TSHistory.StaffID)=[Forms]![EditTimeRecordsInit]![cboStaffID]));

Subform RecordSource
SELECT TimeRecords.PeriodDay, TimeRecords.TSID, TimeRecords.ProjectNumber,
TimeRecords.Hours, Projects.Project, TimeRecords.StatusID
FROM TimeRecords INNER JOIN Projects ON TimeRecords.ProjectNumber =
Projects.ProjectNumber
ORDER BY Left(Projects.ProjectNumber,1), TimeRecords.ProjectNumber,
TimeRecords.PeriodDay;

Subform Property:
LinkMasterFields = LinkChildFields = TSID

With Edited records, the record locks when a user begins editing a record.
If you change it to No Locks, the record does not lock until the user
attempts an update.

"Sprinks" wrote:

Klatuu,

Yes, the front-end is on each user's local drive. My current settings a

Default Open Mode - Shared
Default Record Locking - Edited Record
Open databases using record level locking - Checked

Can you tell me how the behavior will differ by changing the Default Record
Locking?

Thank you.

Sprinks

"Klatuu" wrote:

Hopefully you have a copy of the front end on each user's desktop and the
back end on a shared folder.

In Tools, Options, Advanced:
Default Open Mode - Shared

Default Record Locking - No Locks

Open databases using record level locking - Checked.

"Sprinks" wrote:

I’m having frequent difficulty in getting locked out of a form. I’m new to
multiple user applications. I thought we were using Record-level locking, and
so would get locked out only if the same record were being edited at the same
time by another user. With a 15-person office, that seems unlikely.

In addition to this Timesheet application, we have another application
(which we use much more frequently) that uses a couple of the same
tables—Staff and Projects. To avoid either duplicating the effort for these
two tables or combining the two applications into one, I chose to create a
second backend file, Common.mdb, where the shared tables are stored once.
These are then linked into the front-end file. I understand that this means
the ability to enforce referential integrity is thus lost because I can’t
establish any relationships in the backend.

This may be a part of the explanation of why we get locked out of the form
when anyone has the 2nd application opened. I would appreciate any help on
record-locking in general, how to correct the current situation, and whether
it might make sense to combine these two applications into one.

Thank you.
Sprinks

Tables:

Staff
StaffID AutoNumber (PK)
FName Text
LName Text
…

TSHistory (One side of relationship w/TimeRecords)
TSID AutoNumber (PK)
PeriodBeginning Date
StaffID Integer (FK to Staff)


TimeRecords (Many side of relationship w/TSHistory)
TimeRecordID AutoNumber (PK)
TSID Integer (PK to TSHistory)
ProjectNumber Text (FK to Projects)
PeriodDay Integer (1-14)
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date

Projects
Project Text (PK)
ProjectName Text
…

Main Form RecordSource:
SELECT Staff.FName & " " & Staff.LName AS StaffName, TSHistory.TSID,
TSHistory.PeriodBeginning, Staff.StaffID
FROM TSHistory INNER JOIN Staff ON TSHistory.StaffID = Staff.StaffID
WHERE
(((TSHistory.PeriodBeginning)=DateValue([Forms]![EditTimeRecordsInit]![cboPeriodBeginning]))
AND ((TSHistory.StaffID)=[Forms]![EditTimeRecordsInit]![cboStaffID]));

Subform RecordSource
SELECT TimeRecords.PeriodDay, TimeRecords.TSID, TimeRecords.ProjectNumber,
TimeRecords.Hours, Projects.Project, TimeRecords.StatusID
FROM TimeRecords INNER JOIN Projects ON TimeRecords.ProjectNumber =
Projects.ProjectNumber
ORDER BY Left(Projects.ProjectNumber,1), TimeRecords.ProjectNumber,
TimeRecords.PeriodDay;

Subform Property:
LinkMasterFields = LinkChildFields = TSID

Thanks, Klatuu. And I found out this morning that that was the issue. All
the other users had the default configuration of No Locks, while I'd changed
mine to Edited Record. After changing it this morning, the conflict is gone.

Thanks.
Sprinks

"Klatuu" wrote:

With Edited records, the record locks when a user begins editing a record.
If you change it to No Locks, the record does not lock until the user
attempts an update.

"Sprinks" wrote:

Klatuu,

Yes, the front-end is on each user's local drive. My current settings a

Default Open Mode - Shared
Default Record Locking - Edited Record
Open databases using record level locking - Checked

Can you tell me how the behavior will differ by changing the Default Record
Locking?

Thank you.

Sprinks

"Klatuu" wrote:

Hopefully you have a copy of the front end on each user's desktop and the
back end on a shared folder.

In Tools, Options, Advanced:
Default Open Mode - Shared

Default Record Locking - No Locks

Open databases using record level locking - Checked.

"Sprinks" wrote:

I’m having frequent difficulty in getting locked out of a form. I’m new to
multiple user applications. I thought we were using Record-level locking, and
so would get locked out only if the same record were being edited at the same
time by another user. With a 15-person office, that seems unlikely.

In addition to this Timesheet application, we have another application
(which we use much more frequently) that uses a couple of the same
tables—Staff and Projects. To avoid either duplicating the effort for these
two tables or combining the two applications into one, I chose to create a
second backend file, Common.mdb, where the shared tables are stored once.
These are then linked into the front-end file. I understand that this means
the ability to enforce referential integrity is thus lost because I can’t
establish any relationships in the backend.

This may be a part of the explanation of why we get locked out of the form
when anyone has the 2nd application opened. I would appreciate any help on
record-locking in general, how to correct the current situation, and whether
it might make sense to combine these two applications into one.

Thank you.
Sprinks

Tables:

Staff
StaffID AutoNumber (PK)
FName Text
LName Text
…

TSHistory (One side of relationship w/TimeRecords)
TSID AutoNumber (PK)
PeriodBeginning Date
StaffID Integer (FK to Staff)


TimeRecords (Many side of relationship w/TSHistory)
TimeRecordID AutoNumber (PK)
TSID Integer (PK to TSHistory)
ProjectNumber Text (FK to Projects)
PeriodDay Integer (1-14)
Hours Integer
StatusID Integer (FK to Status)
InvoiceDate Date

Projects
Project Text (PK)
ProjectName Text
…

Main Form RecordSource:
SELECT Staff.FName & " " & Staff.LName AS StaffName, TSHistory.TSID,
TSHistory.PeriodBeginning, Staff.StaffID
FROM TSHistory INNER JOIN Staff ON TSHistory.StaffID = Staff.StaffID
WHERE
(((TSHistory.PeriodBeginning)=DateValue([Forms]![EditTimeRecordsInit]![cboPeriodBeginning]))
AND ((TSHistory.StaffID)=[Forms]![EditTimeRecordsInit]![cboStaffID]));

Subform RecordSource
SELECT TimeRecords.PeriodDay, TimeRecords.TSID, TimeRecords.ProjectNumber,
TimeRecords.Hours, Projects.Project, TimeRecords.StatusID
FROM TimeRecords INNER JOIN Projects ON TimeRecords.ProjectNumber =
Projects.ProjectNumber
ORDER BY Left(Projects.ProjectNumber,1), TimeRecords.ProjectNumber,
TimeRecords.PeriodDay;

Subform Property:
LinkMasterFields = LinkChildFields = TSID