Role-based Form recordsource

Question

Is there a best practice around opening a form based on recordsources whose columns are limited by the user's role?

We have some users who modify data that don't want to see the columns marking criteria for inclusion on different reports (columns with boolean values named isOnRpt1, isOnRpt2, etc. used for filtering purposes) and other users who only run reports that need to sort and filter data but not see columns used for internal calculations. Say we have 65 columns total and everyone wants to see the same 5 key columns and 10 filtering columns but some users need visibility of 30 additional columns and others want to see 20 other columns. What I have now is the God view with all of everyone's columns, and it's freaking them out.

Is there a best practice for maintaining a form for users in different roles whose datasheet view contains different subsets of columns? I'm not worried about visibility of controls on the form view, users in the reporter role won't use that view at all.

I don't want to create different forms for each role (frmDetailReporter, frmDetailWriter) whose only difference is the qry in the recordsource property. I have a main form staff in any role could use to navigate to a role-specific instance of this detail form (they need to navigate to other forms with aggregate data) but I'm not sure if I should have an entry point for each role in the form's VBA code or change the recordsource property before opening the form or if there's a better way to support users of different views of the same data source.

TIA

Let's say I've identified two roles, Reporter and Writer. I can create two queries for those roles, qryDetailReporter and qryDetailWriter with different columns. I have two buttons on a main form for the two different roles, btnDetailReporter and btnDetailWriter used to open the shared detail form, frmDetail, containing all the VBA code for all the roles. Is there a best practice for the VBA code behind frmMain.btnDetail* for loading frmDetail? I'd like to set the recordsource property to one of the two queries before loading the form, but Access forms don't work that way, so I'm thinking I could have a global variable containing a roleID referenced in frmDetail's load event that sets the recordsource property, but I'd prefer to do this without a global variable.

Here's what I have now:

' module
    Public g_roleID As Integer

' frmMain
Private Sub btnDetailWriter_Click()
    g_roleID = 1
    DoCmd.OpenForm "frmDetail", acFormDS
End Sub

Private Sub btnDetailReporter_Click()
    g_roleID = 0
    DoCmd.OpenForm "frmDetail", acFormDS
End Sub

' frmDetail
Private Sub Form_Open(Cancel As Integer)
    Dim objCtl As Control
    Dim sTxt As String
    Dim sFld As String

    Select Case g_roleID
        Case 0
            RecordSource = "qryDetailReporter"
        Case 1
            RecordSource = "qryDetailWriter"
        Case Else
            RecordSource = "vw_detail"
    End Select

    On Error GoTo noFld
    For Each objCtl In Controls
        Select Case objCtl.ControlType
            Case acTextBox, acComboBox
                sTxt = objCtl.ControlSource
                objCtl.ColumnHidden = False
                sFld = RecordsetClone.Fields(sTxt) & ""
        End Select
    Next
    Exit Sub

noFld:
    objCtl.ColumnHidden = True
Resume Next
End Sub

Is it a datasheet form? I have some code lying around that loads all columns from a recordset into a datasheet form for viewing. That way, you only have to worry about creating the right recordset, not about the form part — Erik A
You can most certainly set the forms recordsource to a sql string or query on startup. just go me.RecordSource = "some sql" — Albert D. Kallal

Sergey S. Sergey S. · Accepted Answer · 2018-01-26T04:15:10

I would suggest to create different queries for each role with different set of fields and change RecordSource property of form depending on user role. The form should have set of all available columns. After selecting required recordsource a simple VBA code should compare set of available fields in recordsource with set of ControlSource properties of each form's control. If the field is missing in recordsource, hide the column using ColumnHidden control's property.

Role-based Form recordsource

1 Answers