Need help with Active record report

  • Thread starter Thread starter Les Coover
  • Start date Start date
L

Les Coover

Suppose I have a form based on 3 tables tblAGENCY
1 to many > tblDIVISION 1 to many > tblAGENT.

I would like to put print preview and print report command
buttons on the report that will display/print ONLY the currently
active AGENCY displayed on the form along with all
the associated DIVISIONS and AGENTS.

How do I do this? Could you point me to some help
guides?

Appreciate the help.

Les
 
The WhereCondition argument of the DoCmd.OpenReport statement will allow you
to supply a "WHERE clause without a WHERE" that will be applied to the
Report's RecordSource. Assuming you have a unique identifier available for
the AGENCY in the form, the VBA to construct that WhereCondition argument
will be simple... something like this (air code):

Dim strWhCond as String
. . .
strWhCond = "[AgencyID] = " & Me!txtAgencyID

if the unique ID is a numeric field; or

strWhCond = "[AgencyID] = """ & Me!txtAgencyID & """"

if it is a text field.

Then,

DoCmd.OpenReport "rptYourReport",,,strWhCond

Alternatively, it would be more efficient (useful if you have a large
database, tens or hundreds of thousands of records) to build the entire SQL
statement for the RecordSource of the Report... that could be stored in the
SQL property of a query used as RecordSource, or could be picked up in the
Report's Open event and used to reset the Record Source. For modest-sized
databases, the additional efficiency won't be discernable, and it is a bit
more work.

Larry Linson
Microsoft Access MVP
 
Thank you Larry

Les

Larry Linson said:
The WhereCondition argument of the DoCmd.OpenReport statement will allow you
to supply a "WHERE clause without a WHERE" that will be applied to the
Report's RecordSource. Assuming you have a unique identifier available for
the AGENCY in the form, the VBA to construct that WhereCondition argument
will be simple... something like this (air code):

Dim strWhCond as String
. . .
strWhCond = "[AgencyID] = " & Me!txtAgencyID

if the unique ID is a numeric field; or

strWhCond = "[AgencyID] = """ & Me!txtAgencyID & """"

if it is a text field.

Then,

DoCmd.OpenReport "rptYourReport",,,strWhCond

Alternatively, it would be more efficient (useful if you have a large
database, tens or hundreds of thousands of records) to build the entire SQL
statement for the RecordSource of the Report... that could be stored in the
SQL property of a query used as RecordSource, or could be picked up in the
Report's Open event and used to reset the Record Source. For modest-sized
databases, the additional efficiency won't be discernable, and it is a bit
more work.

Larry Linson
Microsoft Access MVP


Les Coover said:
Suppose I have a form based on 3 tables tblAGENCY
1 to many > tblDIVISION 1 to many > tblAGENT.

I would like to put print preview and print report command
buttons on the report that will display/print ONLY the currently
active AGENCY displayed on the form along with all
the associated DIVISIONS and AGENTS.

How do I do this? Could you point me to some help
guides?

Appreciate the help.

Les
Click to expand...
Click to expand...
 
Back
Top