sequence of events in ACCESS

Question

what is the proper way of doing the following:

1. getting DATE as user input
2. running a query
3. generating a report that uses the query

this is the solution i was thinking:

1. have a form that takes user input
2. run the query
3. open the report

what is the correct way of doing this>?

Answer 1

I'd create a button on the form in which you build the query and open the report in preview mode (assuming they do not want to just automatically send it to the default printer) in the Click event procedure for the button.

Answer 2

A query can reference a form filed as an input parameter, which can be used as the query for the results to the report.

• So have a form with a date user input.
• Place a button that would open the report.
• the report should make use of a query/embedded sql, that uses the field from the form as input.

Typically the report, when run without the form open, would request the value of the "form field".

So in general, you would create a Reports Form, that you can luanch reports from, which has the required fields for the reports.

Answer 3

I dissent from this approach, as I don't like tying reports to particular forms.

Instead, I use a dialog form (as here) that is opened in the OnOpen event of the report, and writes the Recordsource of the report.

If you want the report to be runnable without popping up the dialog, you can make it conditional on OpenArgs, or, say, if the Filter property is already set (which is what happens if you use DoCmd.OpenReport with a WHERE argument).

I like to make reports and dialogs as independent as possible, and often I'll use a standalone class module as a data storage structure, and check it in the OnOpen event. If the public variable for the relevant instance of the class module is Nothing, then just run the report, otherwise, pull the data from the properties of the class module instance and write the Recordsource.

In this way, you can have the dialog form and the report completely independent. Both need know nothing about each other, but both will be used with the class module (though the form doesn't need to know anything about the class module instance).

For more detail, just ask.

This is a complicated subject, and I've spent years working on it to make apps as maintainable as possible. Decoupling UI objects from each other is one of the things that makes for better re-usability, and, hence, better maintainability and extensibility.

(of course, you don't need to use class modules -- you could use user-defined types, or arrays or whatever, but I like the capability to have multiple instances of the same structure, which is the whole point of a class module)