Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Irving, TX (Dallas, Fort Worth)

    Unanswered: Printing Process Flow


    Having a problem with process flow while printing.

    I have a process that:
    1. Pulls and Prints a summary sheet grouped by client (report 1),
    2. Pulls and Prints a seperator page with Client Name (report 2),
    3. Pulls and Prints the Client tickets for the Client (report 3)
    Problem is each is suppose to wait on the other and additionally I have a pop-up which is suppose to allow the user to select whether he/she wants to just print or preview any or each.

    Right now they both run ahead of each other and additionally do not wait for input from the form.

    Here is my code:
    Sub Run()
        Call Mak_Tmp("tmpREPfnr")
        Call Sav_Tmp("tmpREPfnr", "tblMODfnr")
    '    DoCmd.OpenForm "pfmPRTopts"
        DoCmd.OpenForm "pfmPRTopts", acNormal, , , , acDialog
        If PrtOps = 2 Or PrtOps = 3 Then
            Call Run_Rep("repINVreview", acViewPreview)
            Call Run_Rep("repINVreview", Null)
        End If
        If PrtOps = 3 Or PrtOps = 4 Then
            Call Run_TS("tblMODfnr", acViewPreview)
            Call Run_TS("tblMODfnr", Null)
        End If
    End Sub
    Sub Run_Rep(myRpt, myOpts)
        FrmEDate = TargetForm![tboxEDT]         ' Set Report Dates
        FrmSDate = TargetForm![tboxSDT]
        If IsNull(myOpts) Then
    '        DoCmd.OpenReport myRpt
            DoCmd.OpenReport myRpt, , , , acDialog
    '        DoCmd.OpenReport myRpt, myOpts
            DoCmd.OpenReport myRpt, myOpts, , , acDialog
        End If
    End Sub
    Sub Run_TS(mySTBL, myOpts)
        Dim dbs As DAO.Database, WSp As DAO.Workspace, RSc As DAO.Recordset, RSs As DAO.Recordset
        Dim SQLstm, SQLstr, WHRstr
        DATbeg = TargetForm![tboxSDT]
        DATend = TargetForm![tboxEDT]
        FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
        FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
        SQLstm = "INSERT INTO tblTIMrep ( trp_ahr, trp_aml, trp_anl, trp_bhr, trp_bnl, " & _
                 "trp_bml, trp_cln, trp_eby, trp_ccf, trp_pno, trp_pds, trp_tno, trp_pty, " & _
                 "trp_wdt, trp_wid, trp_win, trp_wir, trp_wit, trp_xir ) SELECT b.tmp_ahr, " & _
                 "b.tmp_aml, b.tmp_anl, b.tmp_bhr, b.tmp_bnl, b.tmp_bml, b.tmp_cnm, b.tmp_int, " & _
                 "b.tmp_nik, b.tmp_pno, b.tmp_pnm, b.tmp_tno, b.tmp_typ, b.tmp_wdt, b.tmp_wid, " & _
                 "b.tmp_win, b.tmp_wir, b.tmp_wit, b.tmp_xar FROM tblMODfnr as b "
        SQLstr = "SELECT DISTINCT tmp_cnm FROM " & mySTBL & ";"
        Set dbs = CurrentDb
        Set WSp = DBEngine.Workspaces(0)
        Set RSc = dbs.OpenRecordset(SQLstr, dbReadOnly)
        With RSc
            For n = 1 To .RecordCount
                If IsNull(myOpts) Then
    '                DoCmd.OpenReport "repCLIsep"
                    DoCmd.OpenReport "repCLIsep", , , , acDialog
    '                DoCmd.OpenReport "repCLIsep", myOpts
                    DoCmd.OpenReport "repCLIsep", myOpts, , , acDialog
                End If
                RepClient = ![tmp_cnm]
                MsgBox "Client is => " & RepClient
                WHRstr = "WHERE ((b.tmp_cnm = '" & RepClient & "')) AND " & _
                         "(((b.tmp_wdt >= #" & FMTbeg & "# AND b.tmp_wdt <= #" & FMTend & "#)) OR " & _
                         "((b.tmp_ted >= #" & FMTbeg & "#) AND (b.tmp_ted <= #" & FMTend & "#))) " & _
                         "ORDER By [tmp_wdt] desc;"
                SQLstm = SQLstm & WHRstr
                DoCmd.SetWarnings False
                DoCmd****nSQL "DELETE * FROM tblTIMrep"
                DoCmd****nSQL SQLstm
                DoCmd.SetWarnings True
                If IsNull(myOpts) Then
    '                DoCmd.OpenReport "repTIMsht"
                    DoCmd.OpenReport "repTIMsht", , , , acDialog
    '                DoCmd.OpenReport "repTIMsht", myOpts
                    DoCmd.OpenReport "repTIMsht", myOpts, , , acDialog
                End If
            Next n
        End With
        Set RSc = Nothing
        Set WSp = Nothing
        Set dbs = Nothing
    End Sub
    I did not include the code for the first two modules as they just build tables and save the data needed for the reports to run and work fine.

    As you can see I orginally did not have "acDialog" on my pop-up form "pfmPRTopts", or my reports and when I added it flow stopped at the form until I set the value and flow was sequential for the reports.

    But this report, with the dates I've selected for it, has 3 clients on the form, so my "FOR" loop in the last subroutine, should loop, but it is not.

    Therefore I put in the "msgbox" statement just to track and prove it is somehow taking a false exit.

    Any suggestions as to why?


    Last edited by dbsupport4me; 07-31-09 at 00:35.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    I'd argue thats a flaw in your design, or your understanding of how VBA works.

    if you want execution to pause to allow user input then you would need to call the report from the form. the code will always run . you may be able to get round that by making a form modal (not too sure if you can do that BTW in Access VBA)

    so either you need to rethink how the reports are run...

    I think the options are either to call the reports from the form not a piece of code such as you have.. could be you populate a list box on a form and then iterate through the list from the list box or you generate a report queue where the user can request whatever reports they want in whatever style they want
    or you run your code module with a flag which indicates iof a user wants to preview or no

    what ever you do do its up to you.

    I've never had a requirement to do this sort of thing, perhaps one of the experts may be able to help you further
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts