By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Programatically lock Access 2003 records

P: 12
Hello Everyone,

I am using Access 2003 to create a parts management system.

I am looking to create a checkbox (or any other control) in a form that when checked by the user locks the current record (or group of records, in the event a subform is used) so that it/they cannot be edited until another user unchecks the box. This is intended to prevent anyone from accidentally modifying a record, but still allowing them to do so if necessary by unchecking the box.

The check box is labelled "RMA Closed", and when checked should lock the current record(s).

Any help would be greatly appreciated as I have looked all over and cannot find code to perform this specific task. Also tell me if I'm CRAZY and this is not possible so I don't pull any more hair out.

Cheers,
Evan
Jun 3 '07 #1
Share this Question
Share on Google+
21 Replies


100+
P: 375
Hello Everyone,

I am using Access 2003 to create a parts management system.

I am looking to create a checkbox (or any other control) in a form that when checked by the user locks the current record (or group of records, in the event a subform is used) so that it/they cannot be edited until another user unchecks the box. This is intended to prevent anyone from accidentally modifying a record, but still allowing them to do so if necessary by unchecking the box.

The check box is labelled "RMA Closed", and when checked should lock the current record(s).

Any help would be greatly appreciated as I have looked all over and cannot find code to perform this specific task. Also tell me if I'm CRAZY and this is not possible so I don't pull any more hair out.

Cheers,
Evan
I think you can do by changing cursor type. I have not done myself. Let me try out and I will let you know(preferably tomorrow)
Jun 3 '07 #2

MSeda
Expert 100+
P: 159
You could do something in the forms on current event to evaluate the record's locking check box and then lock or unlock the controls on the form.
use something like:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_current
  2. dim ctl as object
  3.  
  4. For Each ctl In me.Controls
  5.             If me.lockrec = true then
  6.              ctl.locked = true
  7.             else
  8.             ctls.locked = false
  9.             End If
  10. Next
  11.  
  12. End Sub
  13.  
this is just a vague example if you need help applying it let us know.
Jun 4 '07 #3

P: 12
Thanks to all who replied, in fact I am new to VBA so a little extra help on this would be great.

The form I need to lock is called “frmReceiving”.

“frmReceiving” contains one subform: “PartsTableReceivingSubform” which I also need to lock

The record lock checkbox is called “ContentsVerified” and is located in “frmReceiving”.

I understand that the code has to go in the on current event, it’s the structure I think I need a little help with. Any assistance is greatly appreciated.

Evan
Jun 4 '07 #4

MSeda
Expert 100+
P: 159
This code should work the way it is. I have added an additional if statement that checks the controls tag for the string "locked" If you do not want a specific control to be locked or unlocked type the word locked into the controls tag property so it will be bypassed, you would want to do this for the contents verified check box if you want the user to be able to uncheck it on this form and also for a combo box used to navigate the form so that it will not become locked, preventing the user from navigating records. additionally, the subform container is also just a type of control so it will be modified the same as any other controls by the loop.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_current
  2. dim ctl as object
  3. For Each ctl In me.Control
  4. if ctl.tag <> "locked" then
  5.    if me.ContentsVerified = true then
  6.        ctl.locked = true 
  7.        else
  8.        ctl.locked = false
  9.        end if
  10.    end if
  11. Next
  12.  
additionally you want to call the above procedure in the contents verified after update event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ContentsVerified_AfterUpdate()
  2. Form_current
  3. End Sub
  4.  
try to implement this code into your form and see if you can get it to work for you then post back with an update
Jun 4 '07 #5

P: 12
I tried to use the code and it produced the following error

"The expression On Current you entered as the event property setting produced the following error: Method or Data member not found..."

I am also using an On Load event to disable the mousewheel and get the same error for it

"The expression On Load you entered as the event property setting produced the following error: Method or Data member not found..."

The debugger takes me to line 3. and highlights in yellow the ".Control" portion of the On Current code. Am I supposed to change this to match the form, or a specific control?

Thanks again for all the help!
Jun 4 '07 #6

Denburt
Expert 100+
P: 1,356
I hate breaking into a thread mid stride and redirecting you however I think that a much simpler and more secure method would be to use the before update event and verifying the checkbox there.

