Results 1 to 12 of 12
  1. #1
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14

    Printing report programatically causes code to cease before the end of the subroutine

    Thanks in advance for any assistance. I'm fairly new to ACCESS, having previously specialised in excel, so apologies if the question is a little low brow.

    Am attempting to have a report print as part of a subrountine. However, whenever it gets to the report line, it looks like it's printing (throws up the sending to printer spawn window) but then the code simply stops... Doesn't pause, doesn't throw an error, it just resets like I've hit the stop button on the vba editor.

    Code is as follows

    Sub procrej()
    Dim datablock As Database
    Dim recset As DAO.Recordset
    Dim uname, uname2 As String


    'Identify actioning user
    DoEvents
    uname = Environ("USERNAME")
    uname2 = UCase(uname)
    On Error GoTo eh
    DoCmd.SetWarnings False
    'Ensures that the configuration data is present
    If IsNull(DLookup("[REMA]", "APDT", "[ADID]=" & 1)) Then GoTo nocon
    If IsNull(DLookup("[REMB]", "APDT", "[ADID]=" & 1)) Then GoTo nocon
    'Display in progress form
    DoCmd.OpenForm "RRPS"
    'Call the processing subroutines
    Call emailread
    Call readrej
    'Directs to the appropriate comms subroutine
    If DLookup("[RJAP]", "APDT", "[ADID]=" & 1) = True Then Call rejautproc Else Call rejmanproc
    'Close in progress form
    DoCmd.Close acForm, "RRPS"
    'Clear the temp cache
    DoCmd.RunSQL "DELETE * FROM TRJP"
    MsgBox "Rejected payments reports distributed and archival form printed at the local printer.", vbOKOnly, "I060RJ"
    'Print hard copy archive report
    DoCmd.OpenReport "LRPR", , , acHidden '<<<<<<<<<**************This is where the problem occurs
    GoTo ender
    nocon:
    'Config data missing error handler
    MsgBox "Configuration data is missing! Please submit a service request so that production support can configure the application correctly.", vbOKOnly, "E054AA"
    Set datablock = CurrentDb()
    Set recset = datablock.OpenRecordset("ACTN")
    recset.AddNew
    recset![ATUR] = uname2
    recset![ATTD] = Now
    recset![ATTY] = "DEBUG - UNEXPECTED ERROR"
    recset![ATNT] = "Missing configuration items"
    recset.Update
    GoTo ender
    'General and runtime error handler
    eh:
    Set datablock = CurrentDb()
    Set recset = datablock.OpenRecordset("ACTN")
    recset.AddNew
    recset![ATUR] = uname2
    recset![ATTD] = Now
    recset![ATTY] = "DEBUG - UNEXPECTED ERROR"
    recset![ATNT] = Err.Number & "-" & Err.Description
    recset.Update
    MsgBox "A runtime error has occurred. Please submit a service request to have this bug investigated.", vbOKOnly, "E053AA"
    On Error GoTo -1
    GoTo ender
    ender:
    Exit point for sub and re-open main menu
    DoCmd.OpenForm "MMNU"
    End Sub

    Has anyone else encountered a similar issue?

    Thanks again.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The acHidden parameter is in the wrong argument. Put another comma in front of it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Quote Originally Posted by June7 View Post
    The acHidden parameter is in the wrong argument. Put another comma in front of it.
    Thanks for the advice, June7. I've amended the code as suggested but am still getting the same result. Anything else you think I should check?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Never encountered this issue.

    Probably won't make a difference but why use acHidden parameter since you are sending direct to printer?

    Test printing a very simple report with a much simpler procedure. Start with just the command line to send document to printer. Expand code until you reproduce the issue. Maybe save paper in this testing by opening to PrintPreview and then follow with DoCmd.Close.

    At some point, should SetWarnings back to True. Or use CurrentDb.Execute instead of SetWarnings and RunSQL.

    Note for the future, please post code within CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Does the report itself have any VBA code that might be causing the problem?
    Code:
    DoCmd.OpenReport "LRPR"

  6. #6
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Quote Originally Posted by June7 View Post
    Never encountered this issue.

    Probably won't make a difference but why use acHidden parameter since you are sending direct to printer?

    Test printing a very simple report with a much simpler procedure. Start with just the command line to send document to printer. Expand code until you reproduce the issue. Maybe save paper in this testing by opening to PrintPreview and then follow with DoCmd.Close.

    At some point, should SetWarnings back to True. Or use CurrentDb.Execute instead of SetWarnings and RunSQL.

    Note for the future, please post code within CODE tags to retain indentation and readability.

    Thanks June.

    I have done a bit of experimentation for basic troubleshooting and if I pull that line out and run it on its own, the report prints and the sub finalises without issue. I will continue to explore by paring back the code line by line.

    Regards

  7. #7
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Quote Originally Posted by davegri View Post
    Does the report itself have any VBA code that might be causing the problem?
    Code:
    DoCmd.OpenReport "LRPR"
    Hi Davegri

    No VBA code attached to the report. That was my first thought as I had initially put some controls on the thing, but I pulled all that as the first step.

    Regards

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Dim uname, uname2 As String
    This doesn't do what you think. uname2 is declared as a string and uname is declared (implicitly) as a variant.
    Could use
    Code:
    Dim uname As String, uname2 As String
    I prefer to have each variable on a separate line.....


    I modified your code to remove the "GOTO" lines and used "CurrentDb.Execute" instead of the recordset.
    Code:
    Option Compare Database  'should be at the top of every module
    Option Explicit          'should be at the top of every module
    
    Sub procrej()
        On Error GoTo eh
    
        Dim uname As String
        Dim uname2 As String
    
        'Identify actioning user
        DoEvents
    
        uname = Environ("USERNAME")
        uname2 = UCase(uname)
    
        'Ensures that the configuration data is present
        If IsNull(DLookup("[REMA]", "APDT", "[ADID] = 1")) Or IsNull(DLookup("[REMB]", "APDT", "[ADID] = 1")) Then
            'Config data missing error handler
            MsgBox "Configuration data is missing! Please submit a service request so that production support can configure the application correctly.", vbOKOnly, "E054AA"
            CurrentDb.Execute "INSERT INTO ACTN (ATUR, ATTD, ATTY, ATNT) VALUES ('" & uname2 & "', #" & Now & "#, 'DEBUG - UNEXPECTED ERROR', 'Missing configuration items');"
        Else
            'Display in progress form
            DoCmd.OpenForm "RRPS"
            
            'Call the processing subroutines
            Call emailread
            Call readrej
            
            'Directs to the appropriate comms subroutine
            If DLookup("[RJAP]", "APDT", "[ADID] = 1") = True Then
                Call rejautproc
            Else
                Call rejmanproc
            End If
    
            'Close in progress form
            DoCmd.Close acForm, "RRPS"
    
            'Clear the temp cache
            DoCmd.RunSQL "DELETE * FROM TRJP"
            CurrentDb.Execute "DELETE * FROM TRJP", dbFailOnError
    
            MsgBox "Rejected payments reports distributed and archival form printed at the local printer.", vbOKOnly, "I060RJ"
            'Print hard copy archive report
            DoCmd.OpenReport "LRPR", acViewNormal, , , acHidden   '<<<<<<<<<**************This is where the problem occurs
        End If
    
    ender:
        'Exit point for sub and re-open main menu
        DoCmd.OpenForm "MMNU"
        Exit Sub
    
    
    eh:        'General and runtime error handler
        CurrentDb.Execute "INSERT INTO ACTN (ATUR, ATTD, ATTY, ATNT) VALUES ('" & uname2 & "', #" & Now & "#, 'DEBUG - UNEXPECTED ERROR', " & Err.Number & "' - '" & Err.Description & "');"
        MsgBox "A runtime error has occurred. Please submit a service request to have this bug investigated.", vbOKOnly, "E053AA"
        Resume ender
    End Sub

    Feel free to ignore the changes...

    Don't know if it will affect your print problem...... couldn't test it.....

  9. #9
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Quote Originally Posted by ssanfu View Post
    Code:
    Dim uname, uname2 As String
    This doesn't do what you think. uname2 is declared as a string and uname is declared (implicitly) as a variant.
    Could use
    Code:
    Dim uname As String, uname2 As String
    I prefer to have each variable on a separate line.....


    I modified your code to remove the "GOTO" lines and used "CurrentDb.Execute" instead of the recordset.
    Code:
    Option Compare Database  'should be at the top of every module
    Option Explicit          'should be at the top of every module
    
    Sub procrej()
        On Error GoTo eh
    
        Dim uname As String
        Dim uname2 As String
    
        'Identify actioning user
        DoEvents
    
        uname = Environ("USERNAME")
        uname2 = UCase(uname)
    
        'Ensures that the configuration data is present
        If IsNull(DLookup("[REMA]", "APDT", "[ADID] = 1")) Or IsNull(DLookup("[REMB]", "APDT", "[ADID] = 1")) Then
            'Config data missing error handler
            MsgBox "Configuration data is missing! Please submit a service request so that production support can configure the application correctly.", vbOKOnly, "E054AA"
            CurrentDb.Execute "INSERT INTO ACTN (ATUR, ATTD, ATTY, ATNT) VALUES ('" & uname2 & "', #" & Now & "#, 'DEBUG - UNEXPECTED ERROR', 'Missing configuration items');"
        Else
            'Display in progress form
            DoCmd.OpenForm "RRPS"
            
            'Call the processing subroutines
            Call emailread
            Call readrej
            
            'Directs to the appropriate comms subroutine
            If DLookup("[RJAP]", "APDT", "[ADID] = 1") = True Then
                Call rejautproc
            Else
                Call rejmanproc
            End If
    
            'Close in progress form
            DoCmd.Close acForm, "RRPS"
    
            'Clear the temp cache
            DoCmd.RunSQL "DELETE * FROM TRJP"
            CurrentDb.Execute "DELETE * FROM TRJP", dbFailOnError
    
            MsgBox "Rejected payments reports distributed and archival form printed at the local printer.", vbOKOnly, "I060RJ"
            'Print hard copy archive report
            DoCmd.OpenReport "LRPR", acViewNormal, , , acHidden   '<<<<<<<<<**************This is where the problem occurs
        End If
    
    ender:
        'Exit point for sub and re-open main menu
        DoCmd.OpenForm "MMNU"
        Exit Sub
    
    
    eh:        'General and runtime error handler
        CurrentDb.Execute "INSERT INTO ACTN (ATUR, ATTD, ATTY, ATNT) VALUES ('" & uname2 & "', #" & Now & "#, 'DEBUG - UNEXPECTED ERROR', " & Err.Number & "' - '" & Err.Description & "');"
        MsgBox "A runtime error has occurred. Please submit a service request to have this bug investigated.", vbOKOnly, "E053AA"
        Resume ender
    End Sub

    Feel free to ignore the changes...

    Don't know if it will affect your print problem...... couldn't test it.....
    Thanks Steve.

    I gave the amended code a try but got the same result. Appreciate you giving it a shot though.

    Regards

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The posted problem is code stops executing after the report is opened/printed? I must be missing something. After the opening, the original code has a GoTo ender, so everything between that directive and ender will never be executed (such as the recordset edits - not sure you're saying they should). Or is it that form MMNU doesn't even open? Or it does but it's not visible? Or are you saying a printer dialog opens up and nothing else happens while it's still open? That would be normal.

    Don't know how I come up with so many questions when you guys seem to be aware of the problem. I'm beginning to think I read too much into posted issues (not just this one).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Quote Originally Posted by Micron View Post
    The posted problem is code stops executing after the report is opened/printed? I must be missing something. After the opening, the original code has a GoTo ender, so everything between that directive and ender will never be executed (such as the recordset edits - not sure you're saying they should). Or is it that form MMNU doesn't even open? Or it does but it's not visible? Or are you saying a printer dialog opens up and nothing else happens while it's still open? That would be normal.

    Don't know how I come up with so many questions when you guys seem to be aware of the problem. I'm beginning to think I read too much into posted issues (not just this one).
    Hi Micron

    The problem is that the code runs fine until it gets to the report line. It then briefly throws up the printing document spawn window which disappears before it completes. The sub then ceases. It does not actually print the report, nor does it follow the remainder of the logic path through the sub (such as displaying MMNU). The sub terminates then and there.

    Regards

  12. #12
    MTD072 is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2017
    Posts
    14
    Thankyou to those who offered assistance.

    I have located the problem in one of the called subs... I had failed to clear an object variable and fixing that seems to have resolved the problem.

    Regards

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Printing a Report from a button using Code Builder
    By liamfrancis2013 in forum Programming
    Replies: 3
    Last Post: 07-14-2015, 09:46 AM
  2. Printing a record in a report programatically
    By argsemapp in forum Reports
    Replies: 3
    Last Post: 12-03-2013, 05:40 PM
  3. Help fixing a code to change password programatically
    By smartflashes in forum Programming
    Replies: 3
    Last Post: 01-19-2012, 10:20 PM
  4. Replies: 3
    Last Post: 10-19-2011, 01:05 PM
  5. Calling Access VBA subroutine from an Excel VBA subroutine
    By richard_yolland in forum Programming
    Replies: 0
    Last Post: 02-16-2011, 11:30 AM

Tags for this Thread

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