Thanks John, I will give that a try.....
-----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Monday, January 23, 2006 11:30 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Mike- Let's start over. You don't need to change the Record Source. Create an unbound form that has unbound controls where the user can enter optional criteria. In the Click event of the command button to open the report, examine what criteria the user has entered and build a filter string. Use that string in the WhereCondition parameter of OpenReport to fetch the results the user wants. You can find several examples of a custom "filter by form" in the sample databases on my website. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bisson Sent: Monday, January 23, 2006 7:33 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Hi John, Sorry for the confusion, it's really just "criteria". My report is always grouped and sorted by Machine. I may have one report that only looks at dates as a criteria, and one that looks at dates and entered by as criteria. I have all of the criteria info in the header of the report, so everyone knows what the criteria was for that particular version of the report. Is this making sense to you? I'm even confusing myself at this point! LOL -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Monday, January 23, 2006 7:57 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Mike- Maybe not possible. The sorting sequence of most reports is determined by the Sorting and Grouping specification in the report design. If you want one report by date and another by entered by, those probably must be separate reports. If your report design has no Sorting and Grouping specification, then you might be able to influence the report sequence using the Order By in the Record Source. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bisson Sent: Monday, January 23, 2006 6:55 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Hi John, I've assembled a form which contains multiple command buttons for launching the reports. I only listed one variation because that's all I have for now, but there will be others in the near future. The purpose is to use one report format with multiple queries. For instance: Imight have a button that lists all work orders, one that lists work orders by date, one that lists workorders by date and entered by, etc.. These would all use the same report, but the queries are distinctly different. I already have a few combo boxes on the "Report" form for start-date, end date, machine, equipment and status that "feed" the underlying query. I'm contemplating just adding another combo box for the "Entered By". That might be the most straightforward way to do this. Opinion?? -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Monday, January 23, 2006 7:00 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Mike- You said you have 10 possible queries, but you list just one. What are the other queries, and how will the user select which "report type" to print? Let's say you have a form with an Option group that allows the user to pick the report type. The form also has a command button to run the report. First, you need a public variable. In a Standard module (one you can see in the list in the Database window), put this in the Declarations section: Public gstrReportQuery As String In the Click event of the command button, do somethin like: Select Case Me.optReportType ' Option 1 Case 1 gstrReportQuery = "qry_WOsummaryByDate_EnteredBy" DoCmd.OpenReport "rptWOSummary", acViewPreview ' Option 2 Case 2 gstrReportQuery = " <name of query for 2nd option> " DoCmd.OpenReport "rptWOSummary", acViewPreview ' .. and other cases as needed End Select In the Open event of the report: Me.RecordSource = gstrReportQuery John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bisson Sent: Monday, January 23, 2006 3:05 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Thanks John, Can you provide a little help on the code? My query name will be: qry_WOsummaryByDate_EnteredBy Thanks :) -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of John Viescas Sent: Monday, January 23, 2006 2:46 PM To: [email protected] Subject: RE: [ms_access] Changing a reports record source Mike- You can change the RecordSource property in the Open event of the report. Perhaps set a public variable with the SQL or name of the query you want to use and have the code in the Open event pick it up from that. John Viescas, author "Building Microsoft Access Applications" "Microsoft Office Access 2003 Inside Out" "Running Microsoft Access 2000" "SQL Queries for Mere Mortals" http://www.viescas.com/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Mike Bisson Sent: Monday, January 23, 2006 12:35 PM To: [email protected] Subject: [ms_access] Changing a reports record source Hi All, I have a basic report template that I want to use. How can I modify each version of the report to use a different record source (query)? I don't want to have to recreate the same report 10 times. Thanks :) ================================== Mike Bisson Manager of IT & Logistics Carthuplas, Inc. V: 207-985-4972 x658 F: 207-985-2105 [EMAIL PROTECTED] http://www.carthuplas.com Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/ms_access/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
