Printing Process Flow

[OldManRiver]

All,

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)
     Else
         Call Run_Rep("repINVreview", Null)
     End If
     If PrtOps = 3 Or PrtOps = 4 Then
         Call Run_TS("tblMODfnr", acViewPreview)
     Else
         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
     Else
 '        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
         .MoveLast
         .MoveFirst
         For n = 1 To .RecordCount
             If IsNull(myOpts) Then
 '                DoCmd.OpenReport "repCLIsep"
                 DoCmd.OpenReport "repCLIsep", , , , acDialog
             Else
 '                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.RunSQL "DELETE * FROM tblTIMrep"
             DoCmd.RunSQL SQLstm
             DoCmd.SetWarnings True
             If IsNull(myOpts) Then
 '                DoCmd.OpenReport "repTIMsht"
                 DoCmd.OpenReport "repTIMsht", , , , acDialog
             Else
 '                DoCmd.OpenReport "repTIMsht", myOpts
                 DoCmd.OpenReport "repTIMsht", myOpts, , , acDialog
             End If
             .MoveNext
         Next n
         .Close
     End With
     DoEvents
     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?

Thanks!

OMR

 

[DCrake]

You can enter DoEvents at strategic parts of the code to render Access stationary whilst the execution catches up with the code process. Once it has completed the process the code will resume execution.

David