[color="green"]'---------------------------------------------------------------------------------------

' Procedure : SecurityEnabled

' DateTime  : 2/20/2006 14:09

' Author    : Dane Miller

' Purpose   : To disable controls on forms and reports.

' Arguments : ObjectName is the Name of the form in question.

'             Object_type is the type of object "Form" or "Report"

' Returns   :

' Modification History:

' Who        When           WHY

'---------------------------------------------------------------------------------------[/color]



Public Function SecurityEnabled(TheUser As String, ObjectName As String, ByVal Object_Type As String)



Dim cnn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim SQL As String, Arr_Index() As String

Dim RecCount As Integer, c As Integer

Dim frm As Object

Dim Frm_ctl As Object, Rpt_ctl As Object



'On Error GoTo handler

On Error Resume Next



Set cnn = CurrentProject.Connection

Set rst = New ADODB.Recordset



SQL = "SELECT tbl_ObjectControls.ControlName, tbl_UserAccess.UserName, tbl_ObjectControls.ObjectName " & _

"FROM (tbl_ObjectControls INNER JOIN (tbl_Groups INNER JOIN tbl_ControlsToDisable ON tbl_Groups.AccessTypeID = " & _

"tbl_ControlsToDisable.AccessTypeId) ON tbl_ObjectControls.ControlId = tbl_ControlsToDisable.ControlID) INNER JOIN " & _

"(tbl_UserAccess INNER JOIN tbl_AssignUsersToGroups ON tbl_UserAccess.MyID = tbl_AssignUsersToGroups.MyID) " & _

"ON tbl_Groups.AccessTypeID = tbl_AssignUsersToGroups.AccessTypeID " & _

"WHERE (((tbl_UserAccess.UserName)='" & TheUser & "') AND ((tbl_ObjectControls.ObjectName)='" & ObjectName & "'));"



rst.Open SQL, cnn, adOpenStatic

RecCount = rst.RecordCount



If RecCount > 0 Then

    ReDim Arr_Index(RecCount) As String ' get the maximum size of the array.

    ' pass the controls names in the table to the Array

    Select Case Object_Type

        Case "Form"

            Set Frm_ctl = Forms(ObjectName).Controls

            With rst

                For c = 1 To RecCount

                    Arr_Index(c) = rst.Fields("ControlName")

                    .MoveNext

                    Frm_ctl(Arr_Index(c)).Enabled = False

                Next

            End With

        Case "Report"

            MsgBox UCase(TheUser) & " you do not have permission to view this report."

            DoCmd.Close acReport, ObjectName

    End Select

End If



exit_handler:

    Exit Function



handler:

    Select Case Err.Number

        Case 2465 Or 438

            Resume Next

        Case 2450

            MsgBox Err.Description

            MsgBox "Please recheck the spelling of the object_type in the function SecurityEnabled on your form or report." & Chr(13) & _

                    "The form or report will open but no contols will be affected till the error is corrected."

        Case 2451

            MsgBox Err.Description & Chr(13) & "Please recheck the spelling of the name of your form or report in the function SecurityEnabled."

        Case Else

            MsgBox "Error Number  " & Err.Number & Chr(13) & "Error Description " & Err.Description

            Resume exit_handler

    End Select





'clean up



rst.Close

Set rst = Nothing

cnn.Close

Set cnn = Nothing



End Function