Creating an instant report - Repost

  • Thread starter Thread starter Michael Clifford
  • Start date Start date
M

Michael Clifford

I need to design an application that allows users to select an existing
field by pushing a button on a form. The routine will calculate the percent
null. That's all working well.

However, the end users now want an exception report that will give details
about records that are null. This is launched by pressing a corresponding
button.

One of the goals is to limit the number of persistent objects (reports,
queries). A temporary CreateQueryDef is being used.

The problem: How can a report be created from the querydef? This would
be similar to an Instant Report in other database packages, which can take a
grid of data and generate a serviceable report.

I've spent hours in the Internet and the consensus seems to be to
use Excel and transfer the data. Surely there's got to be a way to create an
instant report from a querydef and stay within Access 2000. Please advise.
Thanks in advance. This is a repost.

Regards,

MJC
 
I question your goal to "limit the number of persistent objects". Don't you
already have a serviceable report that would work? How much formatting would
you want or need?
 
Duane,

I'm not apparently clear. If there's ten fields to be measured for the %
filled in on the form, that would normally engender ten separate exception
reports, each of which would provide detail on that field and other fields
in each of the corresponding record and table. I'd like to simply present a
record set than print it, rather than a proliferation of rarely used report
objects in the Database window.

Let's approach this another way: How do a code up from a temporary querydef
the production of a temporary table and the printing of a temporary "report"
of the data? Any help is appreciated.

Regards,

Michael
 
I have faced this issue many times. I wrote a Query By Form applet that I
integrate into almost every application I create (costs a client ~$500).
This QBF can potentially meet 75% of the data reporting needs in an
application. It might take less than an hour for a developer to add this
applet to their mdb.

Roger Carlson posted the solution at his web site earlier this morning.
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='DH Query By Form'

This solution is free with only a few conditions regarding its use.

Let me know how this works for you.
 
Duane,

The application that I've designed has a number of lines of buttons and
unbound text boxes. Each line deals with a particular field in a table in a
medical database. A button click on a line computes and displays the percent
null of that field for all records, using a temporary querydef. That's
working fine.

The end users now want another button to be added on the right for each
line. When clicked, a grid of data would be presented of the records for
which the corresponding field is null. They could then locate the record and
correct the problem.

My design problem: I don't want to design permanent record objects (one for
each of these buttons) and therefore have them cluttering up the Database
Window. All that's needed is to print a grid of data from a coded querydef -
and I can't find the commands to do so after hours of looking.

Please advise. Any help is appreciated.

Regards,

Michael
 
I'm sorry -- I don't understand why you'd need a temporary table. Can't you
just use the created SQL as the RecordSource of your Report? You can store
your SQL string in the .SQL property of an existing saved QueryDef, if you'd
prefer that to picking up the SQL* in the Report's Open event and replacing
the RecordSource.

* can be saved in a public variable in a standard
module, in a control (visible or invisible) on an
open form, in a property of an object that you
have defined in a class module, or, if it is short
enough, passed to an Access 2002/2003
report via the OpenArgs argument of the
DoCmd.OpenReport statement.

But, if I have missed something vital, you can certainly create SQL for a
MakeTable query. Just create one close to what you need with the Query
Builder, and use it as a model. You can execute that SQL with VBA DAO code.

Larry Linson
Microsoft Access MVP
 
Thanks Duane,

I'll give this a try. Also of note was the fact that I wasn't missing some
easy InstantReport type command.

Michael Clifford
 
Back
Top