Easier method for locking records

[kit_sune]

I have some VBA that I wrote a while back when I first started working in Access, and I'm trying to find if there is a better way of doing this.

On my form I have a set of fields that can be displayed as editable or read-only depending on a "Lockout" checkbox for that record.

But it feels like a lot of typing for something so simple. Is there an easier way of doing this? If not, would it be smarter to create a function to do this? As it stands I'm assuming I would have to have this code run when the form loads, when the Lockout box is clicked, and whenever the record is changed just to ensure that the records are displayed as locked or unlocked appropriately.

Here's what I have:

Private Sub Lockout_Click()
If [Lockout] = True Then
   Me![Customer_Text].Enabled = False
   Me![ReqDesc_Text].Enabled = False
   Me![MoreInfo_Text].Enabled = False
   Me![ReqType_Combo].Enabled = False
   Me![PhoneNum_Text].Enabled = False
   Me![WorkedBy_Combo].Enabled = False
   Me![UserID_Text].Enabled = False
   Me![Man#_Text].Enabled = False
   Me![ReqDate_Text].Enabled = False
   Me![CompDate_Text].Enabled = False
   Me![Count_Text].Enabled = False
Else
   Me![Customer_Text].Enabled = True
   Me![ReqDesc_Text].Enabled = True
   Me![MoreInfo_Text].Enabled = True
   Me![ReqType_Combo].Enabled = True
   Me![PhoneNum_Text].Enabled = True
   Me![WorkedBy_Combo].Enabled = True
   Me![UserID_Text].Enabled = True
   Me![Man#_Text].Enabled = True
   Me![ReqDate_Text].Enabled = True
   Me![CompDate_Text].Enabled = True
   Me![Count_Text].Enabled = True
End If
End Sub

Thanks.

 

[nanscombe]

This is off the top of my head but you could try ...

Private Sub Lockout_Click()
' In Form module
' Performs doLockout when Lockout is control clicked
  doLockout
End Sub

Private Sub Form_Current()
' In Form module
' Performs doLockout on entering record
  doLockout
End Sub


Private Sub doLockout()
' In Form module
  Dim ctl as Control

  For Each ctl In [COLOR="Blue"]Me.Controls[/COLOR]

    Select Case [COLOR="Red"]ctl.ControlType[/COLOR]
      Case acTextBox, acComboBox
        ctl.enabled = Not Me.Lockout
    End Select

  Next ctl

End Sub

Me.Controls is a collection which holds all of the controls for the form

ControlType returns what type of control it is.

This could be amended to work for any Form by making a couple of amendments.

Private Sub Lockout_Click()
' In Form module
' Performs doLockout when Lockout is control clicked
  doLockout Me.Form, Me.Lockout
End Sub

Private Sub Form_Current()
' In Form module
' Performs doLockout on entering record
  doLockout Me.Form, Me.Lockout
End Sub

Public Sub doLockout(ByRef theForm as Form, byval isLocked as Boolean)
' In separate shared module
  Dim ctl as Control

  For Each ctl In theForm.Controls

    Select Case ctl.ControlType
      Case acTextBox, acComboBox
        ctl.enabled = Not isLocked
    End Select

  Next ctl

End Sub

 

[kit_sune]

I really appreciate the help - the code worked perfectly - impressive for off the top of your head!

I did try to create a shared module for the Public Sub doLockout bit - I created a separate module for it but I don't know how to make it "Shared" as you suggested. I tried using it this way and it doesn't read it.

I would very much like to enable record locking for any table I may need in the future.

Thanks for your input!

 

[nanscombe]

Creating the module is sufficient. Modules in forms and reports are Private but modules you create by "adding a module" are Public (shared).


Yes,I just added a module and added the code ...

Public Sub doLockout(ByRef theForm As Form, ByVal isLocked As Boolean)
' In separate shared module
  Dim ctl As Control

  For Each ctl In theForm.Controls

    Select Case ctl.ControlType
      Case acTextBox, acComboBox
        ctl.Enabled = Not isLocked
    End Select

  Next ctl

End Sub

I then created a form with a checkbox called Lockout and added the following code

Private Sub Lockout_Click()
  doLockout Me.Form, Me.Lockout
End Sub

And it seems to work Ok.

 

[kit_sune]

Thanks again, I just figured it out. Silly me - I forgot that I renamed the checkbox (I was trying to standardize my naming conventions) so I had to change the code to say "Lockout_Check" when referencing the Lockout checkbox.

Thanks! Works as advertized – you are awesome.

~Kit