Programatically lock Access 2003 records

[egaffney]

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

[cmrhema]

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)

[MSeda]

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:

Private Sub Form_current
dim ctl as object

For Each ctl In me.Controls
			If me.lockrec = true then
 		    ctl.locked = true
			else
			ctls.locked = false
			End If
Next

End Sub

this is just a vague example if you need help applying it let us know.

[egaffney]

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: “PartsTableRece ivingSubform” which I also need to lock

The record lock checkbox is called “ContentsVerifi ed” 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

[MSeda]

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.

Private Sub Form_current
dim ctl as object
For Each ctl In me.Control
if ctl.tag <> "locked" then
   if me.ContentsVerified = true then
	   ctl.locked = true 
	   else
	   ctl.locked = false
	   end if
   end if
Next

additionally you want to call the above procedure in the contents verified after update event:

Private Sub ContentsVerified_AfterUpdate()
Form_current
End Sub

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

[egaffney]

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!

[Denburt]

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.
[CODE=vb]Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim rs As Recordset
'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).
Set rs = Me.RecordsetClo ne
If Me![ContentsVerifie d] = True Then
Cancel = true
'You can also use:
'Me.Undo
Else
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
End If
rs.Close
Set rs = Nothing
End Sub
[/CODE]
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.:
[CODE=vb]Private Sub Form_BeforeUpda te(Cancel As Integer)
Dim rs As Recordset
'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).
Set rs = Me.parent.Recor dsetClone
If Me.parent![ContentsVerifie d] = True Then
Cancel = true
'You can also use:
'Me.Undo
Else
MsgBox "Before attempting changes please verify that the verified contents check box is unchecked.", vbCritical
End If
rs.Close
Set rs = Nothing
End Sub
[/CODE]

[Denburt]

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.

[egaffney]

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.RecordsetClo ne

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?

[Denburt]

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.

[egaffney]

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.

[Denburt]

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.

[egaffney]

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

Compiled the code

I commented out

'Set rs = Me.RecordsetClo ne
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...checkbo x checked Subform is locked up tight, uncheck the box, able to edit with no trouble.

Not sure why the RecordetClone won't work...

[Denburt]

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.

[CODE=vb]Private Sub Form_BeforeUpda te(Cancel As Integer)
If Me![ContentsVerifie d] = 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
End If
End Sub[/CODE]

[blyxx86]

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!