Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    22
    Quote Originally Posted by Micron View Post
    I know of no other info to direct you to. It seems basic to me (not trying to be condescending here) so I don't know what else to say.
    Can you post a zipped copy of your db? You should compact/repair it first but given the current situation I wonder what the result of that attempt would be. If it has sensitive info, you could manually run update queries, or eliminate it, or try this

    If you do post, make sure we know what to do to replicate the issue.
    Thank you for your time and willingness to help. Sorry for late response. I have removed all sensitive data from my database. Please note, this is my first database so I know there is significant room for improvement. Still figuring things out...but as clunky as this is, it actually works quite well for my needs. The previous manual process took my predecessor roughly 100 hours to complete the task that can now be completed in about 14 hours with a higher level of accuracy. So, even though its clunky its still a huge improvement over the old process.

    To reach my current dilemma....
    Click on GDLVSCL -> Reports -> Either of the 2 export buttons on the report menu will show you what I'm talking about. I'd like the report to stay hidden while the export code is running. Tried the acHidden but gives me the error mentioned above.



    Database compact/repaired and zipped.
    Attached Files Attached Files

  2. #17
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    If you want the report hidden, don't open it. Just output to PDF.

    Code:
    Do While Not rs.EOF
        temp = rs("ManagerName")
        MyFileName = "GDL User Level Access Report - " & rs("ManagerName") & ".PDF"
        
        'DoCmd.OpenReport "GDLVSCL1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "GDLVSCL1 User Level Access Report", acFormatPDF, sFolder & "\" & MyFileName
        DoCmd.Close acReport, "GDLVSCL1 User Level Access Report"
    
    
        rs.MoveNext
    Loop

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'll comment on the code that was posted and the db and leave it to you to decide whether you stick with your current version or adopt davegri's suggestion.
    As I already mentioned, you cannot provide an empty string ("") for the report name in this scenario. The active report is output by leaving the argument blank as I showed you. However, since you now want to keep it hidden, it cannot be active - so you simply provide the report name again. If I was using such a long name several times as you are/would be I would use a string variable for the name instead.

    Code:
        DoCmd.OpenReport "GDLVSCL1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
        DoCmd.OutputTo acOutputReport, "GDLVSCL1 User Level Access Report", acFormatPDF, sFolder & "\" & MyFileName
        DoCmd.Close acReport, "GDLVSCL1 User Level Access Report"
    EDIT - forgot to mention that it's possible that simply providing the name will output an unfiltered version of the report. If that's the case, not opening the report as davegri suggested would probably do the same. In that case, to keep the report hidden, you'll have to do something else
    - perhaps base the report on a query that gets form variable values or
    - modify the report recordsource on the fly or
    - modify the report query def (obviously there would have to be a query that the report is based on) or
    - simplest would likely be to create a criteria expression for the Where condition of the OpenReport method.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @scor1pion
    Do as Davegri suggested in #17. It works for me.
    -Comment out the OpenReport line.
    -Put the Report name into the DoCmd.OutputTo line

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It works for me.
    Except the page counts are not the same as the original code? I don't think you can apply a filter in the OutputTo method either.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    scor1pion is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Posts
    22
    Thank you all, It works now!!!

    I tried both variations in my full database. I had to use this code:
    Code:
        DoCmd.OpenReport "GDLVSCL1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
        DoCmd.OutputTo acOutputReport, "GDLVSCL1 User Level Access Report", acFormatPDF, sFolder & "\" & MyFileName
        DoCmd.Close acReport, "GDLVSCL1 User Level Access Report"
    While this code did not load the report, the output was the same for each report. It lost the filtering criteria.

    Code:
        'DoCmd.OpenReport "GDLVSCL1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "GDLVSCL1 User Level Access Report", acFormatPDF, sFolder & "\" & MyFileName
        DoCmd.Close acReport, "GDLVSCL1 User Level Access Report"

  7. #22
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the output was the same for each report. It lost the filtering criteria.
    That was my point. Glad you're OK now!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #23
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    My point was that if you comment out the OpenReport, and put the actual report name in the OutputTo line, then you could get the report in pdf without seeing the report - which I thought was the objective. I think that was Dave's thinking also.
    Sorry if there was any confusion.
    Glad you have it resolved now.

    If you want to be able to restrict the records to be reported, my thought is you would have to change at the query level. As has been said there is no filter or where clause parameters on the OutputTo, so to limit the report's recordsource you could use some parameter on thee query, or a combo/listbox on the form to be used as criteria on the recordsource. (untested)
    Last edited by orange; 01-29-2021 at 10:15 AM.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error with OpenRecordset
    By snsmith in forum Modules
    Replies: 15
    Last Post: 06-04-2019, 01:43 PM
  2. Replies: 3
    Last Post: 02-26-2016, 12:34 PM
  3. dbSeeChanges does not fix openrecordset error
    By lringstad in forum Access
    Replies: 1
    Last Post: 12-03-2013, 03:33 PM
  4. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  5. db.OpenRecordset error
    By jscriptor09 in forum Programming
    Replies: 4
    Last Post: 01-27-2012, 12:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums