Report showing the same record multiple times

[MMSMSD]

Hi,
I have a report based on a query that has worked fine. I give it a date range and it will show one line per record that satisfies that range. The problem is that they now want the report to exactly match a paper version that is not computer-friendly. I was able to get the form to LOOK correct but I can't figure out how to set up the center section, which normally just automatically creates the necessary rows of data, to place the data into the manual GRID I've created with lines. If I repeat the fields, I get a single page with the same record in every row.
My question is: How do I tell a field(s) to grab the NEXT record of the query results? I've done this once before but cannot remember at all now. Any help is appreciated. Thanks.

 

[genomon]

If you are using text boxes to display the data, go to the properties page and set "Hide Duplicates" = yes. Might work? Otherwise add a "DISTINCT" clause to the SELECT staement in your recordsource query?

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[MMSMSD]

I tried both options and neither worked. I got it to the point where is shows duplicates of only one distinct record per PAGE but I need all the records on the same page. SELECT DISTINCT is what I had used before but it just seems to ignore that command. To be more specific, the QUERY shows distinct records but the REPORT shows only one record per page.

Under Crystal Reports, you could SELECT DISTINCT within the report and it would work. Is there a way to do that within Access? Thanks.

 

[genomon]

Probably you enlarged the detail section and drew your lines there with the mouse? If so, don't. The (entire) detail section fires once for each record. If you need to draw custom lines on the report that the detail section prints on top of, add code using the Line method to the report page event (line method explained in help). Here is an example of how one of my guys did it (he has learned better coding style since this was written.....):

Private Sub Report_Page()
    
    lBackColor = 0
    Me.ScaleMode = 5                'ScaleMode 5 = inches
    
    Me.Line (0.05, 3.8)-(10.25, 3.8), lBackColor
    Me.Line (0.05, 4.6)-(10.25, 4.6), lBackColor
    Me.Line (0.05, 5.4)-(10.25, 5.4), lBackColor
    Me.Line (0.05, 6.2)-(10.25, 6.2), lBackColor
    
End Sub

This just draws a few simple horizontal lines - research the Line method and you can draw vertical lines, slanted lines, and boxes (both filled and empty). There is also a Circle method if you need to get fancy.....
Good Luck!


I have great faith in fools; self-confidence my friends call it.
-Poe

 

[dhookom]

It sounds like you need a subreport to display multiple child records on a main report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MMSMSD]

dhookom,
This section is already a subreport and was working fine until I needed to hard-code the 'grid' that appears in the paper version of the form.

genomon,
I WAS doing what you anticipated, enlarging the subreport section, which accounted for the single record per page. I put it back the original way and now everything looks the way it should except that there are no blank boxes for the extra rows in the paper form. I submitted it that way and we'll see if they balk at the lack of extra boxes lines.
Assuming they do, I think the line idea is the answer. Since all the fields are snapped to the grid, it shouldn't be too hard to align the lines to box in the fields as they are generated, once I figure out how it works. Thanks.

Michael

 

[dhookom]

You can't use the Page event of the subreport but could draw in most other events. If the boxes are always in the same place on the main page then place your code in the On Page of the main report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[doctorswamp]

Have a report based on a query in which one field is a checkbox. The report fields are in the page header, with no grouping.

With no criteria the report shows all records.

As soon as I ask for records with the checkbox ticked the report shows just one record even though the query correctly shows all the ones ticked.

Bit mystifed because this shold be so easy.

 

[dhookom]

I'm not sure why this message was posted in this thread and assume it was a simple mistake.

Why would you put all fields in the page header? What are you attempting to accomplish? Why don't you have controls in your detail section?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]