If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
add blank lines to report
I am looking for help mimicking a form that was previously created with
excel. i want to create a block in a report wich may possibly be a sub report that will stay the same size and add blank lines to any additional space below the data being displayed so that users can Hand write additional entries once the form is printed.. I would like the access populated rows to "Grow" and Wrap text to fit the reports width. but the following blank lines to be something like 20pt or 1/4" Any help would be greatly appreciated Thanks Barry |
#2
|
|||
|
|||
add blank lines to report
|
#4
|
|||
|
|||
add blank lines to report
Hi Barry,
You will need to determine how many records fit on the first page of the report and each of the succeeding pages. You will need to do this manually. This will be the number of records it takes to completely fill your report. Say it takes 25 on the first page and 29 on each succeeding page. The number of records it takes to completely fill your report can be determined with the formula: 25 + (Pages - 1) * 29 Next , Yes, use a DCount to determine how many records are in the recordsource of the report: DCount(...............) Finally, in the textbox's controlsource put the number of blank rows needed: Number from Formula - DCount Can Grow. Set it to Yes and you don't need to worry about it. The Detail section will grow to fill the last page down to the Report Footer. Steve "Barry A&P" wrote in message ... Steve Thanks for your reply.. i didnt think of a union query to add blank lines. Your response talks about code and a text box to count the number of rows to add from the second table.. do i just do a Dcount with the same criteria as your first WHERE clause? as the number for the second WHERE?? what do i do about "CAN GROW" on the first set of records returning a bad count for remaining rows to fill ?? Ill start researching for more details. thank you so much for getting me on track Barry "Steve" wrote: See Below .... Steve "Barry A&P" wrote in message ... I am looking for help mimicking a form that was previously created with excel. i want to create a block in a report wich may possibly be a sub report that will stay the same size and add blank lines to any additional space below the data being displayed so that users can Hand write additional entries once the form is printed.. I would like the access populated rows to "Grow" and Wrap text to fit the reports width. but the following blank lines to be something like 20pt or 1/4" Any help would be greatly appreciated Thanks Barry Creating Blank Lines In A Report Blank line items are needed in reports such as Purchase orders and Invoices to give the area below the last detail and the top of the Report Header the same appearance as the filled-in line items. This can be done by creating a Table2 containing whatever the number of blank rows it takes to fill the detail section of the report and an Union Query combining Table1 and Table2. The example below shows a simple report using this technique. Table1 AID A A1 A2 1 $1 11 Text1 2 $2 12 Text2 3 $3 13 Text3 4 $4 14 Text4 5 $5 15 Text5 Table2 AROW ROW B B1 B2 1 R1 2 R2 3 R3 4 R4 5 R5 Union Query SELECT Table1.Aid, Table1.A, Table1.A1, Table1.A2 FROM Table1 WHERE (((Table1.A1)"14")) UNION SELECT Table2.Row, Table2.B, Table2.B1, Table2.B2 FROM Table2 WHERE (((Table2.AROW)="3")); Report A A1 A2 $1 11 Text1 $2 12 Text2 $3 13 Text3 Note: The first Where clause in the Union Query determines which records from Table1 will be in the report. In a Purchase Order or an Invoice, the Where Clause would specify the POID or InvoiceID to determine which detail records would be in the report. The second Where clause in the Union Query determines which records from Table2 (or the number of blank line items) will be in the report. In a Purchase Order or an Invoice, the Where Clause is used to specify the number of blank line items. In the code, the number of detail line items would be counted and that number would be subtracted from the number of line items that would fit in the detail section of the report. The calculated difference would be placed in a textbox which becomes the criteria in the second Where clause. |
#5
|
|||
|
|||
add blank lines to report
Steve
I have read alot about the infamous "Steve" ?? I truely appreciate the help.. Barry "Steve" wrote: Hi Barry, You will need to determine how many records fit on the first page of the report and each of the succeeding pages. You will need to do this manually. This will be the number of records it takes to completely fill your report. Say it takes 25 on the first page and 29 on each succeeding page. The number of records it takes to completely fill your report can be determined with the formula: 25 + (Pages - 1) * 29 Next , Yes, use a DCount to determine how many records are in the recordsource of the report: DCount(...............) Finally, in the textbox's controlsource put the number of blank rows needed: Number from Formula - DCount Can Grow. Set it to Yes and you don't need to worry about it. The Detail section will grow to fill the last page down to the Report Footer. Steve "Barry A&P" wrote in message ... Steve Thanks for your reply.. i didnt think of a union query to add blank lines. Your response talks about code and a text box to count the number of rows to add from the second table.. do i just do a Dcount with the same criteria as your first WHERE clause? as the number for the second WHERE?? what do i do about "CAN GROW" on the first set of records returning a bad count for remaining rows to fill ?? Ill start researching for more details. thank you so much for getting me on track Barry "Steve" wrote: See Below .... Steve "Barry A&P" wrote in message ... I am looking for help mimicking a form that was previously created with excel. i want to create a block in a report wich may possibly be a sub report that will stay the same size and add blank lines to any additional space below the data being displayed so that users can Hand write additional entries once the form is printed.. I would like the access populated rows to "Grow" and Wrap text to fit the reports width. but the following blank lines to be something like 20pt or 1/4" Any help would be greatly appreciated Thanks Barry Creating Blank Lines In A Report Blank line items are needed in reports such as Purchase orders and Invoices to give the area below the last detail and the top of the Report Header the same appearance as the filled-in line items. This can be done by creating a Table2 containing whatever the number of blank rows it takes to fill the detail section of the report and an Union Query combining Table1 and Table2. The example below shows a simple report using this technique. Table1 AID A A1 A2 1 $1 11 Text1 2 $2 12 Text2 3 $3 13 Text3 4 $4 14 Text4 5 $5 15 Text5 Table2 AROW ROW B B1 B2 1 R1 2 R2 3 R3 4 R4 5 R5 Union Query SELECT Table1.Aid, Table1.A, Table1.A1, Table1.A2 FROM Table1 WHERE (((Table1.A1)"14")) UNION SELECT Table2.Row, Table2.B, Table2.B1, Table2.B2 FROM Table2 WHERE (((Table2.AROW)="3")); Report A A1 A2 $1 11 Text1 $2 12 Text2 $3 13 Text3 Note: The first Where clause in the Union Query determines which records from Table1 will be in the report. In a Purchase Order or an Invoice, the Where Clause would specify the POID or InvoiceID to determine which detail records would be in the report. The second Where clause in the Union Query determines which records from Table2 (or the number of blank line items) will be in the report. In a Purchase Order or an Invoice, the Where Clause is used to specify the number of blank line items. In the code, the number of detail line items would be counted and that number would be subtracted from the number of line items that would fit in the detail section of the report. The calculated difference would be placed in a textbox which becomes the criteria in the second Where clause. |
#6
|
|||
|
|||
add blank lines to report
Steve has provided some good suggestions from time to time. The trouble is
he strays from that approach, and instead solicits work in the newsgroups. I remember this topic from a thread a week or two ago. I didn't know a solution had been developed. I have not tried it, but I see the reasoning. It is something I may try applying for a sign-in sheet where some of the names are entered in advance and others on the fly. I would probably remove the quotes from around the numbers, as the text value "2" may be evaluated as greater than the number 14. Perhaps Access can sort that out, but even if so a number is better treated as number than as text. Also, if numbers are derived from the record count (you could use DCount, or you could try Me.Recordset.RecordCount) and you allow the text boxes to grow you may not get the correct result. I expect this would work best with fixed height text boxes and Detail section. It may be possible to use a report event procedure to test the height of each text box, add them together, and calculate the number of blank rows remaining, but even if it is possible it is a new level of complexity. In any case, it sounds as if you may want the detail section to be the same height each time. Barry A&P wrote: Steve I have read alot about the infamous "Steve" ?? I truely appreciate the help.. Barry Hi Barry, [quoted text clipped - 169 lines] becomes the criteria in the second Where clause. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...ports/200909/1 |
#7
|
|||
|
|||
add blank lines to report
Since the poster wants the detail sections to grow, I am not sure this
solution would work very well. If all the poster wants is to add blank lines across the last page until the bottom of the last page is reached, then that is possible. I have seen it done, but I would need to do some research in order to do this. I think Duane Hookom may have posted a solution to this in the past. If the poster wants labels to print plus the lines, this also could be done. It would be a bit trickier and again, I would need to research the solution since I don't have an example available. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Steve wrote: Hi Barry, You will need to determine how many records fit on the first page of the report and each of the succeeding pages. You will need to do this manually. This will be the number of records it takes to completely fill your report. Say it takes 25 on the first page and 29 on each succeeding page. The number of records it takes to completely fill your report can be determined with the formula: 25 + (Pages - 1) * 29 Next , Yes, use a DCount to determine how many records are in the recordsource of the report: DCount(...............) Finally, in the textbox's controlsource put the number of blank rows needed: Number from Formula - DCount Can Grow. Set it to Yes and you don't need to worry about it. The Detail section will grow to fill the last page down to the Report Footer. Steve "Barry A&P" wrote in message ... Steve Thanks for your reply.. i didnt think of a union query to add blank lines. Your response talks about code and a text box to count the number of rows to add from the second table.. do i just do a Dcount with the same criteria as your first WHERE clause? as the number for the second WHERE?? what do i do about "CAN GROW" on the first set of records returning a bad count for remaining rows to fill ?? Ill start researching for more details. thank you so much for getting me on track Barry "Steve" wrote: See Below .... Steve "Barry A&P" wrote in message ... I am looking for help mimicking a form that was previously created with excel. i want to create a block in a report wich may possibly be a sub report that will stay the same size and add blank lines to any additional space below the data being displayed so that users can Hand write additional entries once the form is printed.. I would like the access populated rows to "Grow" and Wrap text to fit the reports width. but the following blank lines to be something like 20pt or 1/4" Any help would be greatly appreciated Thanks Barry Creating Blank Lines In A Report Blank line items are needed in reports such as Purchase orders and Invoices to give the area below the last detail and the top of the Report Header the same appearance as the filled-in line items. This can be done by creating a Table2 containing whatever the number of blank rows it takes to fill the detail section of the report and an Union Query combining Table1 and Table2. The example below shows a simple report using this technique. Table1 AID A A1 A2 1 $1 11 Text1 2 $2 12 Text2 3 $3 13 Text3 4 $4 14 Text4 5 $5 15 Text5 Table2 AROW ROW B B1 B2 1 R1 2 R2 3 R3 4 R4 5 R5 Union Query SELECT Table1.Aid, Table1.A, Table1.A1, Table1.A2 FROM Table1 WHERE (((Table1.A1)"14")) UNION SELECT Table2.Row, Table2.B, Table2.B1, Table2.B2 FROM Table2 WHERE (((Table2.AROW)="3")); Report A A1 A2 $1 11 Text1 $2 12 Text2 $3 13 Text3 Note: The first Where clause in the Union Query determines which records from Table1 will be in the report. In a Purchase Order or an Invoice, the Where Clause would specify the POID or InvoiceID to determine which detail records would be in the report. The second Where clause in the Union Query determines which records from Table2 (or the number of blank line items) will be in the report. In a Purchase Order or an Invoice, the Where Clause is used to specify the number of blank line items. In the code, the number of detail line items would be counted and that number would be subtracted from the number of line items that would fit in the detail section of the report. The calculated difference would be placed in a textbox which becomes the criteria in the second Where clause. |
#8
|
|||
|
|||
add blank lines to report
Barry,
Here are two potential solutions... From Marsh MVP http://www.eggheadcafe.com/conversat...did=2952 6322 OR You might want to remove the borders from all controls and use code to drawthe boxes. The following code in the On Page event of the report might work for you. Private Sub Report_Page() Dim intNumLines As Integer Dim intLineNumber As Integer Dim intTopMargin As Integer Dim ctl As Control Dim intLineHeight As Integer intNumLines = 12 intTopMargin = Me.Section(3).Height intLineHeight = Me.Section(0).Height For Each ctl In Me.Section(0).Controls For intLineNumber = 0 To intNumLines - 1 Me.Line (ctl.Left, intTopMargin + _ (intLineNumber * intLineHeight)) _ -Step(ctl.Width, intLineHeight), , B Next Next End Sub -- Duane Hookom Microsoft Access MVP -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Barry A&P" wrote in message ... I am looking for help mimicking a form that was previously created with excel. i want to create a block in a report wich may possibly be a sub report that will stay the same size and add blank lines to any additional space below the data being displayed so that users can Hand write additional entries once the form is printed.. I would like the access populated rows to "Grow" and Wrap text to fit the reports width. but the following blank lines to be something like 20pt or 1/4" Any help would be greatly appreciated Thanks Barry |
Thread Tools | |
Display Modes | |
|
|