Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-18-2019, 05:56 AM   #1
Tiger6115
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
Tiger6115 is on a distinguished road
Report filtering by Logged in User

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 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

Tiger6115 is offline   Reply With Quote
Old 11-18-2019, 06:02 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,521
Thanks: 441
Thanked 841 Times in 812 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Report filtering by Logged in User

So use the Where option when opening the report?

https://docs.microsoft.com/en-us/off...cmd.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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 11-18-2019 at 06:09 AM.
Gasman is online now   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
Tiger6115 (11-18-2019)
Old 11-18-2019, 06:29 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,515
Thanks: 58
Thanked 1,424 Times in 1,405 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Report filtering by Logged in User

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...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Tiger6115 (11-18-2019)
Old 11-18-2019, 12:05 PM   #4
Tiger6115
Newly Registered User
 
Join Date: Jun 2012
Posts: 21
Thanks: 6
Thanked 0 Times in 0 Posts
Tiger6115 is on a distinguished road
Re: Report filtering by Logged in User

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
Tiger6115 is offline   Reply With Quote
Old 11-18-2019, 12:40 PM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,515
Thanks: 58
Thanked 1,424 Times in 1,405 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Report filtering by Logged in User

Quote:
Originally Posted by Tiger6115 View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't get into design mode of report for logged In user Rhughes53 Reports 2 08-15-2011 11:45 AM
User that is logged in Gunilla Forms 3 11-28-2010 11:33 AM
User who just logged in grnzbra General 3 08-31-2006 01:36 PM
user logged JonyBravo General 1 02-07-2006 02:30 PM
need help with logged on user I-1 General 3 10-28-2005 10:18 AM




All times are GMT -8. The time now is 08:21 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World