Choose a report by field value

[McFestoe]

Can you have access decide which report to run depending on a value of a field

I have the following code on a button and iam trying to add to it to be able to

Print report 1 if SYSTEM TYPE = a
Print Report 2 if SYSTEM Type = b
up to report 10
as well as the following

Private Sub Command374_Click()

Dim strWhere As String
strWhere = "[ID]=" & Me.ID
DoCmd.OpenReport "Paperwork Checklist", acViewNormal, , strWhere
DoCmd.OpenReport "Paperwork Handover", acViewNormal, , strWhere


End Sub

Thanks

 

[dhookom]

Are all 10 reports different reports with different formats? If so, you can try code like:

Dim strRptName as String
SELECT CASE Me.txtSystemType
  Case "a"
    strRptName = "rptForSystemA"
  Case "b"
    strRptName = "rptForSystemB"
  ...
End Select
DoCmd.OpenReport strRptName, acPreview

I'm not sure if there is and issue or question in the last part of your posting.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[McFestoe]

Duane

Yes all reports are different due to the satge a job is at, which is selected from a combo box on my main form.

Issue or question in the last part of the posting ? not shure what you mean its probally me not very good at putting ideas in to words.


Richard

 

[dhookom]

I didn't know if the strWhere was needed in the code. Does the Select Case answer your question?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[McFestoe]

Duane

The strWhere was needed to print out a report for the current customer else it printed out every record (lost half a tree) in paper.

This is what i have change it to

Private Sub Command374_Click()
Dim strRptName As String
Dim strWhere As String

strWhere = "[ID]=" & Me.ID
Select Case Me.System_Type

Case "intruder"
strRptName = "Paperwork Handover"
Case "CCTV"
strRptName = "Paperwork Handover CCTV"
Case "Access"
strRptName = "Paperwork Handover Access"

End Select
DoCmd.OpenReport strRptName, acViewNormal, , strWhere


End Sub

Instead of listing ever type of system is there a way to cut it down like there is 20 different types of intruder system which only need the same report, so the first Case "intruder" is there a way of this picking out every system that has the word intruder in the system type ?

System Type

Intruder Alarm BS4737 Pt 1,3 & 4
Intruder Alarm Bs4737 Pt 2 DOD
Wirefree Intruder Alarm BS6799 (Class 111, 1V or V)
External Intruder Alarm

and it goes on for another 4 more but these only need the same report

where as CCTV and Access are different reports which works with the Case statement.

I have tried Case "int.*" which i thought was a wild card statement but it did not like it.

Richard

 

[dhookom]

If you have a "group" of system types that need the same report (a possibly other similar processes), you should consider adding a field to your system type table that uniquely identifies a reporting group. This would allow you to use data to determine the report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.