You stated that you are using MS Access for this project correct? The reason I am asking is because Visual Basic is a program in and of itself where MS Access uses VBA. If you are using strictly MS Access then let us know so it can be moved although many of the VB experts here can help, you will probably have more success in that forum.

If you are designing the forms and code in MS Access then do this.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim rs As Recordset
  3. 'When a recordsets Clone is created it is automatically opened at the current record being viewed (see the help file in a VBA window for more info).
  4. Set rs = Me.RecordsetClone
  5. If Me![ContentsVerified] = True Then
  6. Cancel = true
  7. 'You can also use:
  8. 'Me.Undo
  9. Else
  10. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  11. End If
  12. rs.Close
  13. Set rs = Nothing
  14. End Sub
  15.  
Then for the subform if again we are looking for the checkbox in the main form then you would call it up as a parent of the subform. If the subforms records have their own lock then remove the parent item.:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim rs As Recordset
  3. 'When a recordsets Clone is created it is automatically opened at the current record being viewed (see the help file in a VBA window for more info).
  4. Set rs = Me.parent.RecordsetClone
  5. If Me.parent![ContentsVerified] = True Then
  6. Cancel = true
  7. 'You can also use:
  8. 'Me.Undo
  9. Else
  10. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  11. End If
  12. rs.Close
  13. Set rs = Nothing
  14. End Sub
  15.  
Jun 4 '07 #7

Denburt
Expert 100+
P: 1,356
I am also using an On Load event to disable the mousewheel and get the same error for it

"The expression On Load you entered as the event property setting produced the following error: Method or Data member not found...
This sounds like a separate issue and should have a separate thread started. I would comment it out until you resolve one issue then move on to the next.
Jun 4 '07 #8

P: 12
I ran the code as posted and received the following error:

Run-time error '13':

Type mismatch

and the vb debugger highlights the code:

Set rs = Me.RecordsetClone

I commented out the On Load and On Unload events relating to the scroll wheel code before doing this just to be sure the two did not conflict. Run-time error '13' has a quite alot of causes, so I'm a little usure of where to go from here.

Further background: the form 'frmReceiving' is a very basic child form that is connected to the master form by a long integer autonumber (created in the master form). The RMA number (the master forms primary key) is autogenerated and used as a reference for all other forms and tables, including frmReceiving. Not sure if the integer thing has has any effect here?

If you have any further ideas that would be excellent. This is the last piece of code I have to write and this application is finished.

If you feel I'll fair better in another forum that is also fine by me, it is an Access application that I am working on in the end. Thanks again for everyone's help... any ideas?
Jun 4 '07 #9

Denburt
Expert 100+
P: 1,356
it is an Access application that I am working on in the end.
I am still unsure if you are using Visual basic with MS Access as a database or if you are creating your foms in MS Access.
Jun 5 '07 #10

P: 12
I am still unsure if you are using Visual basic with MS Access as a database or if you are creating your foms in MS Access.
My Bad, I am creating my forms in Access 2003 and trying to create a record lock checkbox control with VBA.
Jun 5 '07 #11

Denburt
Expert 100+
P: 1,356
Are you familiar with compiling your project? I do this almost as often as I save mine. I think you may find something somewhere else that is off as the recordsetclone should work as I posted.

