Suppress records based on VBA logic

  • Thread starter Thread starter Gaz
  • Start date Start date
G

Gaz

I want to create a report that compares values in 2 sequential records from a
table, then decides whether to print the pair of records (with a descriptive
message) or whether to print nothing for that pair of records. I have plenty
of IT experience, but little Access and no VBA experience.

From what I've read, I assume VBA can permit values from one record to be
saved for comparison with the next sequential record. Can someone guide me
on how to suppress printing records when the VBA logic determines that
nothing should be printed for the pair of records? ALso, is the sequence of
records for the report controlled by the input query, or does sorting have to
be specified in the report configuration somwhere? Thank-you...
 
Use a form to search for the records you want to show. Then create a
recordset, and assign the values to variables in that recordset, compare the
variables and if they satisfy your criteria, print the report.

If you can find the records with a simple query that can be used as the
recordsource for the report, you can suppress printing by using the No Data
property and/or event of the report.
 
Gaz said:
I want to create a report that compares values in 2 sequential records from a
table, then decides whether to print the pair of records (with a descriptive
message) or whether to print nothing for that pair of records. I have plenty
of IT experience, but little Access and no VBA experience.

From what I've read, I assume VBA can permit values from one record to be
saved for comparison with the next sequential record. Can someone guide me
on how to suppress printing records when the VBA logic determines that
nothing should be printed for the pair of records? ALso, is the sequence of
records for the report controlled by the input query, or does sorting have to
be specified in the report configuration somwhere?
Click to expand...

VBA code in a report is not a reliable way to compare two
records. That kind of thing needs to be done in the
report's record source query. Just figuring out which
record to compare against can be tricky and usually requires
a subquery. See http://allenbrowne.com/subquery-01.html for
a discussion of subqueries with an example of the kind of
thing you want to do.

OTOH, how you determine which two records to compare is
highly dependent on how the records are related. If they
are related in a very specific way, joining the table to
itself is almost always a more efficient approach.

Sorting the records in a report needs to be specified in the
report's Sorting and Grouping window (View menu).
 
Thanks Marsh and Arvin for the fast and enlightening responses. It sounds
like I need to read up on subqueries and self-joins ... I probably have a
better chance of success with those versus experimenting myself with VBA
anyways.
 
Back
Top