locking all records in a table

[ngreenleaf]

I want to lock all records in a table only for a small point in time during a particular event procedure. The user enters text in a text box, and I want to insert it in the table and requery the table instantly in order to find out what autonumber was assigned to that new record. If I have multi-users there can be issues unless I lock the table before the insert and unlock it after the autonumber is identified.

It appears that I cannot set the recordsource and recordlock properties in the middle of the form's execution. The error message says that the table is being "used exclusively by someone else or is being used by the interface and cannot be programmatically manipulated". As I am currently the only user - the second situation seems to be more likely.

I prefer not to have the locks set for the entire time a user is in the form. Is there a way to do this? As an FYI - I don't have the form recordsource set to anything -- the form uses many tables and queries -- so I code them as necessary in VB. But I can set the recordsource to the desired table and that works fine as well... it is changing the locks that causes the problem.

 

[VicM]

ngreenleaf:

If you put the following command at the beggining of the event procedure, it should prevent the local user from making any entries until the procedure completes:

DoCmd.Hourglass True

In order to prevent any other concurrent user from entering data to that record, you will need to change the Form's Record Locks property to Edited Record.

That should do the trick. If another user tries to make changes to the record while a procedure is running, they will get a message to the effect that the record is locked and no changes can be made. Since you say your procedure is relatively quick, a remote user may not ever see the message.

And by the way, the Edited Record property functions independently of the hourglass all the time, preventing a user from changing a record while another user is editing that record. The hourglass only works for the local user that initiated the procedure.

But be aware, the last change made to the record is the record that will be stored. You need to have a way to determine which change you want to be the ruling one.

Hope this helps,

Vic

 

[ngreenleaf]

Let me clarify. I want the local user to take control of the entire table. The user will (1) insert a record, and (2) query the table immediately in order to get the autonumber just inserted. Once the autonumber is determined the table should be opened up to all users again.

I tried doing me.recordlocks=1 -- to lock "All records" and I had the recordsource set to the entire table. But when it hits the me.recordlocks=1 statement it says I cannot do that. See the error message provided above.

I'm trying to change the locks property during execution rather than on the entire form for the duration of the form being opened.

 

[VicM]

ngreenleaf:

From what I've been able to gather, once the form is opened its record locking properties are not changeable, as you've already discovered.

On the other hand, if you put a textbox on your form showing the autonumber, and set it's data properties to:

Enabled=No; Locked=Yes

then as soon as one character is entered into any of the other text boxes on the form, the autonumber will appear.

You really don't have to set those properties. If someone tries to change it, Access will prevent it from happening since it's an autonumber. However, the property settings will not allow the cursor to be put into the autonumber box, and removes the box from the tab sequence.

Right now, I don't see any other way to achieve what you want to accomplish. Maybe someone else can shed some light.

HTH,

Vic

 

[ngreenleaf]

Thanks Vic... I think you are right about not being able to change the setting.