How to make a report with fixed length "Detail" section? 2

[cheapo]

Hi,

I am having trouble making a report.
This particular report must be 1 page in length.
The format is the usual header-detail-footer.

I need to make a fixed position for header and footer at the top and bottom of the page respectivelly.
The details section occupies the middle of the page.. around 15cm.. the report can only contain a maximum of 15 entries in the details section.
(i.e. 1 cm for every entry)

When it has 15 entries it occupies all the 15cm of space in the details and when it's lesser(e.g. only 3 entries, it should only occupies the first 3 cm and leave the other 12cm of space blank... and the footer will still be at the bottom of the page.

Is it this possible to make such report?
Is there any special tricks?

Thanks In Advance.

 

[THWatson]

If you right click on the Detail section, and then select Properties, and make the following settings...

Section Height "15 cm."
Can Grow "No"
Force New Page "after section"

... will that do what you want?

Tom

 

[cheapo]

Hi Tom,

I did what you suggested, but it did not work.
I tried it with 5 detail entries and the first page contains the header and the first entry only.
The second to the fifth entries are in their own individual page in page 2-5 and page 6 contains the footer.

I have also tryed all other choices like after, before, before and after section for the force new page, but it all did not work.

Any other ideas?

Cheers,
Cheapo.

 

[THWatson]

Without knowing more about your database, it's hard to picture your specific problem. But here's a question: What is the "sorting and grouping" you are using?

