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.

License

For internal use only. This template does NOT come with a royalty-free license. You may only customize the template for your business needs and for use within your organization at one location ONLY. You may not resell or distribute any form of this template to others without express written permission. Contact me for additional information on obtaining a license to distribute if you plan on including this template in a product you are reselling. Additional licenses are required if you plan on using this template with more than ten (10) employees or in multiple locations within your organization.

Not a Finished Product

Please keep in mind that most of my template databases are not designed to be finished products that are ready to go in a working environment. My templates are meant as starting points for you to customize for yourself so you don't have to reinvent the wheel. This requires that you have a basic understanding of Microsoft Access development. To work on most of my databases you should have completed my Access Beginner series and the first couple levels of my Expert series at a minimum. Most of my databases require knowledge of SQL and VBA as well. Keep in mind that most of my templates are not like off-the-shelf software. They're starting points for developers to be able to modify and use as their own. It really is going to be in your benefit to watch all of the prerequisite videos so you understand the database and how it works moving forward. This is not like QuickBooks where you just install it and use it. My templates are mostly designed for people who already have an understanding of how Microsoft Access works.

Customize For Your Needs

If you would like to discuss customizing this template for your needs, and integrating it into your current setup, please see my consulting page for details. While I no longer accept custom jobs that are specific to a single user, I may include your features in a future version of this template if they have mass appeal. If you are looking for custom enhancements made to this template just for you, visit my Developer Network.

Technical Support

Please note that technical support is NOT guaranteed for any of my courses, seminars, or templates. If you require help with modifying this template, you may post a question in the Forums, however an answer to your question is not guaranteed. If this template comes with an accompanying Seminar, then you should purchase that Seminar to see how the database was constructed. If not, then you should have taken the suggested courses. Most of my templates are designed on a Developer level and you should have a thorough understanding of SQL and VBA before attempting to modify them. If you have a problem with one of my databases, I will only support the unmodified database exactly as it's downloaded from my site. If you have modified it in any way, it may not be something I can help you with. You can post in the Forums, and I may be able to help you, but if the issue doesn't exist in my unmodified database, it's not something I can support.

Questions?

Got sales or customer service questions about this template? Feel free to contact me. If you have technical "how do I" questions about this template, you discover a bug, or want to suggest a new feature, then please post your comments below.

Keywords

microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #templates, record locks, record locking, recordlocks

Comments for Access Record Locks Template

Age	Subject	From
4 years	Gone lock crazy	Thomas Gonder
4 years	Is null still problem	Thomas Gonder
4 years	Record Lock on Continuous form	Julian Okolonji
5 years	Invalid Use of Null	Ruud Schildknegt
5 years	combo box unbound	Ruud Schildknegt
5 years	Record Locks	Adam Schwanz
5 years	Where is the Code Vault?	Isa Bassari

Subscribe to Access Record Locks Template
Get notifications when this page is updated

Intro

In this video, I will show you my custom record locks template for Microsoft Access, which gives you a user-friendly way to handle record locking beyond the built-in Access features. You will see how to highlight locked records by changing form colors, check if any records in a table are locked before running action queries or code, and use global functions to work with any table. This template is useful for both automated processes and multi-user environments, helping you prevent simultaneous edits with visual feedback and other enhanced features.

Transcript

If you want to set up custom record locking in your Microsoft Access database, then my record locks template is for you.

Microsoft Access has built-in record locking capabilities, but they leave something to be desired. My template provides a much more user-friendly experience. For example, it can change the background color of the form to red if that record is locked.

I also include functions to allow you to check to see if any records in that table are locked before you perform something like an action query, or code any recordset that will fail if there are any users editing records in that table. That is the whole reason I designed this template in the first place - for myself, so I could check to see if my server was processing any records before I did something that might affect that.

This template is good for you whether you have automated processes that your database runs or if you have multiple users using your database at the same time and you want to prevent them from editing the same record, but give them a nice user experience.

If you just want simple record locking, I do have a TechHelp video available. You will find a link to it down below. That TechHelp video shows you how to use the built-in Microsoft Access record locking.

For my members, I create a simplified version of this template that shows you the basics of how to go about creating your own record locking scheme. Then, of course, there is this template which has all of the bells and whistles.

It is coded to allow you to lock any table, not just the customer table. You can change colors, everything is built into global functions, you can check to see if any record is locked in a particular table, and I include some code to put a timer in single forms. For example, if someone is editing the customer and someone else opens it up, they will see that it is locked, but then when the first user is done in a few seconds, they will see that the user is finished.

There are all kinds of cool extra features in this template. As always, if you have any questions, please feel free to post them in the comments section down below. Thanks.

Quiz

No quiz available.

Summary

Today's video from Access Learning Zone focuses on setting up custom record locking in your Microsoft Access database using my specialized record locks template.

While Access does come with built-in record locking, I have always found these default features lacking in terms of user experience. That is why I created this template, which greatly improves how users interact with locked records. For example, the template allows you to visually indicate when a record is locked by changing the background color of the form to red, making it clear that the record is being edited by someone else.

In addition to providing better visual feedback, I have included functions that let you check if any records in a table are currently locked. This is especially useful before running action queries or processing any recordset that might otherwise fail if one or more records are open for editing by other users. The entire purpose behind developing this template was to give myself a way to check whether any database activity was in progress before launching processes that might conflict with those actions.

This template works well whether your database runs automated jobs or you have multiple users accessing it at the same time. By implementing this system, you reduce the risk of users overwriting each other's changes while also supporting a smoother, more understandable workflow.

If your needs are simpler, there is a basic TechHelp video available that shows you how to use Microsoft Access's built-in record locking feature. You will find a link to that video on my website below.

For members, I offer a straightforward version of this record locking setup so you can get a feel for how to build your own system. However, this more advanced template includes a full range of features. It is designed to work with any table in your database, not just the customer table, and all options like colors and locking functions are managed globally. The template also lets you check for locked records in any specified table and features a timer for single forms, so users will know when another user has finished editing a locked record and the lock is cleared.

There are plenty of additional features included to refine your database's record locking behavior. If you have any questions, feel free to ask in the comments section. You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

This is a sales message describing a Microsoft Access record locking template and its features.