Locking Records?

[dalesellers]

I’m new to access but I have created a table with a form linked to it. It shows one record per page. I just want to be able to lock and unlock individual records in the form by using a checkbox. This will prevent me from accidently making a change to an old record.

For example, when the checkbox is unchecked. the current record on the form can still be edited. When the checkbox is checked, the record can only be viewed, not edited. I still need to be able to navigate through the locked record and create a duplicate from the locked record though. Does anyone know a simple way to do this? My guess that it has to do with the AfterUpdate event? Thanks for any help.

Dale

 

[wazz]

you may have considered this...

add a yes/no field to the table called AllowEdits. using the on current event you could enable (or unlock) all the controls if the check box is true and disable (or lock) all the controls -- except the check box -- if the check box is false.

or

two separate forms, based on two separate queries, one form with all records where AllowEdits = True, one form with all records where AllowEdits = False.

there's probably an easier/better way...

 

[boblarson]

The easiest would be to add a tag to each of the controls you want to lock (remembering to not put this tag on the checkbox or it will get locked and you would not be able to unlock) and then, as mentioned above, on the ON CURRENT event, place this:

Dim ctl As Control

For each ctl in Me.Controls
   If ctl.Tag = "WhateverYouPutInTheTagProperty"
       ctl.Locked = True
   End If
Next ctl
    YourCheckBoxName = False

and then in the check box click event:

Dim ctl As Control

   For each ctl in Me.Controls
      If ctl.Tag = "WhateverYouPutInTheTagProperty"
         ctl.Locked = Not ctl.Locked
      End If
   Next ctl

The code that I've put in the checkbox click event will change the locked status back and forth, so if you check the box the controls will unlock and then if you click the checkbox again it will lock them, just in case you wanted to lock it, even though going to the next, or previous, record will lock it for you.

 

[dalesellers]

Thanks - I'll try it out and see how it works

 

[dalesellers]

I’ve done some minor coding in VB, but I’m just a newbie. Just a couple basic questions - I have Access XP. On the event tab of the property boxes for all of my controls, I can’t seem to find “On Current” event anywhere. I only see Before Update, After Update, On Enter, etc. I have one db record per form, not several – does this have something to do with “On Current” event not being available? Where can I find “On Current” event so I can get this code in there?

Also, I’m new to tags. Does it matter what the tag says? What is a simple example of what a tag might look like and how do I get it into the code? I’m getting lots of mixed messages when I search for this info. Thanks for any help.

Dale

 

[ansentry]

Open the Form in design view and then click Alt+Enter and you will see the properties for the form.

Now select Event and there it is at the top of the list.

 

[dalesellers]

John –

I did in and for some reason it's not showing up. Here's the list:

On Click
On Dbl Click
On Mouse Down
On Mouse Move
ON Mouse Up

That’s it – any ideas on what might be going on?

Thanks-
Dale

 

[ansentry]

Have a look at the attached screen dump, if this does not make sence or you still can get it to work, post a copy of you db (with just this form and it data scource) and I will have a look to-night (about 1 hour till I shut down)

 

[boblarson]

The On Current event is one of the FORM's properties and not one of the control's. You need to make sure the Form is selected and not the details section or any of the controls. There is a combo box on the tool bar that will let you select any of the objects for the form you are open on, which if you select FORM from that list will get you to the right place.

As for the tag, you click on a control and in the properties there will be a property called "tag" which you can then type in anything you want. Replace my code that says, "WhateverYouPutInTheTagProperty" with whatever you type in that spot. Just fill out the tag for the controls you want to be locked, so the code will then bypass any that don't have that tag.

 

[dalesellers]

I finally found the On Current event for the form. Although I still seem to have a problem.

In the On Current event for the form, here's what I have now:

Private Sub Form_Current()
Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "checktag"
       ctl.Locked = True
   End If
Next ctl
    check1 = False
End Sub

I created a check box and set the Control Source to a yes/no field (Allow Edits) that I created in the table.

On the On Click event for the check box, I have this:

Private Sub check1_Click()
   For Each ctl In Me.Controls
      If ctl.Tag = "checktag"
         ctl.Locked = Not ctl.Locked
      End If
   Next ctl
End Sub

Here's the tag I entered in a couple of the combo boxes on the form (in the properties box, Other tab, Tag field):

checktag

When I try to open the form, A little box pops up saying:

Complie error:
Syntax error

It gives the option of OK or Help. In the code, this is what appears:

