Computer Learning Zone CLZ Access Excel Word Windows

A person who won't read has no advantage over one who can't read.

-Mark Twain
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   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  |  Bookmark Buy Now



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.


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 
6 months ago
record locking template gives an error when you add a product. How can I solve this?
Ruud Schildknegt
6 months ago

Richard Rost
6 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
        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.
Adam Schwanz
6 months ago
Sorry I fixed mine a while ago and completely brushed off that it was something in the template that I should maybe tell you about LOL. Assuming a few people did that.
Richard Rost
6 months ago
Gee... thanks, Adam. :/
Adam Schwanz
6 months ago
I'm so helpful :) LOL
Ruud Schildknegt
6 months ago
I've been puzzling for a while but can you guys tell me if there is a way to lock the subform as soon as 1 record is added or changed in the subform?
Richard Rost
6 months ago
You can set a maximum number of records for a related table (subform) using DCount and a Before Insert event. That's actually going to be in today's TechHelp video. Should be posted later today.
Adam Schwanz
6 months ago
What about tempvars? :) LOL
Richard Rost
6 months ago
Ruud: See today's Extended Cut video on DCount

Adam: What about it?
Adam Schwanz
6 months ago
I was making a joke about wanting to bump up the tempvars Techhelp :P.

Richard Rost
6 months ago
Adam: Ah. Funny. Ha. Ha. Ha. :)

Ruud: See the Extended Cut for today's DCount video.
Ruud Schildknegt
6 months ago
I had no success with the dcount command. After quit a puzzle i solved it by using BeforeInsert event with a simple code:

    If IsRecordLocked( 'parentid' , "table") Then
        doFormLocked Me
        doFormUnlocked Me
    End If

this lockes the source table of the subform so no one can edit or add records on the subform
Add a Reply

Show All Comments

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.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
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