Code required for searching unlocked records in multi user situations.

[AshikHusein]

I need help with some Visual basic code for a multi user environment.

There will be about 20 user updating data using a form linked to a table. (so effectively tha table will be getting updated).

There will be pessimmistic locking used while they are working on the table updates through the form, from their respective terminals.

I would like to know the jist of Visual Basic code which does the following:

Whem the form is opened, it should take the user to a record which is either not locked or a certain field in the record (say Field1) has not been filled.

So effectively, when the user opens the form, it should take her/him thru the records and stop at a record which is not locked or Field1 not updated.

After the user updates that record ot should take the user to the next record which meets the above criteria:

I am aware that there is a recordlocks property but am unsure as to how to use it.

Would appreciate help from this truly great ACCESS forum!!

 

[AshikHusein]

Well, nobody replied to this so I will tell you of my own experience with it.

The purpose was to have a multi user ACCESS database which would have a table with information about clients. When multiple users were logged on , it was important that if one representative was viewing a client record, another user should not be able to see that same record but move on to the next one.

The problem with pessimistic locking is that it may allow two people to see the same record but only when one starts to edit the record ,it gives a message that the record "is locked".

What I did was create a dumy field (say Dummy1) in the table, and as soon as a user went to that record, it would update the Dummy1 to a value of "1".

Now if another user tried to move to the same record, the code cheked to see if the Dummy1 field was updated to "1". If it was then the second user would move to the next record which did not have Dummy1 updated to the value of "1".

There was also an error routine in place which moved the user to the next record, in case the record that he/she was on was being edited by another user. This took care of the fact that two users may land on a client record at the same time.

Ashik

 

[sudhirhinduja]

Record locking

Hi,

Could you explain this with code. I'm trying to implement something similar. I have the query off which a form opens. But when I click the Next To Do button it should goto the next unlocked record since I am only locking the current record.

Thanks,
Sudhir

Well, nobody replied to this so I will tell you of my own experience with it.

The purpose was to have a multi user ACCESS database which would have a table with information about clients. When multiple users were logged on , it was important that if one representative was viewing a client record, another user should not be able to see that same record but move on to the next one.

The problem with pessimistic locking is that it may allow two people to see the same record but only when one starts to edit the record ,it gives a message that the record "is locked".

What I did was create a dumy field (say Dummy1) in the table, and as soon as a user went to that record, it would update the Dummy1 to a value of "1".

Now if another user tried to move to the same record, the code cheked to see if the Dummy1 field was updated to "1". If it was then the second user would move to the next record which did not have Dummy1 updated to the value of "1".

There was also an error routine in place which moved the user to the next record, in case the record that he/she was on was being edited by another user. This took care of the fact that two users may land on a client record at the same time.

Ashik

 

[AshikHusein]

Hi

I basically did it using a record set clone object in VBA. As soon as someone went to a record (after pressing a button) I asked the program to keep moving to the next record till there was no update in what I named as a dummy field of each record.

As soon as smeone stopped at a record, the dummy field would update to 1 so if there was someone else searching through the records, they waould not stop at the record having the dummy field as 1.

I will not be able to reply tomorrow if you resoponfd to this cause I may not be in at work. But I will check from home if you need more help.

Ashik

 

[AshikHusein]

Hi

I basically did it using a record set clone object in VBA. As soon as someone went to a record (after pressing a button) I asked the program to keep moving to the next record till there was no update in what I named as a dummy field of each record.

As soon as smeone stopped at a record, the dummy field would update to 1 so if there was someone else searching through the records, they waould not stop at the record having the dummy field as 1.

I will not be able to reply tomorrow if you resoponfd to this cause I may not be in at work. But I will check from home if you need more help.

Ashik

 

[sudhirhinduja]

Thanks

Hey Ashik,

Thanks for that input. But I have not used recordsets to date. I have a table of 40000 records. I made a query to select certain records and a form based on that query for editing.

Now multiple users are opening that form, so many of them goto the same record. I want to go to the next unlocked record. I have a phone status which the users update. Based on the phone status on clicking the next to do button I do a requery. So, say 8 users users might open the same form since the requery runs, it goes back to the same record intially.

If you have the code using recordsets or any other advice that would help.

Thanks,
Sudhir.

Hi

I basically did it using a record set clone object in VBA. As soon as someone went to a record (after pressing a button) I asked the program to keep moving to the next record till there was no update in what I named as a dummy field of each record.

As soon as smeone stopped at a record, the dummy field would update to 1 so if there was someone else searching through the records, they waould not stop at the record having the dummy field as 1.

I will not be able to reply tomorrow if you resoponfd to this cause I may not be in at work. But I will check from home if you need more help.

Ashik

 

[AshikHusein]

Hi Sudhir,

I could have given you the code, but the problem is that when I wrote the code it was in Access 2000 and in my new department they have Access 97 which wont allow me to open anything of Access 2000.

But what you can do (if you dont want to use VBA), is use Record Level Locking. So although two people can view a record at the same time, only one of them will be able to edit the record. Will that help you??

You can search on record level locking and see if you can fine something.

Ashik

 

[sudhirhinduja]

Hi

Hi Ashik,

I'll explain the problem properly. Maybe I need to use recordsets and the lockedits property. I just dont know how to do that in VBA since I have not done too much of coding.

I have implemented an editable multi-user database which has no additions/deletions to it. It has a form based off a query.That form has a button NextToDo. On clicking this button, a macro starts which runs a requery as the first step and gotoRecord Next as the second step.

Problem is many times two or more users are opening the same record. I need to avoid this since it makes no sense for multiple people to open the same form even though they cant edit it. So I need some way to goto the next unlocked record.

Can you help me with the code using lockedits property?

Please help.

Thanks,
Sudhir.

Hi Sudhir,

I could have given you the code, but the problem is that when I wrote the code it was in Access 2000 and in my new department they have Access 97 which wont allow me to open anything of Access 2000.

But what you can do (if you dont want to use VBA), is use Record Level Locking. So although two people can view a record at the same time, only one of them will be able to edit the record. Will that help you??

You can search on record level locking and see if you can fine something.

Ashik

 

[sudhirhinduja]

Hi

Hi Ashik,

Could you elaborate a little more. I have searched a lot but don't find anything helpful.

Thanks,
Sudhir Hinduja

Hi Ashik,

I'll explain the problem properly. Maybe I need to use recordsets and the lockedits property. I just dont know how to do that in VBA since I have not done too much of coding.

I have implemented an editable multi-user database which has no additions/deletions to it. It has a form based off a query.That form has a button NextToDo. On clicking this button, a macro starts which runs a requery as the first step and gotoRecord Next as the second step.

Problem is many times two or more users are opening the same record. I need to avoid this since it makes no sense for multiple people to open the same form even though they cant edit it. So I need some way to goto the next unlocked record.

Can you help me with the code using lockedits property?

Please help.

Thanks,
Sudhir.