 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005A single report for several queries
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. 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.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >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 [quoted text clipped - 6 lines] > Any help would be much appreciated. > Cheers. Thanks Allen, will give it go. Perhaps a If then else type statement in the OpenReport action would be sufficient? > You can set the RecordSource of the report to the desired query in its Open > event procedure. [quoted text clipped - 13 lines] > > Any help would be much appreciated. > > Cheers. 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. 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 ... > This is the code i tried but all i get are errors, my coding knowledge > isnt [quoted text clipped - 20 lines] > > Cheers. 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 SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > This is the code i tried but all i get are errors, my coding knowledge > isnt [quoted text clipped - 20 lines] > > Cheers. 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? 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? SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > 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? 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? > [quoted text clipped - 6 lines] > > i > > know my querys are correct so is there something i am still missing? #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. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Yeah all queries have exactly the same field names, and doubled checked > the [quoted text clipped - 14 lines] >> > i >> > know my querys are correct so is there something i am still missing? 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. > [quoted text clipped - 35 lines] > >> > i > >> > know my querys are correct so is there something i am still missing? 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. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html 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 [quoted text clipped - 42 lines] >> >> > i >> >> > know my querys are correct so is there something i am still missing? 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 [quoted text clipped - 59 lines] > >> >> > i > >> >> > know my querys are correct so is there something i am still missing? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.