Locking Records in MS Access 2003

[greeni91]

I am currently producing a database to check the problems we have with parts are my work. I have set up an input form and linked tables to it. I have 6 areas on my sheet I want to lock when I click on a checkbox at the side of my form. One checkbox needs to lock three fields of information. I am currently using this code in VBA to lock the fields:

Private Sub Complete1_Click()
 If Me.Complete1 = True Then
   Me.IFAWhat1.Enabled = False
   Me.IFAWho1.Enabled = False
   Me.IFAWhen1.Enabled = False
 Else
    Me.IFAWhat1.Enabled = True
   Me.IFAWho1.Enabled = True
   Me.IFAWhen1.Enabled = True
 End If
End Sub

Private Sub Complete1_AfterUpdate()
 If Me.Complete1 = True Then
   Me.IFAWhat1.Enabled = False
   Me.IFAWho1.Enabled = False
   Me.IFAWhen1.Enabled = False
 Else
   Me.IFAWhat1.Enabled = True
   Me.IFAWho1.Enabled = True
   Me.IFAWhen1.Enabled = True
 End If
End Sub

Private Sub Complete2_Click()
 If Me.Complete2 = True Then
   Me.IFAWhat2.Enabled = False
   Me.IFAWho2.Enabled = False
   Me.IFAWhen2.Enabled = False
 Else
   Me.IFAWhat2.Enabled = True
   Me.IFAWho2.Enabled = True
   Me.IFAWhen2.Enabled = True
 End If
End Sub

Private Sub Complete2_AfterUpdate()
 If Me.Complete2 = True Then
   Me.IFAWhat2.Enabled = False
   Me.IFAWho2.Enabled = False
   Me.IFAWhen2.Enabled = False
 Else
   Me.IFAWhat2.Enabled = True
   Me.IFAWho2.Enabled = True
   Me.IFAWhen2.Enabled = True
 End If
End Sub

Private Sub Complete3_Click()
 If Me.Complete3 = True Then
   Me.IFAWhat3.Enabled = False
   Me.IFAWho3.Enabled = False
   Me.IFAWhen3.Enabled = False
 Else
   Me.IFAWhat3.Enabled = True
   Me.IFAWho3.Enabled = True
   Me.IFAWhen3.Enabled = True
 End If
End Sub

Private Sub Complete3_AfterUpdate()
 If Me.Complete3 = True Then
   Me.IFAWhat3.Enabled = False
   Me.IFAWho3.Enabled = False
   Me.IFAWhen3.Enabled = False
 Else
   Me.IFAWhat3.Enabled = True
   Me.IFAWho3.Enabled = True
   Me.IFAWhen3.Enabled = True
 End If
End Sub

Private Sub Complete4_Click()
 If Me.Complete4 = True Then
   Me.PRAWhat1.Enabled = False
   Me.PRAWho1.Enabled = False
   Me.PRAWhen1.Enabled = False
 Else
   Me.PRAWhat1.Enabled = True
   Me.PRAWho1.Enabled = True
   Me.PRAWhen1.Enabled = True
 End If
End Sub

Private Sub Complete4_AfterUpdate()
 If Me.Complete4 = True Then
   Me.PRAWhat1.Enabled = False
   Me.PRAWho1.Enabled = False
   Me.PRAWhen1.Enabled = False
 Else
   Me.PRAWhat1.Enabled = True
   Me.PRAWho1.Enabled = True
   Me.PRAWhen1.Enabled = True
 End If
End Sub

Private Sub Complete5_Click()
 If Me.Complete5 = True Then
   Me.PRAWhat2.Enabled = False
   Me.PRAWho2.Enabled = False
   Me.PRAWhen2.Enabled = False
 Else
   Me.PRAWhat2.Enabled = True
   Me.PRAWho2.Enabled = True
   Me.PRAWhen2.Enabled = True
 End If
End Sub

