Display Warning if Record Locking

[Peter Hibbs]

I am writing a bog standard A2003 FE database for a small insurance
broker that have 7 PCs linked to a BE file on a Server PC.

The main form is bound to tblClients with a subform bound to
tblPolicies. I'm told by the user that it is quite likely that two (or
more) users could try and amend the same policy record at the same
time. For this reason I have set the Record Locks property on both
forms to 'Edited Record' so that once a user starts to amend a record,
any other users cannot do so, (I don't see any merit in leaving the
property value as 'No Locks' as the second user could spend several
minutes changing the data in a number of fields and then, when they
exit the record, be told they can't save those changes or if they do,
it will overwrite someone else's amendments).

The system works OK except that it is decidely user unfriendly. If the
first user opens a record and then changes the data in a field it
'dirties' the record. If the second user opens the same record and
tries to amend a field the change is not accepted (although,
bizarrely, it also 'dirties' the record) and it appears to the second
user that something has gone wrong with the database.

What I would like to happen is that when the second user opens a
record that has been 'dirtied' by another user, is to display a big
red label which says "Record in Use". It would also have to be
'dynamic' in that if the second user remains on the record and the
first user moves off the record, the label disappears to indicate to
the second user that the record is now available for use.

I don't see any property on a form which would indicate if a record
has been 'dirtied' or an event which triggers when it has been saved,
although Access itself must know these things to lock the fields to
the second user.

Does anyone know if it is possible to do what I want?

Peter Hibbs.

 

[Arvin Meyer [MVP]]

There is a Dirty property in a form, but unfortunately, it is the form on
the current desktop, not anything to do with record not yet written to the
table. Therein lies your problem. Although the engine can lock a record or
records, there currently is no way to inform the users. I doubt if there
ever will be a way with a JET engine, since it isn't a server based engine,
and the lock comes from a workstation. The only thing I can think of is to
change to a server based engine that supports this property.

 

[Peter Hibbs]

Arvin,

Thanks, I suspected as much. I suppose the users will just have to
live with it as it is.

Peter Hibbs.

 

[Peter Hibbs]

Chris,

Sorry but that is not what I see. The first user gets a Pencil image
in the Record Selector bar when he dirties the record and the second
user gets nothing (just the right arrow symbol that is) and he cannot
change the data in any field on the record.

Have I missed something here. Admitted I am testing this with two
instances of the database on the same PC but I think that it would be
the same for two separate PCs linked to the same BE.

Peter Hibbs.

 

[Peter Hibbs]

Chris,

I didn't but I have done now and it makes no difference.

Peter Hibbs.

 

[a a r o n . k e m p f]

Jesus christ dude

maybe, instead of reinventing the wheel-- maybe you should just move
to a reliable database.

Jet has random locking problems, and only through getting rid of Jet
are you going to have a more reliable solution.

Move to Access Data Projects-- this will allow you to keep your
existing forms / reports-- and will allow you to grow your database
into the worlds most popular database (SQL Server).

Jet is dead, and it has been for a decade.
Sorry, but the people around here are too old and retarded to know
otherwise.

Do you want to get stuck making $12/hour for the rest of your life?
It's time to lose the training wheels, kids!

-Aaron

 

[Peter Hibbs]

Chris,

I see what you are saying now. I have done some more tests and you are
correct for the main form, if I activate the Record Selector on the
main form it does show the circle symbol on the second user's form
when it is being edited by the first user.

However, this facility does not seem to apply to the subform, i.e. the
circle symbol does not appear when a subform record is being edited.

My question now is - is this how it is supposed to work or is there
something wrong with my mainform-subform design. I think that is
unlikely, however, it is a standard design, the forms are linked
correctly on the ClientID field. Perhaps tomorrow I will try this
system out on a Northwind database to see if that does the same,
unless you have any further insight to the problem.

Peter Hibbs.

 

[Peter Hibbs]

Chris,

OK, that is slightly odd. When I do the same thing on my subform I do
get the (tone tone) beep when the second user attempts to change a
field but only after about a 2 second delay and the Record Selector
does NOT change to the "No" symbol.

Another factor which may or may not be relevant is that there are
actually three subforms on the main form, all linked to the same table
(via a queries). I will investigate further tomorrow.

Watch this space!

Peter Hibbs.

 

[Peter Hibbs]

Chris,

I am actually testing this by opening two instances of the database on
the same computer and the BE is also on the same computer so network
delays do not come into it.

However, your reference to the query prompted me to look at the
subform query design again and it seems that this is in error. The
query for the subform is actually bound to two tables the policies
table itself and another table which holds details of claims on the
policy. I removed the link to the Claim table (I was only using one
field from it anyway) and the problem seems to have gone away. As you
mentioned earlier, the "No" symbol does now appear on the subform
Record Selector button and the response time is now negligible.

Having said that, the system is not perfect. For example, if the first
user dirties the record and the second user opens it he sees the "No"
symbol. If the first user then closes the record the symbol on the
second user's form does not seem to change so that he does not see
that the first user has finished. I would be interested to know if
that is your experience as well, if so then I think the user could
live with that.

Also, I am not too keen on having Record Selectors visible on Single
type forms, it looks a bit messy. It would be nice to be able to
detect this situation and show a warning message when a record is in
use as I originally mentioned (but perhaps that is asking too much).

Anyway, thanks for your help, this has been a useful exercise for me.

Peter Hibbs.

 

[Peter Hibbs]

Chris,

Yes, you are correct, I was being a bit impatient. It looks like I
can't really do exactly what I wanted but the user will probably be
happy with the system as it is, now that I have it working properly.

Thanks again for your help.

Peter Hibbs.

 

[George]

Enough is enough. It's bad enough to have Aaron's rantings here. Why add to
the degradation?

George


in message news:[email protected]...

 

[Howard Burgman]

"Ted Kozlowski" wrote in message news:[email protected]...