Help needed to modify my OnOpen Filter

[chrisb1981]

I currently have the code below which filters the form depending on who should have access to which records. It all works fine but now I want to add in an extra filter criteria. So basically I need the Me.Filter(shown in red) changed to include DeleteRecord="0" (field in tblContacts) which will hide records marked as deleted but I don't know how to change it.

Private Sub Form_Open(Cancel As Integer)
Dim RetVal          As Long
Dim RetValGroupID   As Long
RetVal = Nz(DLookup("RepID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)
RetValGroupID = Nz(DLookup("GroupID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)
If Environ("UserName") = "chris.butler" Then
Me.FilterOn = False
Else
If Environ("UserName") = "caroline.mulvihill" Then
Me.FilterOn = False
Else
If Environ("UserName") = "caroline.farrell" Then
Me.FilterOn = False
Else
If RetVal = -1 Then
    MsgBox "Main user does not exist, closing form...."
    DoCmd.Close acForm, Me.Name
    Else
    [COLOR="Red"]Me.Filter = "RepID=" & RetVal & " OR GroupID=" & RetValGroupID[/COLOR]
    Me.FilterOn = True
End If
End If
End If
End If
End Sub

 

[Frivolous Sam]

Change the line you highlighted to:

    Me.Filter = "( RepID=" & RetVal & " OR GroupID=" & RetValGroupID & " ) AND DeleteRecord=0"

 

[Moniker]

And, just to clean up that code, I redid it a bit for you. Having multiple nested If..Then statements is never a good idea. It performs slowly, it's easy to screw something up accidentally (forgetting an End If, for example), and it's difficult to read. In this instance, a Case statement is cleaner. In other cases, using a Switch function is cleaner.

Also note that constantly calling a function (in this case, the Environ function) will slow things down. Since it's returning a static value (the user name), assign it to a variable and reference the variable. Finally, there was no point to setting the RetValGroupID value before confirming that RetVal was valid. It will only get used if RetVal is not -1 and the user name is valid, so assign it after you've confirmed that both RetVal and the user name are valid.

Hope this makes sense.

Private Sub Form_Open(Cancel As Integer)

    Dim RetVal As Long
    Dim RetValGroupID As Long
    Dim UserName As String
    
    UserName = Environ("UserName")
    RetVal = Nz(DLookup("RepID", "tblreps", "NTLogin ='" & UserName & "'"), -1)

    If RetVal = -1 Then
        MsgBox "Main user does not exist, closing form...."
        DoCmd.Close acForm, Me.Name
    End If

    Select Case UserName
	Case "chris.butler" Or "caroline.mulvihill" Or "caroline.farrell"
	    Me.FilterOn = False
	Case Else
            RetValGroupID = Nz(DLookup("GroupID", "tblreps", "NTLogin ='" & UserName & "'"), -1)
            Me.Filter = "( RepID=" & RetVal & " OR GroupID=" & RetValGroupID & " ) AND DeleteRecord=0"
            Me.FilterOn = True
    End Select

End Sub

 

[chrisb1981]

I tried the coding you gave me moniker as I wanted to use cleaner code and I get a error on the line

Case "chris.butler" Or "caroline.mulvihill" Or "caroline.farrell"

It says Type Mismatch. Which is why I ended up using multiple IF statments in my original code as I could not get it working.