batch printing (1 Viewer)

[gfultz]

Hi All,

I have a report that works flawlessly when a user runs it and enters the specific job number they want the report on. What I'd like to do now is allow the user to run a batch of these reports by Client. (Clients own the Jobs). In order to do this, I created a form in which the user selects the printer, the client, and which report they want to run in the batch. They can click a run button that is supposed to automate this process, grabbing all of the relevant job numbers and then running through the recordset, auto-completing the form for the user. However, the form never seems to receive the code. The form contains one ComboBox which is unbound.

    Dim RptNm As String
    Dim PrtNm As String
    Dim Rpt As Report
    Dim Prt As Printer
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim WAIT As Double
    
  
    RptNm = Me.cboReport
    PrtNm = Me.cboPrinter
    
    Set rst = CurrentDb.OpenRecordset("SELECT Job FROM JobDataBATCH WHERE ClientID=" & Me.ClientID)
    If rst.BOF And rst.EOF Then
    MsgBox ("No Records Found")
    Else
    rst.MoveFirst
    
    Set Prt = Application.Printers(PrtNm)
    Set Application.Printer = Prt
            
    Do While Not rst.EOF
        DoCmd.openReport RptNm, acViewNormal 
        Forms!JobLkUp.CoJob.SetFocus
        Forms!JobLkUp.CoJob = rst!Job
        Call byjob_OK
        WAIT = Timer
        While Timer < WAIT + 2
        DoEvents
        Wend
        rst.MoveNext
    Loop
    End If
    rst.Close

rst!Job is the field that is quivalent to the JobNoID field that the cbobox is bound to, the number the user sees in the cbobox is a more friendly job number. The cbobox is getting the two fields from a query of a sql server table.

Hopefully I covered everything.

Garrett

 

[HiTechCoach]

Hi All,

I have a report that works flawlessly when a user runs it and enters the specific job number they want the report on. What I'd like to do now is allow the user to run a batch of these reports by Client. (Clients own the Jobs). In order to do this, I created a form in which the user selects the printer, the client, and which report they want to run in the batch. They can click a run button that is supposed to automate this process, grabbing all of the relevant job numbers and then running through the recordset, auto-completing the form for the user. However, the form never seems to receive the code. The form contains one ComboBox which is unbound.

    Dim RptNm As String
    Dim PrtNm As String
    Dim Rpt As Report
    Dim Prt As Printer
    Dim rst As DAO.Recordset
    Dim strFilter As String
    Dim WAIT As Double
    
  
    RptNm = Me.cboReport
    PrtNm = Me.cboPrinter
    
    Set rst = CurrentDb.OpenRecordset("SELECT Job FROM JobDataBATCH WHERE ClientID=" & Me.ClientID)
    If rst.BOF And rst.EOF Then
    MsgBox ("No Records Found")
    Else
    rst.MoveFirst
    
    Set Prt = Application.Printers(PrtNm)
    Set Application.Printer = Prt
            
    Do While Not rst.EOF
        DoCmd.openReport RptNm, acViewNormal 
        Forms!JobLkUp.CoJob.SetFocus
        Forms!JobLkUp.CoJob = rst!Job
        Call byjob_OK
        WAIT = Timer
        While Timer < WAIT + 2
        DoEvents
        Wend
        rst.MoveNext
    Loop
    End If
    rst.Close

rst!Job is the field that is quivalent to the JobNoID field that the cbobox is bound to, the number the user sees in the cbobox is a more friendly job number. The cbobox is getting the two fields from a query of a sql server table.

Hopefully I covered everything.

Garrett

Garrett,

Since we can not see all the code used for this routine, it is not impossible to trace through the code to spot all possible issues.

Here is an example I have for this:

Batch Printing

 

[gfultz]

Thanks for the post. What you linked to works for that particular application, but for this I need to set the filter criteria to limit my result set in the sub report. I am pulling data from the MDB and a sql server and then having to use a UNION ALL on an empty table to add concatenated notes that regularly exceed 255 chars. By using the same field as the filter criteria for the main query and sub-query i have been able to alleviate the problem of the reports being REALLY SLOW.

As far as not being able to see "everything", this really is it. The report opens and calls the JobLkUp form and the form has a cboBox that queries the sql server for all jobs. the user can type in the friendly job number and click ok which sets the parameter by committing the value and hiding the form. The report closes the form after completing the report.

I have been able to prove that it gets to the form. I pick a different printer and it starts to spool to that printer no problem. However, when the report calls the form, I cannot get it to accept input from the vba code. I have tried to pause the code while the cboBox was querying. I have manually tried to set the focus to the field and then set the value. I cannot figure out why I cannot update the cboBox value via VBA. I can manually update by setting the timer longer and it works. I have never had this issue before.

 

[gfultz]

I was able to isolate the problem to the CboBox. I copied my report and query and changed the form to one with a text box and had the code call up the form. This happens so fast the user can't even see the form and the batch printing is working!