A single report for several queries

[Guest]

I have several queries that all return the same data fields and require the
same report layout. I was just wondering if there was a way to simply have
one report instead of a report for each query. At the moment i simply have a
form with several buttons corresponding to the options and a seperate report
for each option. Is there a simple way to have just a single report?

Any help would be much appreciated.
Cheers.

 

[Allen Browne]

You can set the RecordSource of the report to the desired query in its Open
event procedure.

If these are just different filters, you might be able to get away with
leaving the criteria out of the query, and applying a WhereCondition to the
OpenReport action. Then you don't need to switch queries.

 

[Guest]

Thanks Allen, will give it go. Perhaps a If then else type statement in the
OpenReport action would be sufficient?

 

[Guest]

This is the code i tried but all i get are errors, my coding knowledge isnt
great, any ideas?
I have many more querys to add but obviously so long as a get the first
couple to work the others are identical.


Private Sub Report_Open(Cancel As Integer)

If Forms!SpecIntSearch!txtArgument = Wool Then
Me!RecordSource = "WoolQuery"

ElseIf Forms!SpecIntSearch!txtArgument = Footware Then
Me!RecordSource = "FootwareQuery"

ElseIf Form!SpecIntSearch!txtArgument = Cotton Then
Me!RecordSource = "CottonQuery"

End If
End If
End If

End Sub

Cheers.

 

[John Spencer]

Correcting the syntax of the bit of code you have shown

Private Sub Report_Open(Cancel As Integer)

If Forms!SpecIntSearch!txtArgument = "Wool" Then
Me!RecordSource = "WoolQuery"

ElseIf Forms!SpecIntSearch!txtArgument = "Footware" Then
Me!RecordSource = "FootwareQuery"

ElseIf Form!SpecIntSearch!txtArgument = "Cotton" Then
Me!RecordSource = "CottonQuery"

End If
....

 

[Allen Browne]

The literal text value in the text boxes need to be in quotes.
Also, there's only one EndIf after the If, i.e. the ElseIf does not need an
EndIf of its own.

Private Sub Report_Open(Cancel As Integer)
If Forms!SpecIntSearch!txtArgument = "Wool" Then
Me!RecordSource = "WoolQuery"
ElseIf Forms!SpecIntSearch!txtArgument = "Footware" Then
Me!RecordSource = "FootwareQuery"
ElseIf Form!SpecIntSearch!txtArgument = "Cotton" Then
Me!RecordSource = "CottonQuery"
Else
MsgBox "I don't know what query to use."
Cancel = True
End If
End Sub

 

[Guest]

Ahh thankyou, when i changed it to “Wool†for example i recieve no errors but
when the report opens i simply have #name? appearing where my data should, i
know my querys are correct so is there something i am still missing?

 

[Allen Browne]

Do all these queries have EXACTLY the same field names?

Or could there be something in the ControlSource of a text box that does not
match the name of a field in your query?

 

[Guest]

Yeah all queries have exactly the same field names, and doubled checked the
ControlSource's and everything seems to be in order but still im getting
#name? instead of the required results.

 

[Allen Browne]

#Name means that you are incorrectly referring to something.

Does this happen only when there are no records returned?
YES:
Test the HasData property of the report.
Example: if you currently have a box with Control Source of:
=[Quantity] * [PriceEach]
change it to:
=IIf([Report].[HasData], [Quantity] * [PriceEach], Null)

NO:
Then does this happen on all fields?
YES:
To trace what, manually set the Control Source the query you want, and
temporarily comment out the code in Report_Open. Get the report working with
that query.

NO:
There is a problem in the way you are referring to a particular
field/parameter/object/value/expression.

 

[Guest]

Unfortunately it happens on all fields under all circumstances. And when i
set the control source to the required query and comment out the code
everything works fine, in fact all the query's are working perfectly.

#Name means that you are incorrectly referring to something.

Does this happen only when there are no records returned?
YES:
Test the HasData property of the report.
Example: if you currently have a box with Control Source of:
=[Quantity] * [PriceEach]
change it to:
=IIf([Report].[HasData], [Quantity] * [PriceEach], Null)

