Help with Locking a Record

[hawk_2001 via AccessMonster.com]

I have a DB that utilizes a "temporary table" environment. A user will open
a form with the specified information, and that record will immediately be
written to a temporary table. On exit, the user is prompted whether or not
they want tosave the record. If they say yes, the corresponding "main table"
record is updated with the information form the temp table. If the user
selects no, the temp table record is deleted and the main table record
remains unchanged.

Here is my dilemma :

Because this DB is in a multiuser environment, I want some sort of msgbox to
promt a user if they attempt to open a record that is open by another user.

For example, User A opens record 1. 2 mins later, User B attempts to open
record 1. I want User B to get a MsgBox that says "Cannot open record. It is
currently being edited. Please try later."

I have the forms "Record Locks" property set to "Edited Record", which
effectively prevents User B from editing a record currently in use. But,
since I am using temporary tables with append and delete queries, User B will
get various "cannot execute action query" messages, which I do not want. I
have tried error trapping, turning off warnings, etc., but to no avail

Can someone help me figure out a way to accomplish this?

Thanks in advance.

 

[Mark A. Sam]

Hawk,

You state that is the user does not save the record, that the temp record is
deleted, insinuating that it is not deleted if the record is saved. If you
are using the temp table as an editing tablet, it seems to me that you
should delete it whether changes are saved or not. You wouldn't need it any
longer. If the record was deleted in either case you could work out a
solution to the problem. In that case, if one user were addrsssing a record
on the main the temp record would be created. If a second user tried access
the same record on the main table, he could be given a message to that
effect and the temp table record be denied him. This is the idea, without
working out the exact procedure. If it were me doing this, I would have a
form for editing the temp table record, with another form for the main
table. The main table form would not allow editing, so the second user
could be allowed to stay on it or even tanken off of it. I'd use the
current event to address the issue. That is how I would approach it.
Working out the details and tweaking is another matter.

I hope that makes sense.

God Bless,

Mark A. Sam