Cross referencing a RS for multiple forms...........

[sleonard1432]

Hey All

I have designed a DB with a password form: here is the code below.......

Private Sub cmdOpenFrm_Click()
Dim UserId, Pwd As String

cboUserId.SetFocus
UserId = cboUserId.Text

txtPwd.SetFocus
Pwd = txtPwd.Text

    If IsNull(UserId) = True Then
        MsgBox "Please select user name from drop down.",   vbOKOnly, "INVALID USER!"
        Me.cboUserId.SetFocus
    End If

    If IsNull(Pwd) = True Then
        MsgBox "Please enter correct password.  If you do not know or you have forgotten your password, please contact database administrator.", vbOKOnly, "INVALID PASSWORD!"
        Me.txtPwd.SetFocus
    End If
    
Dim PwdRS As DAO.Recordset
Dim db As Database
Set db = CurrentDb()
Set PwdRS = CurrentDb.OpenRecordset("select * from tblUser where strUserId = " & Chr(34) & UserId & Chr(34))
    If PwdRS.EOF = True Then
        MsgBox "Please select a proper User ID from dropdown.  If your User ID is not in the dropdown area, then please contact the database administrator.", vbOKOnly, "Invalid User!"
        cboUserId.SetFocus
    End If
        
intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact database administrator for assistance.  Thank you!", vbOKOnly, "Invalid User!"
        DoCmd.Close
    GoTo TheEnd
    End If

PwdRS.MoveFirst
    If PwdRS.Fields("strPwd") = Pwd Then
        Dim stDocName As String
        stDocName = "frmSplash1"
        DoCmd.Close acForm, "frmPwScreen"
        DoCmd.OpenForm "frmSplash1"
    Else
        MsgBox "The password you have entered is incorrect." & "Please enter another password."
        txtPwd.SetFocus
        GoTo TheEnd
    End If
    
Dim MyUserId As String
Dim MyUserNam As String
Dim MyUserLevel As String
Dim MyQryTbl As String

    MyUserId = PwdRS.Fields("strUserId")
    MyUserNam = PwdRS.Fields("strUserNam")
    MyUserLevel = PwdRS.Fields("strUserLevel")
    MyQryTbl = "Select" & Chr(34) & MyUserId & Chr(34) & "as strCurUserId, " & Chr(34) & MyUserNam & Chr(34) & _
                "as strCurUserNam, " & Chr(34) & MyUserLevel & Chr(34) & "as strCurUserLevel into tblCurUser"
    DoCmd.SetWarnings False
    DoCmd.RunSQL MyQryTbl
    DoCmd.SetWarnings True
    
TheEnd:
End Sub

Now I have stored the Current User ID (strCurUserId), Current User Name(strCurUserNam) and Current User Level (strCurUserLevel) into a table named "tblCurUser". What I am looking to achieve is allow specified users to access management tools, by crossreferencing the strCurUserLevel within tblCurUser and I would also like to have visible at all times the User Name listed on each of the forms that the user has entered.

Any ideas would be GREATLY appreciated, as I have not been successful in my attempts.

Thanks in advance for your help.....

SEL