Listing the group from a Workgroup file

lryckman · January 7th, 2008, 10:49 AM

Hello to all!

I know this can be done...but I am stumbling on it.
I am trying to add some code to an procedure that will be dependant on the user group. I already have Access finding out the Windows user ID, but now I need it to tell me what groups they belong to within the workgroup file that is being currently used.

Any ideas??

Thanks!

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · January 7th, 2008, 03:13 PM

What is the workgroup file?

mmcdonal

Look it up at: http://wrox.books24x7.com

lryckman · January 7th, 2008, 03:40 PM

Sorry...I should have explained it a little better.
It is the User-level workgroup .mdw file.
I have Access telling me the user ID for the Windows log in and for the workgroup file user log in, but I am wanting it to also tell me what groups the person belongs to.

(I am wanting to use an IF statement to put an exception to the On Load event of a form if the user is a member of one of the groups I specify.)

Thanks!

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · January 7th, 2008, 03:53 PM

Well, I think the User Level Security wizard is probably the way to go since the username maintained in the mdw isn't the same as the one you are retrieving. Have you tried that?

mmcdonal

Look it up at: http://wrox.books24x7.com

lryckman · January 7th, 2008, 04:23 PM

I'm not sure that I understand how the wizard will help me. I already have the .mdw file created and all the user accounts and groups are created already too. All the accounts have been assigned as members to the groups I created, and all permissions were assigned at the group level.

Here is how I have the users launching the database so they use the workgroup file:
The user launches the initial front end. There are several different buttons for the different databases available. When the user clicks on the one they wish to use, it launches the correct database and the workgroup file for them. Here is the code behind one of them for example:

Code:

Private Sub cmdECNandReleaseDatabase_Click()

Dim UserNameStr As String
UserNameStr = UserNameFunction

Const ACCESSEXE = "msaccess.exe"
Const FileName = "O:\Database\DataStorage\MASTERECNDatabase.mde"
Const SECUREWRK = "O:\Database\DataStorage\SecureDBWorkgroup.mdw"

Dim strFilePath As String, varAppID As Variant, strShell As String

On Error GoTo err_cmdWrkOpen
strFilePath = SysCmd(acSysCmdAccessDir)

If Len(Dir(FileName)) > 0 And Len(Dir(SECUREWRK)) > 0 Then

  strShell = strFilePath & ACCESSEXE & " " & FileName & " " & _
   "/WRKGRP " & Chr(34) & SECUREWRK & Chr(34) & _
   "/USER " & Chr(34) & UserNameStr & Chr(34)
  varAppID = Shell(strShell, vbNormalFocus)


Else
  MsgBox "Problem Opening Your Application. Contact your DBA", vbCritical
End If

DoCmd.Quit acQuitSaveAll
exit_cmdWrkOpen:

Me.ActiveControl.Name & "_Click"

  Exit Sub

err_cmdWrkOpen:
  Select Case Err.Number
    Case vbObjectError + 1
      Resume Next
    Case Else
      MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical
  End Select
  Resume exit_cmdWrkOpen

End Sub

The actual database they work in is owned by my user ID and the Admin has no permissions to any objects...that is how I am enforcing them to use this front (launching) database.

The UserNameFunction is a separate module that determines the Windows Log-in...so in actuality, I set it up so that the Windows log-in user id and workgroup log-in id are always the same.

Since I know the log-in ID...now I am wanting it to do something like a 'look-up' within the workgroup information file so that it takes the user id and looks to see what groups he/she is a member of. Then at the very beginning of the On Load event I can use a statement something like...
IF groupname Like "*Coordinator*" then Exit Sub
to make it bypass the remaining code of the event.

Make any sense?

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mmcdonal · January 7th, 2008, 04:26 PM

My understanding is that the security system allows you to set these permissions within the security environment, so no need for additional code. Have you set permissions to any objects based on group membership?

mmcdonal

Look it up at: http://wrox.books24x7.com

lryckman · January 7th, 2008, 04:41 PM

I have set the object permissions...but I don't think I can actually do what I am wanting with out placing an exception in the code. Here is the On Load event code...

Code:

Private Sub Form_Load()

On Error GoTo errLoad

Dim DateProcessed As Boolean

    DoCmd.Maximize

    DateProcessed = True

    [19-DatePrintsProcessed].Enabled = True

    [19-DatePrintsProcessed].visible = True

    ' This requirements checks to make sure the ECRN has not been processed
    [19-DatePrintsProcessed].SetFocus

    If InStr([19-DatePrintsProcessed].Text, "/") > 1 Then

        Customer.SetFocus

        [19-DatePrintsProcessed].Enabled = False

        [19-DatePrintsProcessed].visible = False

        MsgBox "You may not make any modifications to this ECR/N since it" & _
        " has been processed already.", vbOKOnly, "No Modifications Allowed"

        DoCmd.Close

        Exit Sub

    End If

    [1-CCNumber].visible = True

    [1-CCNumber].Enabled = True

    ' This requirements checks to make sure the ECRN has not been processed
    [1-CCNumber].SetFocus

    [19-DatePrintsProcessed].Enabled = False

    [19-DatePrintsProcessed].visible = False

    If InStr([1-CCNumber].Text, "(AutoNumber)") = 1 Then

        Dim vbAnswer As String

        Customer.SetFocus

        [1-CCNumber].Enabled = False

        [1-CCNumber].visible = False

        ReportCancel = False

        vbAnswer = MsgBox("You are about to assign a new CCN number." _
        & vbCrLf & "Would you like to continue?", vbYesNo, _
        "New CCN Number")

            If vbAnswer = 6 Then

                Exit Sub

            End If

            If vbAnswer = 7 Then

                ReportCancel = True

                DoCmd.Close acForm, "ECN2008Main"

                OpenForm_FX "MainMenu"

                Exit Sub

            End If

    End If

    Customer.SetFocus

    [1-CCNumber].Enabled = False

    [1-CCNumber].visible = False

    Exit Sub

errLoad:

    Select Case Err.Number

        Case 2501

            DoCmd.CancelEvent

            Exit Sub

        Case 3112

            MsgBox "You do not have sufficient permissinos to access this option." & _
            vbCrLf & "Please contact Laura Ryckman at the Connersville facility" & _
            vbCrLf & "if you need permissions to access this object."

        Case Else

            MsgBox "An error has occured.  Please contact Laura Ryckman at the" & _
            vbCrLf & "Connersville facility and provide her with the following information:" & _
            vbCrLf & "Error Number: " & Err.Number & _
            vbCrLf & "Error Description: " & Err.Description & _
            vbCrLf & "Your data may be lost if you close this form.", vbCritical, "Application Error"

    End Select

End Sub

In generic terms...what this does is keeps a user from making any modifications to a record that has already been processed by closing the form and not even giving the opportunity to read/modify the record. There is one small group of users though that needs to be able to modify one field of the record even after it was processed, so if I could figure out how to get it to tell me the group name, I was thinking of doing this...

Code:

Private Sub Form_Load()

On Error GoTo errLoad

If GroupName Like "*Coordinator*" Then

    Exit Sub

End if

Dim DateProcessed As Boolean

    DoCmd.Maximize

    DateProcessed = True

    [19-DatePrintsProcessed].Enabled = True

...and so on like above.

This way it still enforces the On Load event, but not for the few people in the special group that needs to make modifications.

I know this is a little confusing...I hope I am explaining this to where it is understandable.

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!

mariahayek · January 12th, 2008, 06:43 AM

Thanks lryckman,very helpful..

-----------------------------------------------------------------------------------------------------------------------------------------------------------------
http://coco4info.com
http://supportinglinks.com/