Can't edit SOME records on a form

[Donna Thornhill]

Form is based on query where teacher inputs term,
teacherID, and class period. Button1 on form runs append
query that finds students enrolled in class and creates
attendance record for that date based on info displayed on
open form. Button2 on form displays second form with
attendance records just created so teacher can mark
absences, tardies, etc.

Some records can be edited but at times others can't. I
can't find a pattern as to when or why records are
locked. Doesn't always happen. Teacher can always go
back in later and edit the records but might have to try
several times before records are released.

Is this bad design on my part? Have moved db to server
with oplocks disabled and problem still occurs. This is
driving all of us crazy! Thanks!

 

[MarionB]

If I am understanding your e-mail. Button 2 opens the a
form where you want to add records that are related to
the main form. If this is the case add saverecord to the
code for button 2 so that it saves the record you just
entered before you tie the new record to it.

 

[Donna Thornhill]

I don't want to add records in the form that button2
brings up. I just want to be able to edit the records,
i.e. change the "P" for Present to an "A" for Absent.
Most of the time the records can be edited, but at some
times, 1 or 2 records on the same form can't be edited
while all the others can. Makes no sense to me.

 

[Dirk Goldgar]

This is Access 2000 on WIN2K workstations with WIN2K
server and multiple users. There are about 20 teachers
who should be logging attendance at approximately the same
time (first 15 minutes of class), but each creates and
views only their own class attendance. Theoretically, no
two teachers should be trying to edit the same attendance
record at the same time because there's a separate record
for each class. The only thing I can think of might be
the counselors running reports for their assigned sutdents
against that table at the same time the teachers are
trying to log attendance. That might explain why some
records on the form are locked since only a few students
in each class would be assigned to any one counselor.

The Record Locks on the form is set to "edited record."
On the "Advanced" tab, I have the Default Open Mode set to
Shared; the Default Record Locking set to No Locks; and
the Open databases using record-level locking is not
checked. Should this be checked? And isn't this is a
workstation setting rather than a database setting? (Did
I mention that I'm not a programmer? Please continue
to "guess" on this! Your guesses are better than mine!

Donna -

I'm guessing that your problem is most likely due to page-level locking
being used. Even though there's a separate record for each class, if
two or more of these records happen to be stored on the same physical
page on disk, editing one of them will lock the page and prevent any of
the others from being edited. With the database open, click Tools ->
Options... and on the Event tab check that box labeled "Open databases
using row-level locking". I don't know whether you need to do this on
every user's workstation or not, but I suspect you do.

Is your database split into back-end and linked front-end, with the
back-end on the server and a copy of the front-end on each user's
workstation?

 

[Donna Thornhill]

I'll make sure that the record-level choice is checked on
my workstation and then look for the registry entry for
it. I think the IT guys can push that out to the user
workstations for me.

I haven't separated into BE and FE but am looking at that;
it does seem to be recommended. It's only 4 MB right now
but maybe size isn't a strong factor. I have 9 copies of
the db (one at each campus) so I'll need a good way to
keep the front ends current and manage the security.
Right now, IT has ScriptLogic plopping an icon on the
desktop for each user in the container which opens Access
on the C: drive and then the db with /wkgp to the .mdw on
the server. I guess we could still keep the .mdw file on
the server with the BE and use it that way. The only
disadvantage I can see is that the users would be limited
to a workstation where the FE is already set up, rather
than just any workstation. Also, in the other Access db's
we have which are split, it's the FE's that "grow" and
need frequent compacting. Not sure how that can be done
with the FE's on the workstations.

Thanks again for your input.

 

[Dirk Goldgar]

I'll make sure that the record-level choice is checked on
my workstation and then look for the registry entry for
it. I think the IT guys can push that out to the user
workstations for me.

Let's hope that solves your locking problem.

I haven't separated into BE and FE but am looking at that;
it does seem to be recommended. It's only 4 MB right now
but maybe size isn't a strong factor. I have 9 copies of
the db (one at each campus) so I'll need a good way to
keep the front ends current and manage the security.

If your network is fast and rock-solid, maybe you can get away with
running an unsplit database over the network. But really, you'll be
substantially less vulnerable to corruption if you split the DB. See
Tony Toews' page

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

for a discussion and lots of good links. Tony also has developed a free
tool for keeping distributed front-ends up to date (though I admit I
haven't yet had occasion to use it myself). You can get it here:

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

Right now, IT has ScriptLogic plopping an icon on the
desktop for each user in the container which opens Access
on the C: drive and then the db with /wkgp to the .mdw on
the server. I guess we could still keep the .mdw file on
the server with the BE and use it that way.

That's what I do, but you could also give each user a copy of the .mdw
file, so long as you don't plan to make frequent changes to it.

The only
disadvantage I can see is that the users would be limited
to a workstation where the FE is already set up, rather
than just any workstation. Also, in the other Access db's
we have which are split, it's the FE's that "grow" and
need frequent compacting. Not sure how that can be done
with the FE's on the workstations.

Access databases will grow with use, and do need to be compacted from
time to time. There are some things you can do in the database itself
to keep that growth to a minimum; for example, use an external,
temporary .mdb file to hold temp tables. You can let the FEs compact
themselves on close, or you can easily set up a warning message to the
user when the database reaches a certain size, instructing them to
compact, or with a bit more difficulty you can have the database
automatically compact itself, on close, when it reaches a certain size.
I don't really see this as a problem.

Best of luck with it. Let us know how it turns out.