Private Sub Form_Current() (this line is higlighted in yellow with an arrow pointed to it)
Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "checktag" (all the text in this line is red)
       ctl.Locked = True
   End If
Next ctl
    check1 = False
End Sub

Any ideas on what I might have done to screw it up?

Thanks,
Dale

 

[ansentry]

Private Sub Form_Current()
Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "checktag" [B]then[/B]
       ctl.Locked = True
   End If
Next ctl
    check1 = False
End Sub

Private Sub check1_Click()
   For Each ctl In Me.Controls
      If ctl.Tag = "checktag" [B]then [/B]
         ctl.Locked = Not ctl.Locked
      End If
   Next ctl
End Sub

 

[dalesellers]

Ok, I got this in there:

Private Sub check1_Click()
   For Each ctl In Me.Controls
      If ctl.Tag = "checktag" Then
         ctl.Locked = Not ctl.Locked
      End If
   Next ctl
End Sub

And this:

Private Sub Form_Current()
Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "checktag" Then
       ctl.Locked = True
   End If
Next ctl
    check1 = False
End Sub

But I get pop up with this:

Run-time error '438':
Object Doesn't suport this property or method.

At bottom of box it give me option to end, debug, or help. When I go to bebug, it takes me here:

Private Sub Form_Current()
Dim ctl As Control

For Each ctl In Me.Controls
   If ctl.Tag = "checktag" Then
       ctl.Locked = True (highlighted in yellow with arrow)
   End If
Next ctl
    check1 = False
End Sub

Any ideas on what I have to do to get this fixed.

Thanks,
Dale

 

[ansentry]

Have a look at the attached sample.

The controls coloured Yellow are locked until you click the tick box. You can then edit them. When you remove the tick they are locked again.

If my works and you still can't get yours to work post a copy and someone will have a look at it.

 

[boblarson]

What controls do you have the tag on? Because I can get it to work for me too.

 

[boblarson]

The only thing is, if you have the checkbox on the Main form and the subform controls are the ones needing locking, you will have to refer to the controls in the checkbox code like this:

   For Each ctl In Forms!YourMainFormName.YourSubformName.Form.Controls
      If ctl.Tag = "checktag" Then
         ctl.Locked = Not ctl.Locked
      End If
   Next ctl

making sure to change the part that says 'YourMainFormName' with your actual main form name and the part that says 'YourSubformName' should be the name of the control that contains the subform on the main form.

Not sure why you're getting an error on the ctl.Locked line. As noted by Ansentry, you may have to post your db (or as much as needed for this stuff) for us to take a look.

 

[dalesellers]

I have the tag on all of the controls throughout the form (except checkbox and navigation controls). Fortunately, there are no subforms so I don't have to mess with that aspect of coding.

Here is a link to the database if you want to take a look. I upoladed it to a directory on our site. The one (and only one available) form that is there is the CIF form. A apprecate you taking a look. You should be able to get the file - if you can't , please let me know.

http://www.lifeleap.org/database.html

Thanks,
Dale

 

[GaryPanic]

on your table have a yes no option
so lets call it xxlocked
with a default value of no
now on your form open up in desihgn and select the last field on your table selection (which should be this yes/no option)

put in on your form somewhere

now a little bit of coding in a couple of places
type the code in the after update properties of the tick box and
on the on current and on load of your form

(their are smarter ways of doing this - but i've just done this myself and this works a treat)


If xxlocked = True Then fieldnames.Enabled = False
i presume their is going to be more than 1 field so just type the above for each field name and then the reverse so when its unticked it will free these up as below
If xxlocked = False Then Combo35.Enabled = True
now copy all of this you have just type and paste it on the on load or on cuurrent properties of your form - otherwise when your form opens it doesn't know what to do - it will lock at theis tick box and either lock it or enable it

 

[boblarson]

Gary:

Your problem with my code is that you put "checktag" on a label (ZipCode under Alternate Address) and that isn't supported as you can't lock a label.

 

[boblarson]

You also put the tag on the labels "Check Information" and "Credit Card Information" and "Service Information"

 

[dalesellers]

Although I got the form cleaned up, the only code I seem to be able to get to work is from GaryPanic. The records seem to lock just fine. Only issue is I seemed to have lost the ability to use the “find record” button that installed from Command Button option. Any ideas on how to keep this functional even though most of the records are locked in the database? Here’s the code from the button:

Private Sub Find_Click()
On Error GoTo Err_Find_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Click:
    Exit Sub

Err_Find_Click:
    MsgBox Err.Description
    Resume Exit_Find_Click
    
End Sub

Thanks for any insights -
Dale