In the VBA window look at the menu Click Debug then Compile (I hold the alt key then press d Then press L very quick and convenient. Try that and if the recordsetclone is still offending comment it out temporarily and see if there are any other errors.
Jun 5 '07 #12

P: 12
I compile regularily too (thanks for the shortcut). So here is what I did:

Compiled the code

I commented out

'Set rs = Me.RecordsetClone
and
'rs.Close

The code will now run with no errors, however when I edit a record and then go to lock it I can check off the box, but when i close the form it tells me "cannot save this record at this time". Also, if I open the form to an unlocked record, check the box to lock the record and close it, the check disappears when i reopen the form to that record.

The subform code you gave me works like a charm...checkbox checked Subform is locked up tight, uncheck the box, able to edit with no trouble.

Not sure why the RecordetClone won't work...
Jun 5 '07 #13

Denburt
Expert 100+
P: 1,356
Hmmm where was my head at yesterday... I just reread my code and realized that I didn't even use the recordset so it shouldn't be an issue. I made a few slight changes and it should be O.K.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If Me![ContentsVerified] = True Then
  3. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  4. Cancel = True
  5. 'You can also use:
  6. 'Me.Undo
  7. End If
  8. End Sub
Jun 5 '07 #14

blyxx86
100+
P: 256
This in itself is very useful information. I'm going to be using it within my own projects from now as I find it to solve a lot of problems. I had created code to keep records of what had changed and who and from where the changes were made (net logon, computer name, access logon, etc..) but now I can just do this to prevent changes. yay! this would also remove the possibility of accidentally changing a serial number or some other field that a change/mistake might not be caught.

Thanks for asking this question!
Jun 5 '07 #15

P: 12
We are getting closer to the answer here. I used the code you posted and am still having the problem that if i open a record that is unlocked, lock it by checking the box, then try to exit the record or move to the next record it will tell me

"Before attempting changes please verify that the verified contents check box is unchecked."

So as a consequence the checkbox is being included in the items to be locked, controlled by itself. The checkbox won't attach to a specific record if i make it unbound. Is there any way that i can tag the checkbox so that it is omitted from the record lock code?

Also, something else that is interesting, when i click on the subform with the record locked i cannot change any of the records, they are frozen and i immediately get the "Before attempting changes please verify that the verified contents check box is unchecked." routine...

but on the main form i can modify the fields and it isn't until i get to leaving the record or clicking on a subform that i get the notification and the Me.Undo takes effect and reverts the entry to its prior entry...

The way the subform behaves is ideal at this point, it is just the main form that needs a good talking to...Thanks again for all of your help!
Jun 5 '07 #16

Denburt
Expert 100+
P: 1,356
Yes I think we are getting close try this and see if you find any issues, it seems to work fine on this end.

Main Form
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim ChckBool As Boolean
  4.  
  5. Private Sub Form_BeforeUpdate(Cancel As Integer)
  6. If ChckBool = False And Me!ck = True Then
  7. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  8. Cancel = True
  9. 'You can also use:
  10. 'Me.Undo
  11. Else
  12. ChckBool = False
  13. End If
  14. End Sub
  15.  
  16. Private Sub Check54_AfterUpdate()
  17. ChckBool = True
  18. DoCmd.RunCommand acCmdSelectRecord
  19. End Sub
  20.  
Jun 5 '07 #17

FishVal
Expert 2.5K+
P: 2,653
Yes I think we are getting close try this and see if you find any issues, it seems to work fine on this end.

Main Form
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim ChckBool As Boolean
  4.  
  5. Private Sub Form_BeforeUpdate(Cancel As Integer)
  6. If ChckBool = False And Me!ck = True Then
  7. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  8. Cancel = True
  9. 'You can also use:
  10. 'Me.Undo
  11. Else
  12. ChckBool = False
  13. End If
  14. End Sub
  15.  
  16. Private Sub Check54_AfterUpdate()
  17. ChckBool = True
  18. DoCmd.RunCommand acCmdSelectRecord
  19. End Sub
  20.  

Why not so
Set in control properties Tag="Protected" for each control you want to lock/unlock.
Run the following sub on AfterUpdate event in locking CheckBox and Current event in the form if the CheckBox is bound to table field (and this should be so if you want to keep a record locked until it will be unlocked by user)

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub chkLock_AfterUpdate()
  3.     LockUnlock
  4. End Sub
  5.  
  6. Private Sub Form_Current()
  7.     LockUnlock
  8. End Sub
  9.  
  10. Private Sub LockUnlock()
  11.  
  12.     On Error Resume Next
  13.     For Each ctrl In Me.Controls
  14.         If ctrl.Tag = "Protected" Then ctrl.Locked = Me.chkLock
  15.     Next
  16.  
  17. End Sub
  18.  
  19.  
Simple and works. :)

PS. Obviously, if you want to lock controls in subform replace "Me.Controls" to "Me![Subform Name].Form.Controls"
Jun 5 '07 #18

P: 12
This combination works like magic:

