Need help with a Survey

[dionicia]

Hi.

I have been working on a general survey that will be applied towards different survey questions in the future. I have it done (I think) for the most part.

The only problem I am running into right now is that before the survey is sent out, I want to set it up to allow someone to go in and prefill in the top part of the survey and then email the form to the person who will take the survey.

In addition, I don't want the person who will take the survey to be able to see anything else but the one form yet I want the people who will be sending these forms out to be able to access other parts of the database.

I'm at a loss of where to go from here to get this accomplished.

I hope this made sense and would appreciate any assistance that can be given.

Thank you.

 

[dionicia]

Oh, I'm still looking through the other threads on here for direction. If anyone knows a really good thread for surveys, I would really appreciate it.

Thank you again.

 

[Dennisk]

If I read your post correctly, our problem is not with surveys but how to apply permissions.
What I do is to assign users to groups within a workgroup file. Then when a user logs in I determine what group they belong to and restrict access to forms and/or data as each form is opened.

 

[dionicia]

Would you be so kind as to elaborate on how to use the permissions please?

I tried to set up permissions using the security wizard and I locked myself out. Luckily I made a back up and was able to use that.

 

[Dennisk]

First you need to execute code everytime the db is opened.
Either use an Autoexec Macro that calls a function
or a hidden form. that call the function GetUsergroup(Username)

The step to take in the code are as follows
1) Obtain the user name and you get this from the function CurrentUser()
2) Determine what group they are in
and store this data say in a global variable.

Here is an example for Access 2000 that determines if a user belings to a group called Finance

Option Compare Database
Option Explicit

' Routine to Obtain a users permission Group

Private Const JET_SCHEMA_USERROSTER = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
Private Const UPDATE_PERMISSION_GROUP = "ReadWrite"
Private Const DEVELOPER_USER_NAME = "MyDeveloperPassword"

Public Function GetUsergroup(strLoggingOn As String) As Boolean
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i As Integer
    Dim strUser As String
    Dim fFound As Boolean
On Error GoTo Err_handler

Set cn = CurrentProject.Connection
    
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider. You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
    
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, , JET_SCHEMA_USERROSTER)
    
    fFound = False
    While Not rs.EOF And Not fFound
        ' rs.fields(1) is fixed length, the text is terminated by asc 0 and padded out with asc 32
        i = InStr(1, rs.Fields(1), Chr(0))
        strUser = Left$(rs.Fields(1), i - 1)
        
        If strLoggingOn = strUser Then
            GetUsergroup = GetFinanceGroup(strUser)
            fFound = True
        End If
    rs.MoveNext
    Wend
    
Exit_Handler:
    Set rs = Nothing
    Exit Function
    
Err_handler:
    If Err <> NOT_A_VALID_ACCOUNT Then
        MsgBox Err & " " & Err.Description
    End If
    Resume Exit_Handler
End Function

Private Function GetFinanceGroup(strUser As String) As Boolean
' Returns true if the User currently logged in
' is in the Update group
    Dim ws As Workspace, usr As User, grp As Group
On Error GoTo Err_handler
    Set ws = DBEngine.Workspaces(0)
     GetFinanceGroup = False
     
    For Each usr In ws.Users
    'Debug.Print "Users/strUser "; usr.Name, strUser
        If usr.Name = strUser Then
            For Each grp In usr.Groups
                'Debug.Print "groups "; grp.Name
                If grp.Name = "Finance" Then
                        GetFinanceGroup = True
                End If
            Next
       End If
    Next
    
Exit_Handler:
  
    Exit Function
    
Err_handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler
End Function

 

[dionicia]

I will try that.

Any ideas about the first part of my question with someone pre-filling information on the form and then emailing it out to the person taking the survey?

Thank you.

 

[Dennisk]

I've done something similar, with a spreadsheet. Email that out. Set it so it is protected with only fields for data unprotected, when you receive the spreadsheet back use vba to open the spreadsheet and read in the data.