Kayleigh
Member
- Local time
- Today, 11:28
- 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:
This is my code:
Appreciate any suggestions here.
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.
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.