Force Subreport to Repeat X Times

  • Thread starter Thread starter Ernie
  • Start date Start date
E

Ernie

With your subreport based on a query, you can use SELECT
TOP 4 <fieldlist> WHERE <criteria> GROUP BY <grouping>
ORDER BY <sort order>

and get just the first 4 items returned by the query. I'm
not certain that you will get blanks when there are less
than 4 items returned, but this will limit you to a max of
4.

Note: <fieldlist> is whatever fields you want on your
subreport
<criteria> is how you want that information selected
<grouping> is how you want the selected records to be
grouped
-----Original Message-----
I've designed an Access report that, when printed, will
Click to expand...
appear to be a regular paper form with some answers
already filled in. It's based on a set of linked tables
generated by third-party software. One of the table
relationships is one-to-many, so while the form doesn't
change, I have a subreport that fills in that section of
data as required. I originally encapsulated that entire
section of the form in a subreport, but the subreport only
repeats once for every child record found. I need to set
the number of times the subreport repeats to a fixed
number - say, 4 (this number won't change once
implemented, I just haven't decided what it ought to be
yet). If there are only three child records, I want the
fourth iteration to appear like the others, but with bound
fields blank.
If at all possible, I'd like to do this without having to
Click to expand...
do any coding. The rest of the report hasn't required any,
and I'd like to keep this as simple as I can (especially
since this project is on a pretty short deadline.
 
I forgot the "FROM" keyword in the previous post ... it
should be SELECT TOP 4 ... FROM ... WHERE ...(etc)

and that goes in your "record source" for the subreport
 
TOP 4, huh? I'm not entirely sure, but I don't think that's a standard SQL clause (my installation of MySQL didn't like it). Neat, the things Access provides.

I tried adding it after the DISTINCT keyword in my query and got mixed results. It certainly does limit the number of records returned when I run the query. Also, when I run the subreport it returns the top four records. However, when I run the main report I get no records returned. After a little thought, I realized that the query underlying the subreport pulls all records, and I rely on the Link Fields settings to screen mismatches out (a bad idea anyhow). So the top 4 records would fail the Link test and return nothing. I would need a way to sort the relevant records to the top. Alternatively, I think I could use some VB to do a FindFirst with the current LINK field as the parameter. I'm not very comfortable with that as my experience with VB amounts to about 12 hours over the past two days.

Unfortunately, it does not pad to 4 matches with blank records. Thanks, though. I do like the idea of a SQL fix, since it's probably faster than Accessing processing something else.

Thanks very much for the suggestion though. Any further help you're willing to provide will be greatly appreciated.
 
Back
Top