vba in access report: how to edit records before the report is displayed

[.]

i have an msaccess report based on a query, it works correctly.

i need to examine the records returned by the query BEFORE they are used in
the report, and have no idea how to go about doing this.

I am assuming there is a report-event that would allow me to have the proper
timing, and i need an idea of how to point to the query records and be able
to examine them and make changes and delete unwanted records.

the deletes and changes would not effect the underlying table, only the
query dataset.

thanks a bunch.

mike g

 

[Steve Schapel]

Mike,

I suggest you do whatever data manipulations you need, before running
the report. So the Event you would use would not be an event of the report.

Also your idea of editing the data in a query without affecting the
table the query is based on, is also a problem.

Can you post back with some specific details, perhaps with some data
examples, of what you are trying to achieve? I think that will help
somebody offer useful advice.

 

[.]

to be accomplished:
1) run a query to select only records for a particular job
2) in this group of records i want to examine several fields within each
record for a variety of text and unique co-dependant criteria and remove
those records from the temporary dataset (without effecting the original
source table, but only effecting the dataset returned by the query)
3) run the report on the final version of the edited data returned by the
query

 

[John W. Vinson]

to be accomplished:
1) run a query to select only records for a particular job
2) in this group of records i want to examine several fields within each
record for a variety of text and unique co-dependant criteria and remove
those records from the temporary dataset (without effecting the original
source table, but only effecting the dataset returned by the query)
3) run the report on the final version of the edited data returned by the
query

What Steve is saying is quite correct: you CANNOT edit records in a query
without affecting the underlying table. You'll need to run an append or
make-table query to copy the desired records into a second table; edit those
records (using a form); and then base the report on this second table.

 

[.]

OK, understood.
so, where do i go to create the vba code based on this new table before the
report starts using the records?
and how do i reference the various records in the table?
i have some coding experience, i just dont have a clue where to place the
coding for this inside the msaccess reports environment.

 

[Steve Schapel]

Mike,

If I understand you correctly, the records you want to exclude can not
be excluded based on criteria applied to the original query. Right?

So this is how I would approach it...

1. As John suggested, run an Append Query to write the data returned by
the original query into a temporary table. This step comes *before* you
do anything with the report.

2. Open a form based on this temporary table.

3. On this form, do the data deletions/changes you want.

4. Set up the report so that this temporary table (or a query based on
it) is the Record Source of the report.

5. On the form opened in step 2, have a command button, on whose Click
event you have your OpenReport method to print the report.

 

[Larry Linson]

to be accomplished:
1) run a query to select only records for a particular job
2) in this group of records i want to examine several fields within each
record for a variety of text and unique co-dependant criteria and remove
those records from the temporary dataset (without effecting the original
source table, but only effecting the dataset returned by the query)
3) run the report on the final version of the edited data returned by the
query

You have not described anything that _necessarily_ cannot be done in the
criteria of the query to select the records... WHERE conditions and joins
can accomplish a great deal... you may, or may not, require VBA code. I
think my colleagues are assuming that you have already determined that the
criteria are so complex that you can't do the selection in the Query, but I
haven't seen you describe anything that would lead me to believe it is
necessarily so.

If you would describe the details, then perhaps someone could help you.
Many times, and it has been the case with me in the past, people fall back
on VBA simply because they don't realize the Access facilities that would
let them avoid it.

Larry Linson
Microsoft Office Access MVP

 

[.]

more specifically:
if a record in the table has a description that is similar to another record
and on of them has the word 'rev' in it, and if they have opposing signs
with the same dollar amounts, and if one record has one of several special
codes and the other record has special code that is dependant on the code in
the irst record, and they were both created within a number of days of each
other, then both records are to be deleted.

i was hoping to do this work in the beginning of the report at some event
before the report actually read the table, but i dont know how to refer to
the table records.

also, the report is grouped, and i want to NOT display detail and total
records for groups that total to $0

 

[.]

thanks for the direction Steve.

Mike,

If I understand you correctly, the records you want to exclude can not be
excluded based on criteria applied to the original query. Right?

So this is how I would approach it...

1. As John suggested, run an Append Query to write the data returned by
the original query into a temporary table. This step comes *before* you
do anything with the report.

2. Open a form based on this temporary table.

3. On this form, do the data deletions/changes you want.

4. Set up the report so that this temporary table (or a query based on
it) is the Record Source of the report.

5. On the form opened in step 2, have a command button, on whose Click
event you have your OpenReport method to print the report.

 

[Larry Linson]

more specifically:
if a record in the table has a description that is similar to another
record and on of them has the word 'rev' in it, and if they have opposing
signs with the same dollar amounts, and if one record has one of several
special codes and the other record has special code that is dependant on
the code in the irst record, and they were both created within a number of
days of each other, then both records are to be deleted.

As a start, try a Query with the records sorted on the description field. If
the descriptions were identical and the REV was in a separate field, you
could use a self-join (join the table to itself), and use that as a starting
point. If you combine the data in a Query by joining so that all the data
for the description is together, you may be able to do what you want in the
Query, because in a Query, calculations can only refer to other fields in
the same result Record (though the fields might be in separate Records prior
to your joining them). "Similar" may not be good enough for _any_ type of
processing unless that means "identical but for the REV notation" and even
in the second case, you still couldn't use them for joining.

I think I'd best leave further discussion to the other responders, because
even determining whether it could be done in a Query will be difficult in
the "back and forth" of newsgroup communication.

Larry Linson
Microsoft Office Access MVP