Change recordsource depending on user access level (1 Viewer)

Kayleigh

Member
Local time
Today, 14:34
Joined
Sep 24, 2020
Messages
706
Hi,
I have a simple form which contains list of students. I would like it to be viewed by various levels of users but each may only view info pertinent to them. So I have designed two queries to show:
  • SLT staff - students in their department
  • and admin staff - all students.
In the forms recordsource I have one query which will is defined on the form's load event depending on the user signed in to the system. However I can't seem to get the admin query to work - default is the SLT query so it does not change at all on event, (though will work independently).

This is my code:
Code:
Private Sub Form_Load()
    Dim intX As Integer
    Dim varAdmin As Boolean
    Dim varSLT As Boolean
    Dim strSQLa As String
    Dim strSQLb As String
    Dim db As DAO.Database
    Dim qdAdmin As DAO.QueryDef
    Dim qdSLT As DAO.QueryDef
   
    If Not IsFormLoaded("frmLogin") Then
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "frmlogin"
        Exit Sub
    End If
   
    intX = Forms!frmLogin!cmbstaff.Value
    varAdmin = DLookup("fldAdmin", "tblStaff", "[fldStaffID] = " & intX)
    varSLT = DLookup("fldSLT", "tblStaff", "[fldStaffID] = " & intX)
     Debug.Print varAdmin
     Debug.Print varSLT
       
    Set db = CurrentDb()
    Set qdAdmin = db.QueryDefs("qryStudentDirectory")
    Set qdSLT = db.QueryDefs("qryStudentDirectory")
   
    'query for admin staff
    strSQLa = "SELECT DISTINCT qrylkpStudentName.fldStudentID, qrylkpStudentName.Student, tblClassID.fldClassID, tblClassID.fldClassName, qrylkpStudentName.Address, qrylkpStudentName.fldDateLeft " & vbCrLf & _
    "FROM qrylkpStudentName RIGHT JOIN tblClassID ON qrylkpStudentName.fldClassID = tblClassID.fldClassID " & vbCrLf & _
    "WHERE (((qrylkpStudentName.fldDateLeft)>Date() Or (qrylkpStudentName.fldDateLeft) Is Null)) " & vbCrLf & _
    "ORDER BY qrylkpStudentName.Student;"
   
    'query for SLT staff
    strSQLb = "SELECT DISTINCT qrylkpStudentName.fldStudentID, qrylkpStudentName.Student, qryStaffClassStudent.fldClassID, tblClassID.fldClassName, qrylkpStudentName.Address, qrylkpStudentName.fldDateLeft, qryStaffClassStudent.fldStaffID " & vbCrLf & _
    "FROM (qrylkpStudentName RIGHT JOIN tblClassID ON qrylkpStudentName.fldClassID = tblClassID.fldClassID) RIGHT JOIN qryStaffClassStudent ON tblClassID.fldClassID = qryStaffClassStudent.fldClassID " & vbCrLf & _
    "WHERE (((qrylkpStudentName.fldDateLeft)>Date() Or (qrylkpStudentName.fldDateLeft) Is Null) AND ((qryStaffClassStudent.fldStaffID)=[forms]![frmLogin]![cmbstaff])) " & vbCrLf & _
    "ORDER BY qrylkpStudentName.Student;"
   
    If (varAdmin = True) Or (intX = 289) Then
        qdAdmin.SQL = strSQLa
    Else
        qdSLT.SQL = strSQLb
    End If
   
    Set qdAdmin = Nothing
    Set qdSLT = Nothing
    Set db = Nothing
   
End Sub

Appreciate any suggestions here.
 

Isaac

Lifelong Learner
Local time
Today, 07:34
Joined
Mar 14, 2017
Messages
8,738
Have you tried Me.Requery after this code runs? That might refresh the recordsource?

I've never tried updating a Form's Recordsource (named query)'s .Sql property and then just tried to let it dynamically refresh, you might need the Requery command
 

Kayleigh

Member
Local time
Today, 14:34
Joined
Sep 24, 2020
Messages
706
Yes but didn't update. Would you suggest another method without changing SQL?
 

Cronk

Registered User.
Local time
Tomorrow, 01:34
Joined
Jul 4, 2013
Messages
2,770
If the source recordset is missing fields on the form, you will have #Name? displayed. A better option is to hide the text boxes etc for lower levels of access.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:34
Joined
May 7, 2009
Messages
19,169
move your code to the Open event.
if you are using Load event and you are changing the Current form's recordsource, you need to Requery the form (on the load event).
 

bastanu

AWF VIP
Local time
Today, 07:34
Joined
Apr 13, 2010
Messages
1,401
Or just remove all the code and edit the query to return all records for admin and just the desired department for staff (by adding criteria to the fldStaffID and a reference to the login form IsAdmin:DLookup("fldAdmin", "tblStaff", "[fldStaffID] = " & Forms!frmLogin!cmbstaff)).
Cheers,
 

Users who are viewing this thread

Top Bottom