Using 1 report form for several queries

[Jackie]

I have 1 report form that contains the same data fields for different queries.&nbsp;&nbsp;How do I tell the report form on which query to base the report?&nbsp;&nbsp;Each query is based on a different transaction code.&nbsp;&nbsp;I want to avoid creating several versions of the same report form to accomodate the different queries.<br>

 

[elizabeth]

Create a form to get input from the user before you open the report. Then use that input to load the Report's RecordSource property.<br><br>1. Create a form (I'm calling it frmPrompt4Query).<br>2. Create an option group (I'm caling it fraTxnCode) or other control to allow the user to select the proper query.<br>3. Create a new button on the form to open the report. <br>4. Add this code to the Report's OnOpen event: (may need a little debugging as I've modified an existing snippet for you):<br><br>On Error GoTo Err_cmdOpenReport_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;'Open form for user input before opening report.<br>&nbsp;&nbsp;&nbsp;&nbsp;If Not (IsLoaded(&quot;frmPrompt4Query&quot) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Cancel = True<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MsgBox &quot;To print this report, use Form frmPrompt4Query&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strDocName As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strCriteria As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With Me<br>&nbsp;&nbsp;&nbsp;&nbsp;Select Case .fraTxnCode<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Is = 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strCriteria = &quot;tblWhatever.TxnCode='A'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Is = 2<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strCriteria = &quot;tblWhatever.TxnCode='B'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Case Is = 3<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strCriteria = &quot;tblWhatever.TxnCode='C'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End Select<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strDocName = &quot;rptWhatever&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;DoCmd.OpenReport strDocName, acViewPreview, , strCriteria<br><br>Exit_cmdOpenReport_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br><br>Err_cmdOpenReport_Click:<br>&nbsp;&nbsp;&nbsp;&nbsp;MsgBox Err.Description<br>&nbsp;&nbsp;&nbsp;&nbsp;Resume Exit_cmdOpenReport_Click<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub

 

[DougP]

Can you have on query that Prompts for the transaction code?<br><br>You know you can probably make 1 query that has several variable perameters in it.<br>So one query would return many differnt things based on Prompting the user when it run or makin selections from a form.<br>For all 3 examples below put the following in the &quot;Criteria&quot;:<br><br>To prompt a User <br>[Enter transaction code]<br><br>based on a textbox on a form:<br>forms![Formname]![textboxname]<br><br>Looking at a Combo box:<br>[Forms]![Form1]![Combo10]<br>Note the value must be in the &quot;Bound&quot; Column<br>

 

[elizabeth]

Actually, what DougP suggests is what I'm doing, I should've named frmPrompt4Query frmPrompt4Filter. The OptionGroup is collecting a parameter for the filter. DougP's method is much simpler (better!) if the user can be relied on to input the correct value.

 

[Dumboy]

WOW......! <p>Dumboy.! Ouch.! .my brain.....<br><a href=mailto:spiderdesign@yahoo.com>spiderdesign@yahoo.com</a><br><a href= > </a><br>It's hard to think with only one neuron......Ouch.....!....it hurts when I think......!