Record Locks

[xPaul]

Hi all,

I have what I think is a very complicated problem.

I have a query, that pulls a set of values from a table. The query assigns a random number to all of those values, and each time it is refreshed the random number changes but not the value. The query only returns 2 results.

So if you can imagine, loading up the query it will return 2 values, if you close it and reopen it, it returns two different values.

That's all well and good in a single user environment. Though, I am currently in a multiuser environment.

These queries are are form based, if User A accesses Record 1, and starts editing it, and if User B somehow tried to access Record 1 (unlikely due to the random number thing) User B can't due to the form being locked.

I've tested it and you physically cannot type anything on User B's Form.

The Forms Record Locking property is set to Edited Record.

Is there anyway, that VBA can pick up the fact that, that specific record is locked?

What I am essentially trying to achive is:
User A loads up form, gets a random value.
User B loads up form, but gets same random value.
User A starts editing record.
User B tries to edit same record but can't due to record locking.

At the last point, I would like a message to pop up to User B, saying this record is locked, and thus cannot be editied. The VBA code will return them to the main menu, so that User B can reload the form, and get new random values.

Hope this make sense. Probably not.

Thanks

 

[xPaul]

Essentially this: http://www.access-programmers.co.uk/forums/showthread.php?t=209679

 

[gemma-the-husky]

1. why bother with record locking? Seriously, I have never needed record locking. I can only think it is necessary for very sensitive stuff, where you need to manage a transaction consisting of multiple writes, say

2. why allocate the random number? what does it do?

 

[xPaul]

The random number was my first step in trying to reduce the chance of two people accessing the same record. It also ensure's that I am being unbaised with the data that is being used.

I need the record locking in case two or more people access the same record. That way they know someone else is accessing it, and open the form again giving them a new set of values.

 

[gemma-the-husky]

access uses something called optimistic record locking, by default.

trust it. you do not need record locking.

you also do not need the random numbers.

the chance of 2 users editing the same record simultaneously is very slight, and even if they do, optimistic record locking will handle it.

seriously - just design a normalised system for one user to handle data. it will work for multiple users.

 

[maximark]

Gemma - by the time optimistic record locking takes effect - it is too late.
I have 10 sales agents calling numbers from a list. If two agents click on the same number at the same time (happens multiple times per 8 hour shift) the same call details screen opens on each of their pcs. fe/be environment. when they call the same client one will get through one will get a busy signal. Obviously the one with the busy signal will record that result and hit Save first. When the other agent is done with the call Sale or No Sale they try to save the result and get the error message from optimistic locking saying that record has been edited. NOT GOOD.

how can the first record clicked on be locked immediately when first selected?

I have been thinking of the random number thing too!

it seems the each users fe goes to the be and gets a copy of the db
I have them find the record clicked on and set a flag to inuse
it then saves/returns the value to the be
but it still allows the details screen to open on both machines thus leading to the optimistic locking error message.


mark

 

[spikepl]

@Maximark

It is extremely bad form to post the same subject all over - I have counted 3 so far. That is a complete waste of effort for us who volunteer our energy here. Don't do it.

 

[gemma-the-husky]

Gemma - by the time optimistic record locking takes effect - it is too late.
I have 10 sales agents calling numbers from a list. If two agents click on the same number at the same time (happens multiple times per 8 hour shift) the same call details screen opens on each of their pcs. fe/be environment. when they call the same client one will get through one will get a busy signal. Obviously the one with the busy signal will record that result and hit Save first. When the other agent is done with the call Sale or No Sale they try to save the result and get the error message from optimistic locking saying that record has been edited. NOT GOOD.

how can the first record clicked on be locked immediately when first selected?

I have been thinking of the random number thing too!

it seems the each users fe goes to the be and gets a copy of the db
I have them find the record clicked on and set a flag to inuse
it then saves/returns the value to the be
but it still allows the details screen to open on both machines thus leading to the optimistic locking error message.


mark

in that case, you could certainly consider record locking.

alternative do it manually yourself. set a "record out" flag when the first user selects the record, and don't let the second user get the record. release the flag when the user exits. occasionally you might get an in-use flag left set - so you do need a process to release the lock.

a second way is to add a contact attempts table agianst each client, and store details of both calls, and indeed any further calls.