Auto-generating reports in Microsoft Access

0
votes

Currently I am in the process of building an updated Microsoft Access database for our affiliated hospital to keep a timeline of documentation for potential medical studies. The database itself is standalone, so there is no real back end to speak of (which hurts me, but I have no control over how they handle their data). It currently holds only one table with all the necessary fields needed. I'm looking to have reports generate automatically every week, month, quarter, and year. However, I must admit my vba coding abilities are spotty at best. My questions are this:

  1. What code would I need to use to generate said reports?

  2. Would I need to create separate queries just for each type of report based on when it needs to be generated, or can I code it all in one query without the different codes interfering with each other?

If any other information or clarification is needed, I'd be happy to give you what I can.

3
vbams-access
Have you an understanding of access reports creation, not using VBA? - Nathan_Sav
How are the report versions different? Are they are the same information but from different time periods? - HansUp
@Nathan_Sav - Do you mean just setting up the report, the data and the layout? If so, then yes I do. If not, I'll need more of an explanation. - JThomassie
@HansUp - Exactly. We may also need to generate other reports as they tell me exactly what other kinds of reports they may need, but I figure if I know how to do it for the one, I can do it for the others later on in the build. - JThomassie
Look into the Access help topic for the DoCmd.OpenReport Method. Assuming your report's Record Source includes a date field, you can specify a date range with the OpenReport WhereCondition option to filter the report data by date. That way you could use a single report for week, month, quarter, and year --- just ask for the desired date range when you open it. - HansUp

3 Answers

1
votes

When calling a Report from VBA or from a macro, two things could be usefull to you:

  1. providing a condition, like
    docmd.OpenReport "someReport",acViewPreview,,"[examDate] > Date() - 30"
  2. providing an OpenArg, which can be used by vba in your report to change the periodicity for example ("M" for Monthly and "w" for Weekly"), like
    docmd.OpenReport "someReport",acViewPreview,,"[examDate] > Date() - 30",,"W"

Those tricks should allow you to design a report ONCE, then use it with dynamic time range and grouping.

0
votes

If you don't want to use VBA, for creating automatic reports printing/export you'll need:

  1. Create reports which don't require user entry, use date functions for querying required date intervals.
  2. Create macroses for starting reports and closing the database, no VBA knowledge required
  3. Create tasks in Windows Task Scheduler for starting MS Access at requred days/time. Command line should contain parameters for opening specified database and starting specified macro (/x macro_name). Parameters description you can find, for instance, here

.

0
votes

The comments say it all.

Access is designed to make report writing of the nature you describe very easy, and a lot can be achieved without using VBA at all or macros for that matter.

If you want to use the same report to display the same sets of data for different date ranges (or perhaps with other changes in criteria), you should prompt the user to enter a date range or the other criteria that will cause the data displayed to be changed. (ie the parameters they entered are used in the query to change the data returned).

This is infinitely preferable to having many many reports and queries that are all the same except they get data for a different set of criteria.

There are many Access Programming books that will help you re-learn these skills. I have found this channel useful https://www.youtube.com/user/ProgrammingMadeEZ/search?query=reports for other Access videos, but it's not as useful for reports.

Harvey