Private Sub Complete5_AfterUpdate()
 If Me.Complete5 = True Then
   Me.PRAWhat2.Enabled = False
   Me.PRAWho2.Enabled = False
   Me.PRAWhen2.Enabled = False
 Else
   Me.PRAWhat2.Enabled = True
   Me.PRAWho2.Enabled = True
   Me.PRAWhen2.Enabled = True
 End If
End Sub

Private Sub Complete6_Click()
 If Me.Complete6 = True Then
   Me.PRAWhat3.Enabled = False
   Me.PRAWho3.Enabled = False
   Me.PRAWhen3.Enabled = False
 Else
   Me.PRAWhat3.Enabled = True
   Me.PRAWho3.Enabled = True
   Me.PRAWhen3.Enabled = True
 End If
End Sub

Private Sub Complete6_AfterUpdate()
 If Me.Complete6 = True Then
   Me.PRAWhat3.Enabled = False
   Me.PRAWho3.Enabled = False
   Me.PRAWhen3.Enabled = False
 Else
   Me.PRAWhat3.Enabled = True
   Me.PRAWho3.Enabled = True
   Me.PRAWhen3.Enabled = True
 End If
End Sub

The problem I am having is that when I click on the checkbox it locks the fields on ALL records, I am looking for a way to fix this code to be record specific


P.S. I have a field at the top of the form called "ProblemNo" , I was wondering if this could be used as it is connected to my main table and is the "Serial Number" of the problem I am currently looking at...

Thanks in Advance

/Sandy

[missinglinq]

First off, there is no need to have the same code for each checkbox appear in the OnClick event and the AfterUpdate event. One or the other will do!

Secondly, because you're apparently (from your description) doing this on a Continuous or Datasheet View Form, you need to use, for each appropriate control

Me.PRAWhat3.Ena bled = False
Me.PRAWhat3.Loc ked = True

or

Me.PRAWhat3.Ena bled = True
Me.PRAWhat3.Loc ked = False

in order for the formatting not to be obvious on all records, as setting the Enabled Property alone will. Using this combination "locks" the control, but doesn't "gray" it out.

Finally, in order for the formatting, i.e. the "locking" of the record to be record-specific, you need to include this code in the Form_Current event.

In this way, if you move to a record, the corresponding fields will be "locked" or not depending on its checkbox.

Welcome to Bytes!

Linq ;0)>

[NeoPa]

As Linq implied, the controls' attributes don't change per record, they change globally. That is to say there is only one control (for each) whose attributes are the same wherever it is seen on the form (See Why Values in Unbound Form Controls do not Persist). That is why you should follow Linq's advice about running the code in the Form_Current() event procedure too.

Because of this it is a good idea to have the code itself in separate procedures, and invoke them from the various event procedures where they are appropriate.

I would also consider shortening the code as there is little need for so many lines when it can be put more succinctly, without any loss of clarity (readability). As an example I'll post the code for one control :

Private Sub Form_Current()
  Call Handle1(Me.Complete1)
  Call Handle2(Me.Complete2)
  ...
End Sub

Private Sub Complete1_AfterUpdate()
  Call Handle1(Me.Complete1)
End Sub

Private Sub Complete2_AfterUpdate()
  Call Handle1(Me.Complete2)
End Sub

...

Private Sub Handle1(ByVal blnComplete As Boolean)
  Me.IFAWhat1.Enabled = (blnComplete = False)
  Me.IFAWho1.Enabled = (blnComplete = False)
  Me.IFAWhen1.Enabled = (blnComplete = False)
End Sub

Private Sub Handle2(ByVal blnComplete As Boolean)
  Me.IFAWhat2.Enabled = (blnComplete = False)
  Me.IFAWho2.Enabled = (blnComplete = False)
  Me.IFAWhen2.Enabled = (blnComplete = False)
End Sub

...

[greeni91]

Thanks very much to both of you...This method works a treat and my database is now fully funtioning... Can't thenk you enough.

/Sandy

[NeoPa]

Always a pleasure Sandy :)

I'll set the Best Answer for you.