Expand|Select|Wrap|Line Numbers
  1. 'Declaration
  2.  
  3. Option Compare Database
  4. Option Explicit
  5. Dim ChckBool As Boolean
  6. ____________________________________
  7. 'The main form
  8.  
  9. Private Sub Form_BeforeUpdate(Cancel As Integer)
  10. If ChckBool = False And Me![ContentsVerified] = True Then
  11. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  12. Cancel = True
  13. Me.Undo
  14. Else
  15. ChckBool = False
  16. End If
  17. End Sub
  18. ____________________________________
  19. 'The checkbox 
  20.  
  21. Private Sub ContentsVerified_AfterUpdate()
  22. ChckBool = True
  23. DoCmd.RunCommand acCmdSelectRecord
  24. End Sub
  25. ______________________________________
  26. 'Any Subforms
  27. Private Sub Form_BeforeUpdate(Cancel As Integer)
  28. Dim rs As Recordset
  29. If Me.Parent![ContentsVerified] = True Then
  30. MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
  31. Cancel = True
  32. 'You can also use:
  33. Me.Undo
  34. Else
  35. 'An optional and temporary reminder to everyone to lock the record after editing the subform
  36. MsgBox "Remember to lock the record when you have finished editing it.", vbOKOnly
  37. End If
  38. End Sub
  39.  
Thanks ever so much Denburt, this bit of code did the trick. I will test it out over the next day or two and post back any news that I have. Once again thank you for your time, this is one useful feature that many databases could benefit from. I would also like to thank everyone else who posted a response to my question and got me thinking. Talk to you soon!
Jun 6 '07 #19

P: 12
Why not so
Set in control properties Tag="Protected" for each control you want to lock/unlock.
Run the following sub on AfterUpdate event in locking CheckBox and Current event in the form if the CheckBox is bound to table field (and this should be so if you want to keep a record locked until it will be unlocked by user)

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub chkLock_AfterUpdate()
  3.     LockUnlock
  4. End Sub
  5.  
  6. Private Sub Form_Current()
  7.     LockUnlock
  8. End Sub
  9.  
  10. Private Sub LockUnlock()
  11.  
  12.     On Error Resume Next
  13.     For Each ctrl In Me.Controls
  14.         If ctrl.Tag = "Protected" Then ctrl.Locked = Me.chkLock
  15.     Next
  16.  
  17. End Sub
  18.  
  19.  
Simple and works. :)

PS. Obviously, if you want to lock controls in subform replace "Me.Controls" to "Me![Subform Name].Form.Controls"
I will try this piece of code out as well. Thanks for the idea, and I'll let you know if any wacky stuff happens.
Jun 6 '07 #20

Denburt
Expert 100+
P: 1,356
This combination works like magic:

'Declaration

Option Compare Database
Option Explicit
Dim ChckBool As Boolean
____________________________________
'The main form

Private Sub Form_BeforeUpdate(Cancel As Integer)
If ChckBool = False And Me![ContentsVerified] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
Me.Undo
Else
ChckBool = False
End If
End Sub
____________________________________
'The checkbox

Private Sub ContentsVerified_AfterUpdate()
ChckBool = True
DoCmd.RunCommand acCmdSelectRecord
End Sub
______________________________________
'Any Subforms
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset
If Me.Parent![ContentsVerified] = True Then
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
Cancel = True
'You can also use:
Me.Undo
Else
'An optional and temporary reminder to everyone to lock the record after editing the subform
MsgBox "Remember to lock the record when you have finished editing it.", vbOKOnly
End If
End Sub

Thanks ever so much Denburt, this bit of code did the trick. I will test it out over the next day or two and post back any news that I have. Once again thank you for your time, this is one useful feature that many databases could benefit from. I would also like to thank everyone else who posted a response to my question and got me thinking. Talk to you soon!
Great glad its working let us know how it turns out.

Fish your method may be a viable alternative and I am glad you posted it I am sure that it can come in handy.
Jun 6 '07 #21

P: 12
I have tested both codes and here is what I found.

Denburt’s code works well and can be used as a blanket record lock where all records in a form or subform have a lock applied to them.

Fish’s code also works well and can be used when you want to lock some, but not all controls in a record.

Two distinct flavours of programmatic record locking, I love it! Neither chunks of code interfere with my On Load or On Unload events either. I will likely find a use for both techniques in my current and future applications. Thanks again to all who helped out.

Evan
Jun 7 '07 #22

Post your reply

Sign in to post your reply or Sign up for a free account.