Opening a form with a filter

[Dom606]

Hi everyone,
I am using a module that holds logon on data for later use. I then added some code that is supposed to capture the User.UserID from the module and compare it to field in tblEmployees named A_UserID. The result will be the form will only show the records for the person logged into the database.

I tried moving the code from the OnLoad to the OnOpen event but that did not work either.

For the life of me, I cannot get the User.UserID value to populate. Can anyone suggest what I am doing wrong?
Thanks
Dom

Option Compare Database
Option Explicit
'----------------------------------------
' This code is used assign variables to the user  
'information and hold it in memory
' so that it can be used as the user navigates through
' the database
' Created By:  Richard Rensel
' Created On:  19 Feb 2002
' Modified On: 19 Feb 2002
'----------------------------------------

Public Type UserInfo
    ViewID As Integer
    AccessID As Integer
    Active As Boolean
    Password As String
    UserID As String
    SecurityID As String
End Type

Public User As UserInfo

[COLOR=red]************************************[/color]
Private Sub Form_Load()
Call myHideBars

    [b]DoCmd.ApplyFilter "", ([A_UserID] = User.UserID)[/b]
    If Me.Recordset.RecordCount = 0 Then
       MsgBox "No Records To Display"
       Me.Undo
       Me.FilterOn = False
    End If


End Sub

 

[PHV]

Where and how is populated your public User variable ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Hi PH,
Thanks for the reply. I have a user logon screen where the user enters their userid and password. The following module sets the variables:

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click
'------------------------------------------------
' This code is used to validate users found in the tblSecurity table. If the wrong user name or password is
' provided access is denied.
' Created by: Richard Rensel
' Date Created: 18 Feb 2002
' Date Modified: 19 feb 2002
'------------------------------------------------
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstV As Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)
    
    If Not IsNull(Me.txtUser) And Not IsNull(Me.txtPassword) Then
        rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUser & "'"
    
        If rst.NoMatch Then
            MsgBox "You entered the wrong User Name or Password." & Chr(13) & _
            "Please enter the correct User Name and Password or " & Chr(13) & _
            "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
        ElseIf Me.txtPassword = "password" Then
            MsgBox "This is the first time using the database or your passowrd has been reset." & Chr(13) & _
            "You must change your password before you can enter the database.", _
            vbOKOnly + vbExclamation, "Change Password"
            stDocName = "frmUserLogonNew"
            stLinkCriteria = "[UserID]=" & "'" & Me![txtUser] & "'"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            stDocName = "frmStartup"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    Else
        MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
        "Please enter the correct User Name and Password or " & Chr(13) & _
        "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
    End If
    
    With User
        .AccessID = rst.Fields("AccessID")
        .ViewID = rst.Fields("ViewID")
        .Active = rst.Fields("Active")
        .Password = rst.Fields("Password")
        .SecurityID = rst.Fields("SecurityID")
        .UserID = rst.Fields("UserID")
    End With
    
    rst.Close
    
Exit_cmdOk_Click:
    Exit Sub

Err_cmdOk_Click:
    MsgBox Err.Description
    Resume Exit_cmdOk_Click

End Sub

 

[PHV]

The Public stuff should be in a standard code module, not a Form class module.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dom606]

Sorry PV, I don't know what you mean. Are you saying the DoCmd applyfilter belongs in the Public Type UserInfo like this?

Public Type UserInfo
    ViewID As Integer
    AccessID As Integer
    Active As Boolean
    Password As String
    UserID As String
    SecurityID As String
End Type

Public User As UserInfo
   DoCmd.ApplyFilter "", ([A_UserID] = User.UserID)
    If Me.Recordset.RecordCount = 0 Then
       MsgBox "No Records To Display"
       Me.Undo
       Me.FilterOn = False
    End If[code/]