CODEWITHSUNDEEP
vbams-access
JThomassie
JThomassie

Reputation: 1

Auto-generating reports in Microsoft Access

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.

Upvotes: 0

Views: 6268

Answers (3)

iDevlop
iDevlop

Reputation: 25252

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.

Upvotes: 1

HarveyFrench
HarveyFrench

Reputation: 4568

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

Upvotes: 0

Sergey S.
Sergey S.

Reputation: 6336

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

.

Upvotes: 0

Related Questions