Creating reports in Access after the DB is completed

Question

Hi,

I have a fully functional DB developed in Access. It was done using a very "interactive" developer-client increment-iterative process as no one really knows what the final DB is to show. All tables were developed (and normalized) as well as forms (and sub-forms), however, now reports must be designed and implemented.

The forms of course, are based on queries which take the criteria from the form itself to run the queries and show information in the subforms. Now, as in practice, the reports have to basically be pretty printable versions of the on-screen forms for distribution. From my research, the reports are also based on queries, but since the queries I have are using the fields from the forms to display the relevant information in subforms, I don't see anyway to create these reports unless I basically duplicate all my queries to not pull values from forms. This seems very tedious and inefficient. Is this the "best practice" way to create reports for a database?

V.K.

Answer 1

Hard-coding form fields in a query is the problematic part here -- that's definitely not "best practice". I guess you need these values in your WHERE clause? What you could do is to remove the field references from your queries (so that the query returns all records). In the Subform or Report, don't use the query directly as the record source. Instead, use some SQL that accesses your (generalized) query and and additional filter criteria. For example, the record source of your subform could be

SELECT * FROM myQuery WHERE someQueryField = Me.Parent!SomeFormField

allowing you to use the same myQuery with a different WHERE clause (which does not reference a Form) in your report.

Answer 2

You can put a button on the form which launches the report using the same query. The report query will then use the fiels/query from the form, and should then return the same result set.

Answer 3

I'm not sure I'm understanding your question, but having recently worked with an app that used a form to drive a report, I might have an inkling.

You don't want your report tied to the single record of the form. You want your report to use the same recordsource as the form, insofar as they are displaying the same data, of course.

The reason for this is that if you hardwire your report to the form fields, the report can't be used except when the form is open, and it can't display anything but one record.

If you create your report so it loads all records, then you'll need to be able to print one record at a time, and for that you just use the WHERE argument of the DoCmd.OpenReport command, using the PK value as your criterion.

So, yes, you should use the same recordsource. Why would anyone consider that a problem?