microsoft access vba coding in form to determine if a user has a record open for editing
Right now I have a form with a custom built navigation button system that a user uses to navigate thru records and make changes to the record where needed. Each record is identified uniquely by an AutoNumber called "Service Report No". As the user navigates thru the records, all the controls are disabled. Only if the user enters the Service Report No in a textbox called "txtSearch" that matches the Service Report No., then all the controls on the form are enabled for the user to edit the data. What I want to do is, if VBA possible, to check to see if the Service Report No record is already open by another user after another user updates the txtSearch value to edit the record. If the record is open by another user, then the other user(s) is/are prompted with a message box stating that the record cannot be edited and the user(s) acknowledge the message and then docmd go to the first record. Under Tools and Options and the Advanced Tab for my database, I have the "Default record locking" set to "Edited record" and the database opens using record level locking. However, at times, when the user is warned about a write conflict, the user selects "Save Record" instead of "Discard changes" and ends up overwriting the original user's edited values for that record. Somehow I would like to capture the cursor position within the underlying table (called Service Database that the form is tied to) such that if another user tried to get to that same cursor position, that user is prohibited from going to that record when updating the txtSearch value. How can this be accomplished using VBA?
ASKER
Sorry Jeff:
In a nutshell, trying to implement pessimistic record level locking on a form tied to my table using VBA rather than the default options that come with access so that I can customize the record level locking with additional coding such as messages and moving to the first record.
In a nutshell, trying to implement pessimistic record level locking on a form tied to my table using VBA rather than the default options that come with access so that I can customize the record level locking with additional coding such as messages and moving to the first record.
ASKER
The form in question is "Service Log New_QARA" (tied to the Service Database table) which should open up as the db opens and the record goes to the first record. As you can see, the controls are disabled. To enable the controls on the form, simply type in the Service Report No in the textbox located between the custom navigation buttons. This textbox is called txtSearch. In a multi-user environment, if the user is "in" any one of the record and another user decides to "enter" that same record, I would like a vba message stating that "You are not allowed to enter this record", and when the user acknowleges this messages, the record focus is moved back to the first record.
test.mdb
test.mdb
So what is the direct question here?
ASKER
?????
ASKER
Hi Jeff:
I believe I have asked the same question twice........here is my third attempt...................
Thank you.
I believe I have asked the same question twice........here is my third attempt...................
Thank you.
I don't think there is a direct way to do this...
Other than trapping an error.
So if there is an error generated then you can do something like this:
If Error.Number=999 then
msgbox "You cannot enter this record."
End if
Other than trapping an error.
So if there is an error generated then you can do something like this:
If Error.Number=999 then
msgbox "You cannot enter this record."
End if
ASKER
So this is what I did.....in the form's property I set the "Record Locks" to edited record. This works great for me......just don't like the default message that comes up that the record was edited...........so where would i enter your suggestion of the coding to give that msg. I think we are almost there..........
If there was an error then note the error number then set up your error handler something like this:
On Error GoTo Err_YourSubName
'********** Your Code Goes Here **********
Exit_YourSubName:
Exit Sub
Err_YourSubName:
'If the Record is being Edited...
If Err.Number = xxx Then
Msgbox "Record is being Edited",vbinformation
Resume Exit_YourSubName
Else
MsgBox "There was an error executing the command." _
& vbCrLf & "Error " & Err.Number & ": " _
& vbCrLf & Error, vbExclamation
Resume Exit_YourSubName
End If
On Error GoTo Err_YourSubName
'********** Your Code Goes Here **********
Exit_YourSubName:
Exit Sub
Err_YourSubName:
'If the Record is being Edited...
If Err.Number = xxx Then
Msgbox "Record is being Edited",vbinformation
Resume Exit_YourSubName
Else
MsgBox "There was an error executing the command." _
& vbCrLf & "Error " & Err.Number & ": " _
& vbCrLf & Error, vbExclamation
Resume Exit_YourSubName
End If
ASKER
Hi Jeff:
No error number is shown other than that this record was edited (in built access message I guess). See attached picture. I am just trying to change that messagebox to a more custom message box when the record was updated by another user and the current user is now trying to access that same record. I was thinking if there was a way where oncurrentevent property......
if me.currentrecord.islocked then
msgbox....cannot edit this record
endif
But I am not sure if this type of a sub or function exists
editrecord.gif
No error number is shown other than that this record was edited (in built access message I guess). See attached picture. I am just trying to change that messagebox to a more custom message box when the record was updated by another user and the current user is now trying to access that same record. I was thinking if there was a way where oncurrentevent property......
if me.currentrecord.islocked then
msgbox....cannot edit this record
endif
But I am not sure if this type of a sub or function exists
editrecord.gif
Then you will have to determine the event that triggers this message.
If there is some event that triggers if the record is locked, then there may be a way to create your own message.
Perhaps I am missing something simple, but I am not sure (if I am understanding what you are asking for here) that there will be an easy way to do what you are asking.
JeffCoachman
If there is some event that triggers if the record is locked, then there may be a way to create your own message.
Perhaps I am missing something simple, but I am not sure (if I am understanding what you are asking for here) that there will be an easy way to do what you are asking.
JeffCoachman
ASKER
So maybe that is the question......how can I tell if the record is locked. I.e., how do I know that user A is editing on the record X so that if user B wants to also come onto the same record X and edit it, user B will be prompted with an error message.
I dont know how to capture this type of an event. How does one accomplish this type of an even capture in Access. The recordlocks property in design view shows me three selections...no locks, edited record, all records. I have set this to edited record for record locks.
I dont know how to capture this type of an event. How does one accomplish this type of an even capture in Access. The recordlocks property in design view shows me three selections...no locks, edited record, all records. I have set this to edited record for record locks.
ASKER
Please take a look at this thread. I have the same question..................
http://www.access-programmers.co.uk/forums/archive/index.php/t-54041.html
http://www.access-programmers.co.uk/forums/archive/index.php/t-54041.html
ASKER
Narrowing the question.......How do I capture the "Locked Record Indicator" symbol that shows on the left of the form (symbol for this indicator is a circle with a slash in it) when a user attempts to edit a record being edited by someone else (i.e, locked record)?
ASKER
Using access's default locking mechanism the user is not alerted that someone else has the record open until after they make all the changes and then hit save. It wastes their time to find out at the end. If you check when the record becomes active and alert them it is already open by another user they know they can just read the data.
How about this trick:
Add a tracking field to the underlying table.
In the procedure that enables the form, check if there is a value in that field.
If there isn't, then write a value and save it, then enable the form.
If there is, then tell the user, and keep the form disabled.
When the user explicitly saves the record, behind the scenes, save it, then delete the value in the tracking field for that record, then save it again.
Sound good to you?
Add a tracking field to the underlying table.
In the procedure that enables the form, check if there is a value in that field.
If there isn't, then write a value and save it, then enable the form.
If there is, then tell the user, and keep the form disabled.
When the user explicitly saves the record, behind the scenes, save it, then delete the value in the tracking field for that record, then save it again.
Sound good to you?
ASKER
I was thinking about that Andrew.........will let you know.........
The only thing you have to look out for is if something goes wrong, and a value is saved even if there's no-one editing it.
I haven't got the code on hand, but I have found code out there in the interwebs for determining who's connected (it reads the .LDB file). If you find and use that, you can make sure that your tracking field is completely clear when the last user connected gets out of the data. That worked for me, but it was a system with only a few users, so most evenings everyone was out of the app.
If you have too many users in there the whole time, it might have to be an admin function to make someone deliberately opens the record and delete the tracking value.
It's not too tricky to do, but it's like most aspects of Access security - better rolling your own!
I haven't got the code on hand, but I have found code out there in the interwebs for determining who's connected (it reads the .LDB file). If you find and use that, you can make sure that your tracking field is completely clear when the last user connected gets out of the data. That worked for me, but it was a system with only a few users, so most evenings everyone was out of the app.
If you have too many users in there the whole time, it might have to be an admin function to make someone deliberately opens the record and delete the tracking value.
It's not too tricky to do, but it's like most aspects of Access security - better rolling your own!
ASKER
Do you have a link or coding on how to read the .LDB file?
Sorry dude - I might be able to find it later, but you'll probably have found it yourself with google by then!
ASKER
ok.....will keep everyone updated on coding........
Try this: http://support.microsoft.com/kb/285822
(The code I've got is archived away somewhere, but this MSDN article is where I got started.)
(The code I've got is archived away somewhere, but this MSDN article is where I got started.)
ASKER
Experts:
I really need help with this please...........How can a user know that a record is being edited other than "paying attention" to the fact that there is a circle slash sign next to the record being edited. I would like some message box to alert the user as such........The sample db is attached above.
I really need help with this please...........How can a user know that a record is being edited other than "paying attention" to the fact that there is a circle slash sign next to the record being edited. I would like some message box to alert the user as such........The sample db is attached above.
ASKER
How do I capture the "Locked Record Indicator" symbol that shows on the left of the form (symbol for this indicator is a circle with a slash in it) when a user attempts to edit a record being edited by someone else (i.e, locked record)? Using access's default locking mechanism the user is not alerted that someone else has the record open until after they make all the changes and then hit save. It wastes their time to find out at the end. If you check when the record becomes active and alert them it is already open by another user they know they can just read the data.
ASKER
Andrew:
Not sure how to apply your trick........Can you post the applied solution to my sample db posted earlier please?
Not sure how to apply your trick........Can you post the applied solution to my sample db posted earlier please?
ASKER
I was reading this article and wondering if an expert to apply it to my posted db.......
http://www.accessmonster.com/Uwe/Forum.aspx/access/111016/record-locks
http://www.accessmonster.com/Uwe/Forum.aspx/access/111016/record-locks
You're in luck. It's a quiet day at work, I'll see if I can hack something out.
I've taken a look at your database. Is it one you've written yourself or is it one you're maintaining that someone else wrote?
I've taken a look at your database. Is it one you've written yourself or is it one you're maintaining that someone else wrote?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Andrew:
I tested your solution in a multi-user environment. I "went into" record 1 and did not change anthing. Then I typed record 2 in the txtsearch box and stayed there (I did not press enter). My colleageue then navigated to record 1 and entered into record 1 by pressing and entering "1" in the txtsearch box. He made some changes. Then when he pressed the nextrecord button (cmdNext button on the form), he got a write conflict by Access to see if he wanted to save the record, copy to clipboard or drop changes, which should not have popped up on him since I did not make any changes..............?
Thank you so much for taking the time and helping me with this.
I tested your solution in a multi-user environment. I "went into" record 1 and did not change anthing. Then I typed record 2 in the txtsearch box and stayed there (I did not press enter). My colleageue then navigated to record 1 and entered into record 1 by pressing and entering "1" in the txtsearch box. He made some changes. Then when he pressed the nextrecord button (cmdNext button on the form), he got a write conflict by Access to see if he wanted to save the record, copy to clipboard or drop changes, which should not have popped up on him since I did not make any changes..............?
Thank you so much for taking the time and helping me with this.
ASKER
Maybe the problem has to do with saving the updates...................
Sounds like it. Sorry - but I'm buried in my own work.
Still, I think I must have given you something to work with. I'd love to give you more time, but suffice it to say I'm retired as an Access dev, and work now as a Scrum Master for a bank, so my time on EE is a luxury for me!
Keep going though. I slung that code together in about half an hour. I've done it before, and made it work, and I think you're on the right track. The key is to update the record with a flag of some sort and save it as you start to edit it. Have code check as you arrive on any record - does this record have a flag set? If so, tell the user they can't edit it. Once you've finished with the edit, explicitly save and remove the flag.
Access does exactly this, but it doesn't let you play with it. So, roll your own!
(BTW the data design in your db is a shocker! Tell me someone else designed it! If not, I'd urge you to read up on normalization, it'll save you a lot of pain later on, trust me. They came up with database theory because it works!)
Still, I think I must have given you something to work with. I'd love to give you more time, but suffice it to say I'm retired as an Access dev, and work now as a Scrum Master for a bank, so my time on EE is a luxury for me!
Keep going though. I slung that code together in about half an hour. I've done it before, and made it work, and I think you're on the right track. The key is to update the record with a flag of some sort and save it as you start to edit it. Have code check as you arrive on any record - does this record have a flag set? If so, tell the user they can't edit it. Once you've finished with the edit, explicitly save and remove the flag.
Access does exactly this, but it doesn't let you play with it. So, roll your own!
(BTW the data design in your db is a shocker! Tell me someone else designed it! If not, I'd urge you to read up on normalization, it'll save you a lot of pain later on, trust me. They came up with database theory because it works!)
ASKER
Hahahaha......Love the humor. It is always welcome. Yes, this db is a legacy product. And I have to "deal" with it. I am an engineer by trade and vba is something new to me though I have coded in other antiquated languages so logic I understand, syntax still learning. U r right........"save it as you start to edit it." Will keep u posted.............
ASKER
Hi Andrew:
You coding was MASTERFULLY DONE. Talk about overcoming the in-built record locking system in Access and developing your own. I simply added a couple of saves for each of my custom navigation buttons which now allows only one user to enter the record and edit. If other users try to enter the record to edit, they are greeted by a custome message box. The lock is released on the edited record during the form's unload event, which I added. THANK U AND THANK U!!!!! Ur CODING SKILLS ARE AWESOME!!!
Sanj
You coding was MASTERFULLY DONE. Talk about overcoming the in-built record locking system in Access and developing your own. I simply added a couple of saves for each of my custom navigation buttons which now allows only one user to enter the record and edit. If other users try to enter the record to edit, they are greeted by a custome message box. The lock is released on the edited record during the form's unload event, which I added. THANK U AND THANK U!!!!! Ur CODING SKILLS ARE AWESOME!!!
Sanj
Thanks dude. Fifteen years knocking out Access apps, I'm just about getting the hang of it!
Multiple smaller paragraphs would have been easier to read.
Just FYI...
As far as your question is concerned,... with all you have listed here, this will this will be near impossible for any expert to propose a solution without being intimately familiar with your project.
Your best bet would be to post a sample of your database and see if there are any takers...
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.
In other words, ...post a database that we can easily open and immediately see the issue.
JeffCoachman