NO:
Then does this happen on all fields?
YES:
To trace what, manually set the Control Source the query you want, and
temporarily comment out the code in Report_Open. Get the report working with
that query.

NO:
There is a problem in the way you are referring to a particular
field/parameter/object/value/expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yeah all queries have exactly the same field names, and doubled checked
the
ControlSource's and everything seems to be in order but still im getting
#name? instead of the required results.

 

[Allen Browne]

First up, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
If you want to know why:
http://allenbrowne.com/bug-03.html
Then compact the database.

Now, save the report with its RecordSource property blank, i.e. it has no
idea about what data to expect until the Form_Open event runs.

Now, Add this to the end of the Form_Open event procedure:
Debug.Print Me.RecordSource

When it runs (and errors out), press Ctrl+G to open the Immediate Window,
and see if the correct query name is printed.

Continue with that kind of tracing through to see what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately it happens on all fields under all circumstances. And when i
set the control source to the required query and comment out the code
everything works fine, in fact all the query's are working perfectly.

#Name means that you are incorrectly referring to something.

Does this happen only when there are no records returned?
YES:
Test the HasData property of the report.
Example: if you currently have a box with Control Source of:
=[Quantity] * [PriceEach]
change it to:
=IIf([Report].[HasData], [Quantity] * [PriceEach], Null)

NO:
Then does this happen on all fields?
YES:
To trace what, manually set the Control Source the query you want, and
temporarily comment out the code in Report_Open. Get the report working
with
that query.

NO:
There is a problem in the way you are referring to a particular
field/parameter/object/value/expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yeah all queries have exactly the same field names, and doubled checked
the
ControlSource's and everything seems to be in order but still im
getting
#name? instead of the required results.

:

Do all these queries have EXACTLY the same field names?

Or could there be something in the ControlSource of a text box that
does
not
match the name of a field in your query?

Ahh thankyou, when i changed it to "Wool" for example i recieve no
errors
but
when the report opens i simply have #name? appearing where my data
should,
i
know my querys are correct so is there something i am still missing?

 

[Guest]

Ahh, turns out its not setting any record source at all, guess now i will
just have to backtrack and find out why.

Thankyou very much for all the help, i hope now i can get it all sorted

Cheers.

First up, make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
If you want to know why:
http://allenbrowne.com/bug-03.html
Then compact the database.

Now, save the report with its RecordSource property blank, i.e. it has no
idea about what data to expect until the Form_Open event runs.

Now, Add this to the end of the Form_Open event procedure:
Debug.Print Me.RecordSource

When it runs (and errors out), press Ctrl+G to open the Immediate Window,
and see if the correct query name is printed.

Continue with that kind of tracing through to see what's wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Unfortunately it happens on all fields under all circumstances. And when i
set the control source to the required query and comment out the code
everything works fine, in fact all the query's are working perfectly.

#Name means that you are incorrectly referring to something.

Does this happen only when there are no records returned?
YES:
Test the HasData property of the report.
Example: if you currently have a box with Control Source of:
=[Quantity] * [PriceEach]
change it to:
=IIf([Report].[HasData], [Quantity] * [PriceEach], Null)

NO:
Then does this happen on all fields?
YES:
To trace what, manually set the Control Source the query you want, and
temporarily comment out the code in Report_Open. Get the report working
with
that query.

NO:
There is a problem in the way you are referring to a particular
field/parameter/object/value/expression.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yeah all queries have exactly the same field names, and doubled checked
the
ControlSource's and everything seems to be in order but still im
getting
#name? instead of the required results.

:

Do all these queries have EXACTLY the same field names?

Or could there be something in the ControlSource of a text box that
does
not
match the name of a field in your query?

Ahh thankyou, when i changed it to "Wool" for example i recieve no
errors
but
when the report opens i simply have #name? appearing where my data
should,
i
know my querys are correct so is there something i am still missing?