Solved

microsoft access vba coding in form to determine if a user has a record open for editing

Posted on 2010-11-10
34
2,247 Views
Last Modified: 2013-11-28
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?
0
Comment
Question by:sxxgupta
  • 21
  • 8
  • 5
34 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34108631
First I will say that you post is extremely difficult to read as one giant paragraph.
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
0
 

Author Comment

by:sxxgupta
ID: 34110835
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.
0
 

Author Comment

by:sxxgupta
ID: 34111422
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34112381
So what is the direct question here?
0
 

Author Comment

by:sxxgupta
ID: 34112866
?????
0
 

Author Comment

by:sxxgupta
ID: 34112917
Hi Jeff:
I believe I have asked the same question twice........here is my third attempt......................What will be the vba coding to show a message that "You are not allowed to enter this record" when, in a multi-user environment, a user decides to "enter" a record that is already open by another user (i.e, the table cursor is already at the record that a second user is attempting to reach)?
Thank you.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34113650
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
0
 

Author Comment

by:sxxgupta
ID: 34113672
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..........
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34114094
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      

0
 

Author Comment

by:sxxgupta
ID: 34114172
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34114723
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

0
 

Author Comment

by:sxxgupta
ID: 34114975
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.
0
 

Author Comment

by:sxxgupta
ID: 34115038
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
0
 

Author Comment

by:sxxgupta
ID: 34115194
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)?
0
 

Author Comment

by:sxxgupta
ID: 34115635
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.
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34115713
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?
0
 

Author Comment

by:sxxgupta
ID: 34115847
I was thinking about that Andrew.........will let you know.........
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34115870
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!
0
 

Author Comment

by:sxxgupta
ID: 34115929
Do you have a link or coding on how to read the .LDB file?
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34116424
Sorry dude - I might be able to find it later, but you'll probably have found it yourself with google by then!
0
 

Author Comment

by:sxxgupta
ID: 34116658
ok.....will keep everyone updated on coding........
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34117094
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.)
0
 

Author Comment

by:sxxgupta
ID: 34121538
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.
0
 

Author Comment

by:sxxgupta
ID: 34121553
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.
 
0
 

Author Comment

by:sxxgupta
ID: 34121560
Andrew:
Not sure how to apply your trick........Can you post the applied solution to my sample db posted earlier please?
0
 

Author Comment

by:sxxgupta
ID: 34121894
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
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34122137
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?
0
 
LVL 8

Accepted Solution

by:
Andrew_Webster earned 500 total points
ID: 34122595
Ok, so here's what I've done.

1. Added a module modRecordLocking.
2. Added a field to the table [Service Database] called [EditLock].
3. Added code that checks if there's a value in [EditLock] when you use txtSearch to jump to and enable a record.
    If there is, the user is warned that the record is locked.
    If not, then a value is saved to EditLock and the form is enabled.  A flag is set so we know that it is us that set this value.
4. As we leave using the navigation buttons, if the flag is set, then the value in EditLock is cleared.

I've commented code as much as I can to show where I've made changes.

You may want to think about what will happen if the user leaves by using txtSearch.  I've run out of time to work on this.

Good luck, I hope this helps.

test.mdb
0
 

Author Comment

by:sxxgupta
ID: 34124631
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.
0
 

Author Comment

by:sxxgupta
ID: 34124648
Maybe the problem has to do with saving the updates.......................in the code.
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34124848
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!)
0
 

Author Comment

by:sxxgupta
ID: 34125370
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.............
0
 

Author Comment

by:sxxgupta
ID: 34128742
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
0
 
LVL 8

Expert Comment

by:Andrew_Webster
ID: 34154777
Thanks dude.  Fifteen years knocking out Access apps, I'm just about getting the hang of it!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now