Reference to Tab Control in VBA (Should be easy)

[Wombat]

I'm trying to disable all textboxes and comboboxes for people whose access is not "Admin". For some reason this also disables my Tab Control and I can't figure out how to make it so that non-admins can still look at the different tabs



This is my code (I don't know how to post it fancy)

Private Sub Form_Load()
Dim rs As dao.Recordset
Dim permission As String
Dim ctlTmp As Control
Set rs = CurrentDb.OpenRecordset("Select * FROM qry_UserGroup")
permission = rs!UserPermissions
If permission = "Admin" Then
Forms("frm_EmployeeInformation").AllowEdits = True
Else
Forms("frm_EmployeeInformation").AllowEdits = False
On Error Resume Next
For Each ctlTmp In Forms("frm_EmployeeInformation").Controls
If TypeOf ctlTmp Is ComboBox Or TypeOf ctlTmp Is TextBox Then
ctlTmp.Enabled = False
End If
Next ctlTmp
End If
rs.Close
Set rs = Nothing
End Sub

[Wombat]

Nevermind, it works... I had an older version in a different form with a reference to my mainform that was disabling the tabs. Friday afternoon brainfart. Thanks.

[ssanfu]

Glad you got it working. Here is a little different coding:

Private Sub Form_Load()
   Dim rs As DAO.Recordset
   Dim permission As String
   Dim ctlTmp As Control
   Set rs = CurrentDb.OpenRecordset("Select * FROM qry_UserGroup")
   permission = rs!UserPermissions
   If permission = "Admin" Then
      Forms("frm_EmployeeInformation").AllowEdits = True
   Else
      Forms("frm_EmployeeInformation").AllowEdits = False
      On Error Resume Next

      For Each ctlTmp In Forms("form3").Controls
         With ctlTmp
            Select Case .ControlType
               Case acComboBox, acTextBox
                  .Enabled = False
            End Select
         End With
      Next ctlTmp

   End If
   rs.Close
   Set rs = Nothing
End Sub

[Wombat]

That is a little nicer, thanks. I just copied slightly different versions of this code into about 6 different forms (some forms I want to disable the buttons too). It works, but do you know a more elegant way to implement this?

[ssanfu]

If I have code that is almost the same in several forms, I try and use one sub/function and call it from many places. I think the following will do what you want.
Paste the following code into a standard module. Do Not name the module the same as the sub name!!

Sub EnableDisableControls(frm As Form, pEnableCMD_Buttons As Boolean)
' pIncludeButtons = TRUE -> enable buttons
' pIncludeButtons = False  ->  disable buttons

   Dim rs As DAO.Recordset
   Dim permission As String
   Dim ctlTmp As Control

   Set rs = CurrentDb.OpenRecordset("Select * FROM qry_UserGroup")
   permission = rs!UserPermissions
   If permission = "Admin" Then
      Forms(frm.Name).AllowEdits = True
   Else
      Forms(frm.Name).AllowEdits = False
      On Error Resume Next

      For Each ctlTmp In frm.Controls
         With ctlTmp
            Select Case .ControlType
               Case acComboBox, acTextBox
                  .Enabled = False
               Case acCommandButton
                     .Enabled = pEnableCMD_Buttons
            End Select
         End With
      Next ctlTmp

   End If
   rs.Close
   Set rs = Nothing
End Sub

In each form, in the load event, call the code using:

Private Sub Form_Load()
' TRUE  -> enable buttons
' False  -> disable buttons

   EnableDisableControls Me, FALSE

'or
'   EnableDisableControls Me, TRUE

End Sub

Warning: this is largely untested!!!!

[Wombat]

That's pretty slick. I will try it out on Monday morning and report back.

[Wombat]

ssanfu, this works great except that I have to throw another level of complication on it. Some of the forms are actually subforms, so the reference to them isn't working. how do I send the information of the Parent.frm.Name? But still have it work for the ones that aren't subforms.

[ssanfu]

Not sure.

Try "Me.Parent"??? Use a COPY of your database, just in case....