Multiple Report

[Acropolis]

I have made a DB for managing the field works we do in the company.

There are several different job types, and each job type will require a different worksheets to be printed for the engineer.

An engineer could go out with several different job types to do each day.

I have a table - tblFieldWorks which contains the job information, with links to other tables, one of fields been a link to FieldJobTypeID in the tblFieldWorksJobTypes which lists the various job types.

What I want to be able to do, is selcet an engineer(s), select a date or a date range (that part I have got already) and hit print, and produce a single report which has all the worksheets for that engineer(s) which I can then print, we keep a copy as a printed PDF as well which is emailed out to the engineers sometimes.

Any suggestions on how I can go about this. I am fairly new to Access and know a little VAB and other bits but not much. I am guessing I could add another field to the tblFieldJobTypes which says the report(worksheet) it needs, but how would I nest the reports inside a master report? Or I am barking up the wrong tree. Any help very mcuh appreciated.

Thanks

 

[billmeye]

Have you worked with queries? You can link your tables in a query, use the forms date fields and engineer as criteria in your query and then build a report using the query as the source and use the report wizard to make it easy to make the report or use your existing report but base the record source on the query.

 

[Acropolis]

It currently produces a worksheet for the engineer, which is based on several queries, in fact the report has 3 sub reports in it with various bits of information.

What I want to be able to do to is produce several difference worksheets from the one selection.

So I select engineer A for today for example, who has Job Type 1, 2 and 3, select print and it produces the 3 different worksheets. Rather than having to select each one to print, eg Produce worksheet for JOb Type1, then 2 then 3.

Hope that makes sense.

 

[cricketbird]

The code below is from one of my projects where people prepare meat and vegetable products at different stations (tables). It does some of what you want to do, and I think you could modify it for your needs.

First, the code looks to a form to see which stations need to be printed.

Then it checks to see which day of the week a given date is (we print orders ahead of time), and only prints the parts of those reports that are relevant for that day of the week. For your situation, you might substitute your people's names for the days of the week and use a combo box to select them rather than my convoluted system to get the weekday.

Finally, meat jobs are different than vegetable jobs, and get entirely different labels (reports). This might be another way you differentiate between different types of jobs.

Finally, it opens up these reports in print preview.

Private Sub PrintPrepCards_Click()
On Error GoTo Err_PrintPrepCards_Click

    Dim stDocName As String
    Dim TheWeekday As Integer
    Dim stDayLetters As String
    Dim strWhereDay As String
    Dim strWhereTable As String
    
    TheWeekday = Weekday(LabelDate)
    strMeatName = "LABEL REPORT - MEAT"
    strVegName = "LABEL REPORT - VEGETABLE TABLE"

     
'Figure out what day of the week the date specified on the form is and translate that to letters (field names)

Select Case TheWeekday
    Case 1
        stDayLetters = "SU"
    Case 2
        stDayLetters = "M"
    Case 3
       stDayLetters = "T"
    Case 4
       stDayLetters = "W"
    Case 5
       stDayLetters = "R"
    Case 6
       stDayLetters = "F"
    Case 7
       stDayLetters = "S"
End Select

'Set the WHERE statement to only pick the right day of the week:

strWhereDay = "([DIET SUB TABLE]." & stDayLetters & "=True)"

 
'Set the WHERE statement to only pick the tables checked on the form
myVar = ""
 
If Forms![WELCOME SWITCHBOARD].Check2.Value = True Then
myVar = "([DIET TABLE].TABLEID = 2) OR "

End If

If Forms![WELCOME SWITCHBOARD].Check4.Value = True Then
myVar = myVar & " ([DIET TABLE].TABLEID = 4) OR "
End If


If Forms![WELCOME SWITCHBOARD].Check9.Value = True Then
myVar = myVar & " ([DIET TABLE].TABLEID = 9) OR "
End If


'If there are checked boxes on any of these tables, then myVar will be longer than three characters

If Len(myVar) > 3 Then

'Set the WHERE statement to only pick the tables selected, and strip the final " or" from the tables
strWhereTable = myVar
strWhereTable = "(" & Left(myVar, Len(myVar) - 4) & ")"
strWhereBoth = strWhereDay & " AND " & strWhereTable

DoCmd.OpenReport strVegName , acPreview, , strWhereBoth
End If


'Print the meat labels
If Forms![WELCOME SWITCHBOARD].Check1.Value = True Then

    DoCmd.OpenReport strMeatName, acPreview, , strWhereDay
End If

Exit_PrintPrepCards_Click:
    Exit Sub

Err_PrintPrepCards_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_PrintPrepCards_Click
    
End Sub

Let me know if this was useful to you.
Cheers,
CB