Runtime report preparation

[Guest]

I need to produce a "Letter of Authorization" out of my database on request.
The letter contains a number of fields from half a dozen related tables with
a single driver table row.

I've roughed it out as an unbound report. I'm looking for advice on the best
way to have this letter "pop" out from a form which is there to select the
driving row.

Do I create a query, bind the report to it, and alter the report's filter
property at run time?

Do I instantiate the report, then fill in the fields one-by-one from the
form? What event would I choose to fire the population routine?

Is it better as a form? Should it be bound at design time, then "filtered"
at run time?

I will be firing this letter from multiple forms, but the data still comes
from the same query.

 

[Guest]

Jim,

My approach would be to include a Command button on your form that opens the
report with the OpenReport method. Set the RecordSource of the report to the
name of a query that selects all records in the database.

When the button is pressed, Access "knows" which record is the current. You
could use criteria in the Primary Key field of your query such as

=Forms![YourForm]![YourPKTextbox],

but since you will launch this from several different forms, simply use the
optional WHERE parameter of the OpenReport method:

Dim strDocName as String
Dim strLinkCriteria as String

strDocName = "YourReport"
strLinkCriteria = "[YourPrimaryKeyField] = " & Me![YourPKTextbox]
DoCmd.OpenReport strDocName, acViewNormal, , strLinkCriteria

The above code assumes your primary key field is numerical. If it is a
string field, use the following in lieu of the line above:

strLinkCriteria = "[YourPrimaryKeyField] = " & "'" & Me![YourPKTextbox]
& "'"

Hope that helps.
Sprinks

 

[Guest]

Since you plan to do this from multiple forms, I would suggest you create a
Public Function in a standard module so it is visible to all the forms. Put
the logic in the function so you only have to write it once and it is always
consistent. Then call the function from a command button click event on the
form when you want to create the letter. You will need to pass the
information the function will need to select the driving row as arguments.

As to the report, itself. The proper way to provide data to a report is
through either a table or a query. Reports, like forms, have a record source
and the controls on a report have control sources which are bound to fields
in the record source and present the data on the report. Your function,
then, should determine the environment for the report and open it. See the
OpenReport method in VBA fHelp or specifics on how to do that.

So, in short, you need to create a query that joins the various tables you
need to provide data for the report. You need the function to open the
report.

An approach you may need to consider is rather than a function on its own,
you may want to create a form that opens the report. The reason you may need
this is if there are parameters you need to pass to the query or values the
report needs to reference, you can populate controls on the form that the
query and report can reference. The the command button on the other forms
can open this form rather than calling the function. If you need to pass
data from the calling forms to the reporting form, use the OpenArgs argument.