If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
Somebody has told me that they would like to generate their own reports in
Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#2
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
Access 2003, by the way
"HWhite" wrote: Somebody has told me that they would like to generate their own reports in Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#3
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
You can apply the form's filter as the report's WhereCondition when you open
it. The event procedure code for the button on your form would look like this: Dim strWhere as String If Me.FilterOn Then strWhere = Me.Filter End If DoCmd.OpenReport "Report1", acViewPreview, , strWhere There may be some more work to do if the form is filtered with combos that have the first column hidden. An alternative approach is to put some search boxes directly in the form itself, where the user can find them. You can easily set these up to avoid the problems with the combos. There is a bit of work in learning this approach, but it is invaluable for both search forms and flexible report filtering. It it sounds interesting, download this example: Search form - Handle many optional criteria at: http://allenbrowne.com/ser-62.html -- Allen 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. "HWhite" wrote in message ... Somebody has told me that they would like to generate their own reports in Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#4
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
Customized report/query based on user input
You might want to consider the Query By Form applet at http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH%20Query%20By%20Fo rm' ***FEATURES*** The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys. The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use. The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow he user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields. -- Duane Hookom MS Access MVP John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County HWhite wrote: Somebody has told me that they would like to generate their own reports in Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#5
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
Thank you Allen. That code worked perfectly.
So, that gets me through the part about filtering the data and then generating the report based on that filter, but I still need some assistance with prompting the user to provide up to 2 sorting criteria, and a prompt for the report name that will be placed in the report header. I tried a few things and they didn't work. I wish I knew code writing... Thanks again for your assistance!!! "Allen Browne" wrote: You can apply the form's filter as the report's WhereCondition when you open it. The event procedure code for the button on your form would look like this: Dim strWhere as String If Me.FilterOn Then strWhere = Me.Filter End If DoCmd.OpenReport "Report1", acViewPreview, , strWhere There may be some more work to do if the form is filtered with combos that have the first column hidden. An alternative approach is to put some search boxes directly in the form itself, where the user can find them. You can easily set these up to avoid the problems with the combos. There is a bit of work in learning this approach, but it is invaluable for both search forms and flexible report filtering. It it sounds interesting, download this example: Search form - Handle many optional criteria at: http://allenbrowne.com/ser-62.html -- Allen 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. "HWhite" wrote in message ... Somebody has told me that they would like to generate their own reports in Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#6
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
Thanks John. Looks very interesting! I'll have to play with it.
"John Spencer" wrote: Customized report/query based on user input You might want to consider the Query By Form applet at http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH%20Query%20By%20Fo rm' ***FEATURES*** The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys. The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use. The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow he user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields. -- Duane Hookom MS Access MVP John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County HWhite wrote: Somebody has told me that they would like to generate their own reports in Access. This person does not know access at all, so I need to come up with a way to make this as user friendly and efficient as possible. I'm working off one master table that uses one lookup table. This basically is a list of documents received, who they were from, what the date was, what it was about, document titles, legal issues, etc.... Legal stuff. Users are entering data into the database via an input form. My thought was to use this same form and utilize the Filter By Form feature. Here's what I've done and where I'm stuck: I created a read-only replica of the input form so no accidents would happen if they started typing into the form by accident. I created a button that puts the form into Filter By Form mode. This, unfortunately, makes the other command options on the page unavailable. I've tested several filtering criteria and it works fine. I want the person to then view the results in datasheet mode to make sure it is what they want. I've can do this. Here's where I'm stuck. If the data is what they want, they'll want a report from it. It is enough data that the datasheet view will not print well. I want them to click a button that saves the filter as a query (query name forced), and then prints a previously created report that is based on that query. It should prompt them for three things... 1) The report name, which will be placed in the report header automatically (need help with that). 2) Sorting criteria #1. 3) Sorting criteria #2. The sorting criteria is necessary because the data would need to be sorted by at least the date, and probably one other criteria and then the date as a secondary sort. Ideally, they would choose these sorting options from a drop down box. I'm frustrated because the "save to query" option disappears when I apply the filter... which means it would need to be saved before the filter is applied. Backwards, but I can live with it as long as I can figure out how to force the query name behind the scenes. That, plus creating the prompts would really, really help me out. Thanks for anybody willing to help me with this. |
#7
|
|||
|
|||
Using Filter by Form to create a Query and then a Report
To show the report name in the header, just add a text box with this
ControlSource: =[Report].[Name] For dynamic sorting or reports, set the ControlSoruce of the GroupLevel in Report_Open. Details in: Sorting report records at runtime at: http://allenbrowne.com/ser-33.html -- Allen 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. "HWhite" wrote in message ... Thank you Allen. That code worked perfectly. So, that gets me through the part about filtering the data and then generating the report based on that filter, but I still need some assistance with prompting the user to provide up to 2 sorting criteria, and a prompt for the report name that will be placed in the report header. I tried a few things and they didn't work. |
Thread Tools | |
Display Modes | |
|
|