Help needed on filtering a form

[chrisb1981]

I have a form which currently filters records by record owner using NTLogin as the filter this is working fine but now I need to filter the records by record owner and their assigned buddy.

E.g If Andy or Steph logon they see the same records because Andy is the Record Owner and Steph is his buddy.

My current code is:

Dim RetVal As Long

RetVal = Nz(DLookup("RepID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)

If RetVal = -1 Then
MsgBox "User does not exist, closing form...."
DoCmd.Close acForm, Me.Name
Else
Me.Filter = "RepID=" & RetVal
Me.FilterOn = True
End If

Can someone suggest how I could modify this to get the result I am after. All Record Owners and Buddy's are in the table tblreps.

Thanks in Advanced

 

[Brian1960]

Return BuddyID as well

Try something like this. Change the BuddyID to the required field name.

Dim RetVal As Long

RetVal = Nz(DLookup("RepID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)

If RetVal = -1 Then
MsgBox "User does not exist, closing form...."
DoCmd.Close acForm, Me.Name
Else
Me.Filter = "RepID=" & RetVal
Me.FilterOn = True
End If

To

Dim RetVal          As Long
Dim RetValBuddyID          As Long
RetVal = Nz(DLookup("RepID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)
RetValBuddyID          = Nz(DLookup("BuddyID", "tblreps", "NTLogin ='" & Environ("UserName") & "'"), -1)
If RetVal = -1 Then
    MsgBox "Main user does not exist, closing form...."
    DoCmd.Close acForm, Me.Name
    Else
    Me.Filter = "RepID=" & RetVal &" OR RepID="& RetValBuddyID
    Me.FilterOn = True
End If

 

[chrisb1981]

Thanks Brian it worked great!!