You mentioned in the original post that sometimes you would want 15 entries on a page and sometimes only 3. So there must be some criteria by which data is grouped. (e.g. record #, last name)

Check your sorting and grouping in the report and see if this fixes the problem.

Otherwise, give me a little more information, and I'll have a further think about it. Or send me a piece of your database to twatson@sentex.net. However I will only be around for the next 3 hours today and then will be gone the rest of the day.

Tom

 

[cheapo]

Hi Tom,

The report is an invoice, but somehow that report is following a fixed format like I mentioned earlier.

The header contains the customer details and invoice number, date.
It comes from the table Sales(SalesID, CutomerID, Date)

The detail section of the report contains the things ordered: name, quantity and price.
(max of 15)
It comes from the table OrderSales(ProdId, Qty, Price).

The footer contains the othetr goodies like total, gst, discounts, grand total, signatures and name.

I managed to get what I want by putting the header in page header, footer in the page footer and details in details.

But due to the design of my invoice, I need a box to be drawn around the whole thing..... but in my case, the line connecting the "end-of-details" and the "beginning-of-footer" is not present.

I'm more confused now?

I was thinking of making a dummy entry(i.e. blank entryes if the entry in the details is no less than 15 in order to fill up the empty spaces.
You think it's possible?

Cheers,
Cheapo!

 

[THWatson]

I have an invoice in one database, and it sounds similar to yours.

If I understand correctly, your Detail section will be a subreport. Correct?

You probably can put in dummy lines if entries are fewer than 15 by using VBA. I am wondering about a couple of other possibilities. I haven't tested these, so they are off the top of my head.

1. Draw a box around the borders of your Detail section and then set it to Can't Shrink.
2. Draw horizontal lines at the top and bottom of your Detail section. These shouldn't change. Then draw vertical lines down the size, so that they line up at the edges of the horizontal lines, and set these vertical lines to Can't Shrink.

But I am also wondering if you can work with your Orders subreport, and make a box around it and set that box to Can't Shrink.

I'll do a little playing around with a dummy database. If I come up with anything concrete I'll post back.

Design is fun, eh?

By the way, since you used the word GST I am assuming you are somewhere in Canada. So am I.
Tom

 

[cheapo]

>"If I understand correctly, your Detail section will be a >subreport. Correct? "

Correct!


>"You probably can put in dummy lines if entries are fewer >than 15 by using VBA."

What kind of code should I be writing? I dont seem to have any idea on how to go about doing it.


>"By the way, since you used the word GST I am assuming you >are somewhere in Canada. So am I."

It's acctually called PPN in my country, not GST. I'm from Indonesia.

Anyway I have tried to make a dummy counter and tryed out several "magic work" in the Control source... with no luck!
Ill keep on trying.... at least for a couple more days.


Thanks,
Cheapo.

 

[THWatson]

I just opened my Invoice report and then went to the report design, selected the subreport, went to its properties, put a solid border around the subreport, and it stayed the same size regardless of the number of entries when I set the subreport to Can't Shrink.

Does that work for you?

Tom

 

[cheapo]

mmmmhhhhhhh

doesn't seem to work for me.
IF it's not too much trouble, can you send a blank database with just the report to my email address?

TIA,
Cheapo.

 

[THWatson]

It will have to wait until later on tonight. I have to go for now, but I will take another look late tonight, and send something to you.

Later...

Tom

 

[cheapo]

Looking forward to it.

Thanks Tom!

 

[THWatson]

I just got back a few minutes ago. It's 1:30 a.m. and I'm a bit too tired to take much of a run at things tonight. I can do so in the morning if you still want me to. However, I did try something that just might work for you.

Draw a rectangle around the entire Detail section, just inside its borders. On the Format menu choose "Send to back" so that it doesn't cover up any controls. It will always print in the size that you make it.

Does that work for your purpose?

I'll check back in the morning.

Tom

 

[THWatson]

Hey, here's something else to try...and it might be exactly what you are looking for...

Right click on the Detail section of your report, and then select its Properties. In the On Format event put the following code...
Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B

So your entire code would look like this
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B
End Sub

What this does is draw a line equal to the Height and Width of the section. The "B" at the very end of the code line indicates Border.

Let me know if that does it.

Tom

 

[cheapo]

Tom, you'r the champ!!!!
It all worked now!!!

I followed your suggestion on drawing the rectangle around the subform and make the subform to be exactly 15 line.
It all work out just the way I wanted... after spending the whole day setting up the page properly!!! It's a real pain!

Design is fun indeed!
lol!!!

However, I do have another problem now.... it never ends!
My reports ask for 2 prompts(date(not always today) and attn(random)) and print out the two in the appropriate place in the report.

I made a print command(button) for it to print twice, but
it asks the 2 prompts twice(since I print it twice).
Is there a way to store those variables temporaryly so that I can print twice without inputting twice???

I am a newbie at VBA coding.... can you give me a direction?

I have this in mind...

- edit the on_click event of the print button.
- declare 2 variable( x and y) to store the 2 values which I prompted the user when he/she open the report.
- declare 2 variable for reports(r1, r2).
- print the two reports by sending the two variables to the open report command as the input variables.
(print(r1,x,y) and print(r2,x,y)).
- Walah print twice!!!!

lol.... speak is easy huh?

Cheers,
Cheapo.

 

[THWatson]

I'll have a look at the possibilities and post back.

Glad you have the first problem solved.

Tom

 

[THWatson]

I should have asked a question: Do you always, always, always, print 2 copies of the report?

Tom

 

[cheapo]

Acctually here's what happens.

I have 2 different reports named "one" and "two". They are a copy of each other, the only difference is the text in the header, which are just fixed labels(some thing like Page 1(Accountant) and Page 2(Customer).
.... and I am supposed to folow the format exactly as given.

What I want is that so that I only need to input the 2 paramters once and both "one" and "two" to capture the values.

... and yes the reports are always printed in a pair.
very annoying huh?

 

[THWatson]

If you always, always print 2 copies of the report, then follow these instructions...

Open the report in Design view
In the On Activate event, put the following code
DoCmd.PrintOut acPrintAll,,,,2

That will give you 2 copies of the report after you answer your parameter prompts.

If you don't always print 2 copies, only sometimes, then we have to find another approach, which will probably require printing from a form by which you select the number of copies.

If you want some documentation on the PrintOut method, go into VBA and do a search for PrintOut method.

Tom

 

[cheapo]

Ok... Ill do some more reading tomorow...
I better get some rest now... havnt have much rest lately.

Thanks for your help Tom.
I really appreciate it.

Cheers,
Cheapo.

 

[THWatson]

Okay, I got it! At least, if I understand your report I got it.

My understanding of your report is (1)you always do 2 copies, and (2) the two reports are the same except that one copy says "Accountant Copy" and the other says "Customer Copy". If that's correct, here's what to do...

Step 1
In the On Activate event for the report, put the code I mentioned earlier. That is...
DoCmd.PrintOut acPrintAll, , , , 2

Step 2
Put an unbound control in your page header. (in my example it was called Text120)

Step 3
Right click on the Page Header, and then select the On Format event. In the On Format event, put the following code...
If Me.PrintCount = 1 Then
Me!Text120 = "Accountant Copy"
Else
Me!Text120 = "Customer Copy"
End If

This will put "Accountant Copy" on one of the report pages, and "Customer Copy" on the other.

Hope that helps.
Tom