Report filtering by Logged in User (1 Viewer)

Tiger6115

Registered User.
Local time
Today, 18:10
Joined
Jun 25, 2012
Messages
24
I'm looking to filter a report according to the user that opens the report (by command button). I have setup in a data entry form for the DB to capture the logged in username and add it to each record (using a mod I found that uses Environ() ). I've created a report, now I would like for the reports to filter showing only that logged in users data.

I've tried filtering it On Load, On Open, in the properties screen, in VBA, on the query, on the report, I'm just not getting the syntax right:banghead: or haven't structured it correctly. I've run across a number of promising suggestions online and on this great site, but they didn't work, a few due to the 64bit version of office I'm running.

Any help/direction is much appreciated.

Details:

-About 8 total Users
-The basic module I'm using to get the username is:
Public Function GetUserName() As String
GetUserName = Environ("UserName")
End Function
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:10
Joined
Sep 21, 2011
Messages
14,047
So use the Where option when opening the report?

https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport

http://codevba.com/msaccess/docmd_openreport.htm

If it based on a query then you could even hard code the form control name into the query?, but I'd go with the Where option as it is more flexible?

Here is one of mine

HTH
Code:
Private Sub cmdShip_Click()
On Error GoTo Err_cmdShip_Click

    Dim stRptName As String, stParam As String, stLinkCriteria As String, stDBpath As String, stFTPpath As String
    Dim iPreview As Integer, iDialog As Integer
    
    stDBpath = CurrentProject.Path & "\"
    stFTPpath = stDBpath & "Gazette\"
    iPreview = 0
    iDialog = 0
    iPreview = acViewPreview
    If Me.ChkPreview Then
       ' iPreview = 2
        iDialog = acWindowNormal
    Else
        iDialog = acHidden
    End If
    
    stRptName = "Main_by_Ship"
    
    stParam = Replace(LCase(Me.cboShip.Value), " ", "_")
    stLinkCriteria = "[Ship] = '" & Me.cboShip.Value & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, iPreview, , stLinkCriteria, iDialog
    DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

Exit_cmdShip_Click:
    Exit Sub

Err_cmdShip_Click:
    MsgBox Err.Description
    Resume Exit_cmdShip_Click
    
End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 15:10
Joined
Oct 29, 2018
Messages
21,358
Hi Tiger. As Gasman said, you can use the WhereCondition argument of the OpenReport method. For example:
Code:
DoCmd.OpenReport "ReportName", , , "UserNameField='" & GetUserName() & "'"
Hope that helps...
 

Tiger6115

Registered User.
Local time
Today, 18:10
Joined
Jun 25, 2012
Messages
24
Great!! Thank you Gasman, Thank you theDBguy!! I knew I was over complicating it and that it was something that simple. After trying it a couple times I thought it didn't work, then I saw a ton of stuff on my printer. LOL So I played around with it a little bit to display the report how I want it and now it's perfect.

Thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:10
Joined
Oct 29, 2018
Messages
21,358
Great!! Thank you Gasman, Thank you theDBguy!! I knew I was over complicating it and that it was something that simple. After trying it a couple times I thought it didn't work, then I saw a ton of stuff on my printer. LOL So I played around with it a little bit to display the report how I want it and now it's perfect.

Thanks again
Hi. Glad to hear you got it sorted out. Gasman and I were happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom