Help with hiding record base on Chk box

[simpsondm]

Hello, I hope someone can help me I'm pretty new to this. What I'm trying to do and have not been able to is, I would like to put a check box on my main admin form that shows all records and based on a chk box having a check mark or not (True/False) it will hide that record.

Example record 2, check box is checked this record should not be visable to anyone other then people who can access the table but not visable through the form.

Please help if you can.
Thanks
David

 

[Eupher]

I'm not an expert on MS Access security, but here goes...

First, how familiar are you with creating and setting User Level Security in Access? What you want to do is possible, but will involve the implementation of security. There is a wizard that will automate much of it.

So basically you need to test whether the current user is a member of the Admins group, and change the record source of your form accordingly. However, there is a big gotcha here. In your post you seemed to imply that certain members would have access only to the form, and Admins would have access to the form and the table. This won't work - in order to view and/or manipulate the data via a form, the user must also have the necessary permissions for the form's record source. So if the user can see the data in the form, he/she can also open the table. You can, of course, limit what the user is able to do to the data, i.e. read only, add records, update records, etc.; and you can take measures to hide the database window. But for the form to work properly, you will need to assign the same level of permission to the underlying object, i.e. the table and/or query.

If you still wish to pursue this route, here's one way to achieve it.

1) Create a module and paste in the following code:

Function GetCurrentUserGroups() As String
Dim wrk As Workspace
Dim usr As User
Dim grp As Group
Dim strGroups As String

On Error Resume Next

Set wrk = DBEngine.Workspaces(0)
Set usr = wrk.Users(Application.CurrentUser)

For Each grp In usr.Groups
  strGroups = strGroups & grp.Name & ","
Next grp

GetCurrentUserGroups = Left(strGroups, InStrRev(strGroups, ",") - 1)
    
End Function

Function InGroup(ByVal strGroup As String) As Boolean
  Dim varGroups As Variant
  Dim i As Integer

  varGroups = Split(GetCurrentUserGroups(), ",")
  For i = 0 To UBound(varGroups)
    If UCase(varGroups(i)) = UCase(strGroup) Then
      InGroup = True
      Exit For
    End If
  Next i

End Function

Note: The code above is NOT my code, it was posted by VBSlammer in Thread705-666914. But I tried it out, and it works. Kudos to VBSlammer!

2) Create an event procedure in the On Open event of your form:

If InGroup("Admins") Then
    Me.RecordSource = "qrySecLevel1"
    Else
        Me.RecordSource = "qrySecLevel2"
        Me![UserOnly].Visible = False
End If

...where UserOnly is the name of the Yes/No checkbox on your form, presumably bound to a Yes/No field in your table. If you want users (that are not Admins) to be able to add records, since the checkbox field will be hidden, you should probably set the default value of your Yes/No field to True.

qrySecLevel1 is a query that includes all records from your table. qrySecLevel2 is a query that returns all records from your table where the Yes/No field is set to True.

3) Set up your security and assign appropriate permissions to your users/groups for the form and related data objects. This code assumes your administrators group is named "Admins" (that's an Access default).

HTH...

Ken S.