Report problem

[Soladmin]

I’m writing a report with the following fields Dept Name Idnum. These are in Text boxes and coming from a query that contains the correct info (ie. The list of emp names per dept). The report should print 5 rows per page… but if there’s less than 5 names the page should print with blank boxes where the dept name and id would’ve printed. It should look something like this

DeptA JOHN 123 XXX XXX XXX
XXX XXX XXX XXX XXX XXX
XXX XXX XXX XXX XXX XXX
XXX XXX XXX XXX XXX XXX
XXX XXX XXX XXX XXX XXX
New page


Where each XXX represents a blank box

If there are enough details of course then dept name and id should continue to print on
each line to fill the page. Every dept should start on a new page.

I have the report formatted already with the text boxes and labels in place in the detail section of my rep … Sorting/Grouping is on the dept field and Group Header = Yes I have a text box setup to get the number of recs per group with the Running Sum = Over group. At this point I’m at a lost…please help

 

[RickSpr]

I'm not sure what you're getting at with the second to last statement, but it seems to me all you need to do is set the Force New Page property to Before Section for the Dept group header section. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

thanks for replying ...
I currently have the Force New Page property to Before Section for the Dept group header section...and it prints an extra blank page after each page reagarless of whether or not a new group is starting...
Any other suggestions ?... I'm trying all sorts of combinations and so far nothing works.

 

[RickSpr]

An extra blank page? That's odd. Could it be that your report's Detail section is slightly longer than the page, so that its empty bottom gets printed on the next page?

First make sure the Detail section isn't too long (after allowing for the group header, plus any page header you've got defined). Then check that the CanShrink property is set to Yes for the Detail and all the controls in it. The CanShrink properties shouldn't really matter, but it couldn't hurt. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

thanks for your suggestions..I fixed the width of the report and it worked...I have the rep landscape and my fields reached up to 10.5 and with the margin at .25 on both sides it printed the extra blank page...I had to close the grid and change the margin to .166 and it worked...

Any idea on how to get the detail to print blank if there isn't enough data to fill the page?... i.e. if there's only one employee in the dept I'd like to print the one detail followed by 4 for rows with blank details (i.e. just blank squares...)...This is part of the user requirements...

 

[RickSpr]

That's what the ForceNextPage property on the group header is supposed to do. Have you tried that and it doesn't work right?

It's possible to use the OnFormat event property to skip separate detail sections on one page, but that's meant for when you're printing Details in a grid across and down, such as when printing a page of mailing labels. If you have to, you could use it to skip Details until you get to the next page, but it's kind of tricky to get right. I hope the ForceNextPage property will work for you instead. I don't see why it wouldn't. Let me know. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

The ForceNewPage was already set to before Section ...it wasn't the height of the report ...I fixed the problem because the width of the report was too big...

Can you help with the OnFormat ... I currently have vba code to change the text to blank ...I get a runtime error... not allowed at print time... if I use the visible property then the entire control will not print... it's just not what I want..I need the boxes to print with blank data in them(ie ME!NEWDEPT.text = "&quot...I just don't know where to put it.
Any suggestions?

 

[RickSpr]

Oops! Your approach is wrong. In the Format event, you don't blank out the data to leave a blank space in the report. If you could do that, you'd print blanks instead of the current record.

What you do is, you set the report's NextRecord and PrintSection properties to False when you want to leave a blank detail. That keeps Access from advancing to the next record, and from printing anything in the current detail section. Access sets these properties to True before calling the Format event, so you don't have to worry about getting them set back to True in your code.

Check the help file for these properties. There's a little chart that will help you figure out how to use them.

I still don't see why you should need to use the Format event, though. But if this is the way you need to go, at least it should work. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

I don’t need to replace the current record with a blank …
what I need to do is :

1. print the current record ( ie DEPT,NAME,ID and 3
labels that have their caption field empty ) so on
the screen those labels will look like blank
squares.
2. If there aren’t enough details for that group
(eg. 1 person in that dep1) then I should print
blank squares on the next row following the record
that just printed. The next row isn’t supposed to
be completely blank It’s supposed to have blank
square (or labels with nothing in the caption
field)

I tried to set the NextRecord and Printsection to False …it does’t print the current section at all and leaves a blank detail section which is not what I’m trying to do…

Any suggestions?

Thanks

 

[RickSpr]

I see I misunderstood what you wanted from the start. Your illustration with all the XXXs confused me, and this is the first time you've said anything about labels with empty captions being involved. It looked like a layout for 6-across and 5-down mailing labels, and when you said "blank boxes" I took it to mean unprinted labels.

I'm still not sure I know where everything is supposed to go. Would you mind giving me a similar illustration of what it would look like if you had, say, 3 employee names in the group? And tell me whether this illustration represents a single Detail section, or five of them.

In case this idea helps, you might consider placing both a text box and a ""-captioned label in each position, and then in the Format property make the appropriate one visible and the other invisible. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

I'm sorry for the confusion... I wish I could've pasted a copy of the screen ..it would be so much easier to see.. in any case here is an example ..

DeptA Johnson, Karen 123 YYY YYY YYY
XXX Smith,Thomas 456 YYY YYY YYY
XXX Williams,Victor 789 YYY YYY YYY
XXX XXX XXX YYY YYY YYY
XXX XXX XXX YYY YYY YYY

Keep in mind that I put the XXX to show that the text boxes (where the department and name fields belong) and the next 2 rows of the detail section do print. It's just that they're text boxes with the control source having to print no data for these rows. To differentiate the labels ... this time I added the labels as YYY instead. Therefore the last 3 columns on the right are actually labels with no captions... and the XXX under the dept names are blank text boxes because the department name is not supposed to be repeated for each row in the group but a "box" should be there...I already tried to use the visible property =false there and the entire field is not displayed (which is not right)...

Hope that's a little clearer now.
Once again ..thanks for your help

 

[RickSpr]

I think I understand much better now. There's just one thing I'm not sure about: For those controls in which text will actually print, should a rectangle be printed around them?

If the answer is Yes, there is a fairly easy way to do this. Create a picture file that contains boxes in a 6x5 array. Set the report's Picture property to this file, and set its Picture Alighment property to Top Left. To keep the duplicate departments from printing, set that text box's Hide Duplicates property to Yes.

It'll be a little tricky to get the boxes positioned just right. Some facts that will help:
- If you have any kind of page header, you need to leave blank space for it at the top of the picture.
- The boxes need to be one pixel outside where the controls will print, to avoid touching the text.

If you don't want boxes around the text, it's going to be much trickier. I'm not completely sure it's possible with just property settings and VBA code behind the report.

One possibility would be to use code to create a temporary table containing exactly five records for each department. You'd have to fill in this table before opening the report, and use it as the report's Record Source property. You'd still need a Format event procedure to make the borders visible or invisible depending on whether the data is Null, and to suppress the duplicate department names.

I might be able to give you some more advice, once you decide which approach you want to take. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Soladmin]

Yes...the controls that have text in them should have the rectangle printed around them. I'm going to look into creating the picture file..