Code to Print Multiple Jobs

T

tucker61

Registered User.
Local time
Today, 11:12
Joined
Jan 13, 2008
Messages
344
i have some code (Below) that is on my main form, so i can print out individual jobs.

If i need to print out 10 jobs, i have to go into them all individually, and press the button. (I currently have to do this for 66 jobs outstanding)

I would like to do this by being able to select from a list box (I can set this up), so i can select the job numbers and print out all the jobs i need. (this is the bit i am struggling with.

what do i need to change in the code below to loop through all the jobs that i have selected in the listbox ?


Code: 
Private Sub btnPrint_Click()

    If (Not chkJob) Then
        MsgBox "Nothing Selected!", vbExclamation + vbOKOnly, "No Selection!"
        Exit Sub
    End If
    Me.Visible = False
    WaitOn "Creating Print Job . . ."
    If chkJob Then
        PrintReport "rptViewJob"
    End If
    DoCmd.Close acForm, "frmPrint"
    Waitoff
End Sub
 
Here is some code I use for the same process.
ListCount and Selected are the key parts.

HTH
Code: 
Private Sub cmdCreate_Click()
On Error GoTo ErrHandler
Dim db As Database
Dim rstDays As Recordset
Dim strSQL As String, strStartDate As String, strEnddate As String
Dim i As Integer, iRecordsAdded As Integer
Dim lngEmployee As Long

strStartDate = Format(Me.txtDateBW, strcJetDate)
strEnddate = Format(Me.txtDateWE, strcJetDate)
Set db = CurrentDb()

SetStatusBar ("Creating Timesheets .....")
' Now loop through selected listbox items
For i = 0 To LstEmployee.ListCount - 1
    If LstEmployee.Selected(i) Then
        LstEmployee.Selected(i) = False
        lngEmployee = LstEmployee.Column(0, i)
        SetStatusBar ("Creating Timesheet for " & LstEmployee.Column(1, i))
        
        'strSql = "INSERT INTO tblemployeeday ( EmployeeID, DayID, DayDate, StartTime, EndTime, Lunch, DateType )"
        'strSql = strSql & " SELECT tbDfltlHours.EmployeeID, tblDates.DayID, tblDates.DayDate, tbDfltlHours.StartTime, tbDfltlHours.EndTime, tbDfltlHours.Lunch, tblDates.DayTypeID"
        'strSql = strSql & " FROM tbDfltlHours, tblDates"
        'strSql = strSql & " WHERE (((tbDfltlHours.EmployeeID)=" & lngEmployee & ")"
        'strSql = strSql & " AND ((tblDates.DayDate) Between " & strStartDate & " And " & strEnddate & ")"
        'strSql = strSql & " AND ((tbDfltlHours.HoursDayNum)=Weekday([tbldates.daydate])))"
        'strSql = strSql & " ORDER BY tblDates.DayID"
        ' Now take into account any startdate midweek
        strSQL = "INSERT INTO tblemployeeday ( EmployeeID, DayID, DayDate, StartTime, EndTime, Lunch, DateType )"
        strSQL = strSQL & " SELECT tbDfltlHours.EmployeeID, tblDates.DayID, tblDates.DayDate, tbDfltlHours.StartTime, tbDfltlHours.EndTime, tbDfltlHours.Lunch, tblDates.DayTypeID"
        strSQL = strSQL & " FROM tblDates, tblEmployee INNER JOIN tbDfltlHours ON tblEmployee.EmployeeID = tbDfltlHours.EmployeeID"
        strSQL = strSQL & " WHERE (((tbDfltlHours.EmployeeID)=" & lngEmployee & ")"
        strSQL = strSQL & " AND ((tblDates.DayDate) Between " & strStartDate & " And " & strEnddate & ")"
        strSQL = strSQL & " AND ((tbDfltlHours.HoursDayNum)=Weekday([tbldates.daydate]))"
        strSQL = strSQL & " AND ((tblDates.DayDate)>=[tblEmployee].[StartDate]))" ' Start date check
        strSQL = strSQL & " AND ((tblDates.DayDate)<= NZ([tblEmployee].[EndDate],#12/31/2100#))" ' End date check
        strSQL = strSQL & " ORDER BY tblDates.DayID"
        db.Execute strSQL, dbFailOnError
        iRecordsAdded = iRecordsAdded + db.RecordsAffected
    End If
Next i
MsgBox "Timesheets completed - " & iRecordsAdded & " records added"
SetStatusBar (" ")

ExitSub:
    Set db = Nothing
    Set rstDays = Nothing

    
Err_Exit:
    Exit Sub
    
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitSub
End Sub
 
are you filtering the Report by the Form's
TExtbox?

If you are you need to add an Unbound
textbox to your form. then change your report/query of report
to filter on this textbox.
the thing is we will fill this textbox with each
selection on your listbox. you may make the textbox
hidden by setting its Visible Property to No.

we will change also the way you print the report.
Code: 
Private Sub btlPrint_Click()
Dim var As Variant
' print the report
' but first check if there is a selection
' on the Listbox
If Me.lstSelection > -1 'lstSelection is my listboxname 
	' i will call the Unbound textbox as
	' txtUnbound, you must set the criteria
	' Of the report/Query to point to this
	' txtUnbound textbox, eg:
	'
	' Where [CustomerName] = '" & Forms!yourForm!txtUnbound & "'"
	'
	Me.Visible = False
	WaitOn "Creating Print Job(s) ..."
	For Each var in Me.lstSelection.ItemsSelected
		Me.txtUnbound = Me.lstSelection.ItemData(var)
		PrintReport "rptViewJob"
	Next
	WaitOff
	DoCmd.Close acForm, "frmPrint"
End If
End Sub
 
Thanks for the code, still struggling. Here is my updated code.


Code: 
Private Sub btlPrint_Click()
Dim var As Variant
If Me.lstselection > -1 Then
    Me.Visible = False
    WaitOn "Creating Print Job(s) ..."
    For Each var In Me.lstselection.ItemsSelected
        Me.tbJobID = Me.lstselection.ItemData(var)
        PrintReport "rptViewJob"
    Next
    Waitoff
    DoCmd.Close acForm, "frmPrint"
End If

End Sub

Me.lstselection is showing a null value even if i have selected some records from the listbox.
 
Try this.
Changes shown in RED
Assumes JobID is a number - if text use text delimiters

Code: 
Private Sub btlPrint_Click()
Dim var As Variant, [COLOR="Red"]lngJobID As Long[/COLOR]

If Me.lstselection > -1 Then
    Me.Visible = False
    WaitOn "Creating Print Job(s) ..."
    For Each var In Me.lstselection.ItemsSelected
        [COLOR="red"]lngJobID = Me.lstselection.ItemData(var)
        DoCmd.OpenReport "rptViewJob", acViewPreviewl, ,  "JobID = " & lngJobID, acHidden
        DoCmd.SelectObject acReport, "rptViewJob"
        DoCmd.PrintOut acSelection
        DoCmd.Close acReport, "rptViewJob"[/COLOR]
    Next
    Waitoff
    DoCmd.Close acForm, "frmPrint"
End If

End Sub

NOTE
The 4 DoCmd lines open the report filtered to the record required and hidden, selects it, prints it & closes it.
Your PrintOut line may be doing something similar already
 
Still got the same issue with the
Code: 
IF me.lstselection > -1 Then

If i hover over the me.lstselection it says the value is Null.
 
Why is it -1? Do you mean

Code: 
If Me.LstSelection.ItemsSelected.Count>1 Then
 
I used the - 1 as suggested by arnelgp up above. > 1 works. or >0
 
tucker61 said:
Still got the same issue with the
Code: 
IF me.lstselection > -1 Then
If i hover over the me.lstselection it says the value is Null.
Click to expand...

That says the same for one of my lists with everything selected.
 
Sorry should have been
Me.lstSelection.ListIndex> -1
 
No worries. The forum figured it out eventually..
 
FYI,
If there is some criteria in a table that will allow you to identify which jobs you want to print, you can use a query with criteria to provide the data for the print loop rather than individually selecting items in a listbox and then using the multi-select listbox.
 
I do have a query that pulls all open jobs together in one place.

I was thinking of creating a cascading combo to filter my criteria and then the list box for the final selections.

How would I do this using your method Pat Hartman?
 
You can use the hard-coded criteria in the RowSource of the combo and then take the final step by selecting multiple items in a combo. I was just suggesting the query method in case you always wanted all "open" orders or all "shipped" orders or something where criteria would be able to always pick exactly the items you wanted to print.
 
ridders said:
Try this.
Changes shown in RED
Assumes JobID is a number - if text use text delimiters

Code: 
Private Sub btlPrint_Click()
Dim var As Variant, [COLOR="Red"]lngJobID As Long[/COLOR]

If Me.lstselection > -1 Then
    Me.Visible = False
    WaitOn "Creating Print Job(s) ..."
    For Each var In Me.lstselection.ItemsSelected
        [COLOR="red"]lngJobID = Me.lstselection.ItemData(var)
        DoCmd.OpenReport "rptViewJob", acViewPreviewl, ,  "JobID = " & lngJobID, acHidden
        DoCmd.SelectObject acReport, "rptViewJob"
        DoCmd.PrintOut acSelection
        DoCmd.Close acReport, "rptViewJob"[/COLOR]
    Next
    Waitoff
    DoCmd.Close acForm, "frmPrint"
End If

End Sub

NOTE
The 4 DoCmd lines open the report filtered to the record required and hidden, selects it, prints it & closes it.
Your PrintOut line may be doing something similar already
Click to expand...

Ridders - I have not been able to implement your code above as is seems to stop automatically after running the Docmd.OpenReport. Any ideas Why ? the report looks fine if i open it directly, and you are correct in assuming that the Job_ID is a number.

also, in the code above you had acViewPreviewl, I assumed this was a typo and that is should be acViewPreview ?

My amended code is below.

Code: 
If Me.lstselection.ItemsSelected.Count > 0 Then
    For Each var In Me.lstselection.ItemsSelected
        lngJobID = Me.lstselection.ItemData(var)
        DoCmd.OpenReport "rptViewJobList", acViewPreview, , "Job_ID= " & lngJobID, acHidden
        DoCmd.SelectObject acReport, "rptViewJobList"
        DoCmd.PrintOut acSelection
        DoCmd.Close acReport, "rptViewJobList"
    Next
End If
 
Ahhh...i'm from Bristol and we put l's on the end of everythingl
Sorry about the typol.

I don't normally use PrintOut and was trying to work with your code.
Try adding the line DoEvents after the first red line

If that doesn't solve it, try commenting out achidden in the first line and the next 2 lines

It should then open and close each report in turn
If not, I'll try and set up something similar here.
 
OK, managed to work through it, and your code is correct.

Fount that the issue is on some code that i have on the report. which is below.

Strange that this code works when i use the suggestion by arnelgp below, but not with your code.

This code is in the report header section and is used to pull back a picture of the item, and some additional Dlookup values for on the report.

If i hover over the Dlookup Values, and over the tbcatnopic value (this value is being pulled off this report), these are all showing as Null.

also noticed that when pressing F8 to progress through the code, it runs through this code twice.

Should i move this code to a different section of the report ?


Code: 
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim PicPath As String
Dim sURL As String
Dim sLocalFile As String
Dim i As Integer

tbcatnopic = Reports!rptViewJobList!tbCatNo & ".jpg"
If tbcatnopic <> "" Then
    sLocalFile = Environ("Temp") & "\QAPic.jpg"
    PicPath = ImagePath & tbcatnopic
    If Dir(PicPath) <> "" Then
        SaveFile PicPath, sLocalFile
    Else
        SaveFile CurrentProject.Path & "\nopicture.jpg", sLocalFile
    End If
    imgWeb.Picture = sLocalFile
    DoEvents
Else
imgWeb.Picture = ""
End If

tbClaimValue = Nz(DLookup("Claim_Value", "tblQCCharges", "Job_ID=" & tbJobID & " AND [Quote/Charge]=1"), "")
tbClaimValue = Format(tbClaimValue, "Currency")
TBClaimStatus = Nz(DLookup("Approved", "TBLQCCharges", "Job_ID=" & tbJobID & "AND [Quote/Charge]=1"), "")
If TBClaimStatus = 0 Or IsNull(TBClaimStatus) Or TBClaimStatus = "" Then
TBClaimStatus = "Not Approved"
Else
TBClaimStatus = "Approved"
End If

End Sub
 
No idea why it would work in one case but not the other
Similarly unable to tell why it runs twice from this code:

You have a missing space before AND in this line
Code: 
TBClaimStatus = Nz(DLookup("Approved", "TBLQCCharges", "Job_ID=" & tbJobID & [COLOR="Red"][B]"AND[/B][/COLOR] [Quote/Charge]=1"), "")

What is the datatype of approved?
If its boolean (yes/no), change the Nz part above from "" to 0

Then depending on the previous point, simplify this line
Code: 
If TBClaimStatus = 0 Or IsNull(TBClaimStatus) Or TBClaimStatus = "" Then

to
Code: 
If Nz(TBClaimStatus,0) = 0 Then
or
Code: 
If Nz(TBClaimStatus,"") = "" Then

HTH
 
Approved is a text field..

Thanks.. I will change the code tomorrow..
 
You must log in or register to reply here.

Similar threads

A
Display a list of previous jobs with same serial number on current job form
Replies
13
Views
363
andymartin3186
A
N
Solved How stop MS Access from printing blank page at the end of a report
Replies
6
Views
319
KitaYama
K
Juice
Printing html file to a specific printer
Replies
4
Views
339
arnelgp
arnelgp
S
extremely slow when exporting PDF
Replies
8
Views
442
spaLOGICng
S
C
Problem with 'Like' SQL filter
Replies
6
Views
225
Josef P.
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom