Computer Learning Zone CLZ Access Excel Word Windows

Education is a better safeguard of liberty than a standing army.

-Edward Everett
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
Home > Courses > Access > Templates > Record Locks
 
Record Locks Template

Microsoft Access Record Locks Template


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  Buy Now

Videos

Description

Microsoft Access has built-in record locking, to prevent two users from modifying the same record at the same time. You have to know how to turn it on and use it, which I cover in my Free TechHelp video (Video #2 above). However, it's not very user friendly, and there are some problems with it. First of all, if you have record selectors turned off, your user won't know why they can't edit the record. Also, if you try to run an action query that modifies anything in the entire table, it might fail if just one record is locked.

That's why I designed my own record locking system. In Video #3, I teach you how to set up your own record locks. You'll create your own record lock table and add the appropriate code to your database to read/write locked records and make changes to the form, such as putting *LOCKED* in the form caption. You will learn how to lock your customer table and form so two users can't edit the same record at the same time AND they'll get a more user-friendly warning. Plus I'll show you how to check if any records are locked before you run any update queries that might affect the entire table.

Then, in Video #4, I will walk you through the custom Record Lock Template. I've created a set of functions and a global module that you can use to lock ANY table/form you want. I'll show you how to implement them in your database. You'll learn how to change the form's background color to red if the record is locked. I'll show you how to use a timer event to lock/unlock the record (if you're waiting for someone else to be finished editing it, for example). Then I'll show you functions to determine if a single record is locked, or if any record in the table is locked. 

Database Download

Once you have purchased the full database template, come back to this page and click on the Download button below. This will give you access to the ZIP file containing the Template ACCDB file. You will also have access to videos 3 and 4 above. Please watch them.

Customize For Your Needs

If you would like to discuss customizing this database for your needs, and integrating it into your current setup, please see my consulting page for details on rates and other information.

Questions?

Got sales or customer service questions about this template? Feel free to post them below. If you have a questions, or you discover a bug or want to suggest a new feature, then please by all means post your comments below.

 

Invalid Use of Null Upload Images   Link 
Ruud Schildknegt 
2 months ago
record locking template gives an error when you add a product. How can I solve this?
Ruud Schildknegt
2 months ago

Richard Rost
2 months ago
I checked it out. I'm surprised nobody has caught this yet. Turns out that the code WILL throw an Invalid Use of Null error when you go to add a new record. It's trying to LOCK a Null OrderDetailID. So we can fix that by simply checking for a Null and exiting the sub if that's the case. Add one line to the start of the sub:

Private Sub Form_Dirty(Cancel As Integer)

    If IsNull(OrderDetailID) Then Exit Sub

    If IsRecordLocked(OrderDetailID, "OrderDetailT") Then
        Cancel = True
    Else
        LockRecord OrderDetailID, "OrderDetailT"
        LockRecord OrderID, "OrderT"
    End If
    doFormLocked Me
    
End Sub


That record doesn't need to be locked yet if it's new because it won't show up in the table for anyone else anyhow until it's committed after editing.
Add a Reply
combo box unbound Upload Images   Link 
Ruud Schildknegt 
2 months ago
on the form i use a unbound combo box to seacrh while iam typing. Too bad the forumlier doesn't recognize the typing as a change (dirty). Therefore the record lock doesnt work. Can you give me a tip to solve this so the record will lock?
Adam Schwanz
2 months ago
Just manually set it to  locked in the on change event
Ruud Schildknegt
2 months ago
thnx works like a charm now
Add a Reply
Record Locks Upload Images   Link 
Adam Schwanz 
6 months ago
Should i be turning off my access built in record locks when using this method or is it irrelevant? Thanks
Richard Rost
6 months ago
Shouldn't really matter. I can't see it hurting if you already have it active, but it shouldn't get to that point because your code will prevent the record from being edited in the first place.
Add a Reply
Where is the Code Vault? Upload Images   Link 
Isa Bassari 
6 months ago
Where is the code vault?
Isa Bassari
6 months ago
Found it!!!
Richard Rost
6 months ago
Code Vault
Add a Reply

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: templates access record locks record locking recordlocks  PermaLink