Open different reports based on the value in the form

[Merkit]

Hi!
I'm creating a simple data base which will store information on all instruments installed in our water treatment plant. I'm going to create a main form to browse the records. On it I'd like to have a button to open a specific report - a data sheet of the instrument which tag is currently displayed on the form. Since each instrument type requires its own data sheet template, my data base will need 10-15 reports - one report for each instrument type as per ISA-20-1981. For example, that button on the main form should open report S40, if the instrument type is "Pressure Transmitter", or report S42 if the instrument type is "Pressure Switch". I can add to the form a box with the report name, visible or hidden, if it will help to create a macro or a code to open the right report. Something like open report [name = form "main", box "report name"].
Any help/advice will be highly appreciated.
Merkit

[June7]

Reference a variable for the report name. The variable can be a combobox, listbox, textbox, user input to a popup, etc.
Combobox can be designed for user to see and select "Pressure Transmitter" but code uses "S40" as report name.
Example:
DoCmd.OpenReport Me.cbxReport

Simplest way to build a list for combobox is with a table. Do you have a table of these 10-15 reports that can be used as source for combobox list?

[Welshgasman]

Sounds like you will also need to pass in the insrument ID to the report?. So in your table I would expect you would have a field for report type, and just open that with the instrument ID.
I would be using a combo on the Instrument form to select what report type is asscociated with each instrument, or that instrument type.

[June7]

Good point.

Yes, for report on a specific instrument, user probably just needs to select instrument and code would do a lookup on that instrument to determine appropriate report. User should not have to also specify report. So, a combobox for selecting instrument could include a column for the report name.

Example:
DoCmd.OpenReport Me.cbxInstrument.Column(2), acViewPreview, , "InstrumentID=" & Me.cbxInstrument

If you want to provide db for analysis, follow instructions at bottom of my post.

[jojowhite]

see ReportType table that holds the Type and which Report to use.
open InstrumentsForm and see the VBA behind the Report command button.

[Merkit]

Reference a variable for the report name.

Thanks a lot for your help. I prepared a sort/test version of my data base for you to check since the code you suggested didn't work. More likely because I used a text box instead of a combo, as the table LIST already contains a variable for the report name - field DSFORM. I know it is not right to have both the instrument type and the report name in the same table. However, in the real life my table LIST is a linked existing Excel file with both columns. I guess, this will simplify the code I need.
Kind regards

[Merkit]

Sounds like you will also need to pass in the instrument ID to the report?

There will be a tag number displayed on the report. A query does the job pretty well. But tank you anyways.
Kind regard

[Merkit]

see the VBA behind the Report command button.

Cool! Thank you. What if my form already contains the report name? It comes form the linked existing Excel file.

[jojowhite]

here test this.

[mike60smart]

Hi
Why have you no relationships set between any of your tables?

[Merkit]

Thanks a million! Works great!

[Welshgasman]

here test this.

Arnel, DB does not compile.

Public Function IsReportLoaded(ByVal sReportName As String) As Boolean
    On Error GoTo Exit_Func
    Dim tf As Boolean
    With CurrentProject.AllReports(sReportName)
        tf = .IsLoaded And .CurrentView <> acCurViewDesign
    End With
    IsFormLoaded = tf
    Exit Function
Exit_Func:
End Function

You copied IsFormLoaded but did not change it to IsReportLoaded.
O/P, you will need to fix that.

[jojowhite]

he can remove the function, it is not being used at the moment.