One Report, Multiple Queries

I am a die hard Filemaker user/designer. For MS Access – building ONE report that can be “fed” with many different SQL or queries seems like a straight forward idea – but it isn’t. Here is how to get around it … kind of…

This is the site that gave me the goods – there is another, but that was too long ago.

Basically – you have buttons NOT on the current form (this is unfortunate)

• close the form of interest
• reopen the form with the DoCmd but with arguments
• The value of the argument is then used in the Report_Open event – THIS is where you set your query or record source SQL.

Example: A navigation form contains 2 buttons – one for query A and another for query B. We are trying to open a Report called “Questions Test”

Private Sub NavigationButton24_Click()
DoCmd.Close acReport, "Questions Test", acSaveYes
DoCmd.OpenReport "Questions Test", acViewReport, , , , ""
End Sub

Private Sub NavigationButton30_Click()
DoCmd.Close acReport, "Questions Test", acSaveYes
DoCmd.OpenReport "Questions Test", acViewReport, , , , "Not Answered"
End Sub

Private Sub Report_Open(Cancel As Integer)

If IsNull(Me.OpenArgs) Then
'Me.RecordSource = Me.OpenArgs
     Else
         If Me.OpenArgs = "Not Answered" Then
             Me.RecordSource = "qryQuestionsNotAnswered"
         Else
             Me.RecordSource = "qryQuestionsAll"
         End If
     End If



End Sub