Show detail rows in report with or without data using VBA?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that has a similar look to an existing paper form. The paper
form has ten numbered rows of empty line items that are later filled in with
data as needed. My Access version of this report looks identical to the paper
form except that the item rows (detail section) only appear if there is data
(as expected).

Is there a way to create the report so that the ten empty rows appear but get
populated with data as needed? So that the report always shows ten rows,
either empty or with data?

I think there is a way withe VBA. If the report has 3 detail records, I
think this is the vba approach I would use (excuse my psuedo code) : -)

In the "on print" event procedure of the detail section of the report:

If the record count in the detail section is less than 10
for each field in the detail row
get the property values for the outline of each field
end

get the detail record count
subtract detail record count from 10

while count is less than the computed count
for each field in the row
draw each field outline
end
end

end

Is this the right approach or is there an easier way to do this?
Anyone have code to share?

Thanks in advance
 
If you want 10 lines on each page, consider code like the following:
Private Sub Report_Page()
Dim intRptHeadHeight As Integer
Dim intPageHeadHeight As Integer
Dim intTopMarg As Integer
Dim intDetailHeight As Integer
Dim intRecNum As Integer
intRptHeadHeight = Me.ReportHeader.Height
intPageHeadHeight = Me.Section(3).Height
intTopMarg = intRptHeadHeight + intPageHeadHeight
intDetailHeight = Me.Section(0).Height
For intRecNum = 1 To 10
Me.CurrentX = 200
Me.CurrentY = intTopMarg + _
((intRecNum - 1) * intDetailHeight)
Me.Print intRecNum
Me.Line (0, intTopMarg + _
(intRecNum * intDetailHeight)) _
-Step(Me.Width, 0)
Next
End Sub
 
Thanks
Your is a simpler aproach.

My report has a header, detail and footer sections.
The header and footer always have data. The number of rows containing data
in the detail section may vary from 1 to 10, but I want 10 rows to always be
printed.

What i want to accomplish is have the header printed, then the detail rows
followed by a number of empty (filler) rows to complete the detail rows to
10, then the footer section.

How do I configure this code so that it will effect only the detail section?

thanks
 
"How do I configure this code so that it will effect only the detail
section?" You tell me want doesn't work with the code that I provided. Also
let us know what you might have tried to adjust the code.
 
I added the code to the "on print" event procedure in the detail section.
I added a new group footer and added a text box control "ItemCount" with the
control source set to =Count("*")

I then changed one line in the code
from:
intRecNum = 1 To 10
to:
intRecNum = Me.ItemCount

This works correcty as displayed in the debugger. There is 1 item in the
detail section so I that row of data should be followed by an empty row. But
all I see is the data row and no empty row when I do a print preview.

What I'm trying to accomplish is have the report formatted this way:

Header
Detail
Empy rows if needed
Footer

Thanks
 
How would I load them using VBA? How would I make the visble?
Do you have an example?

thanks
 
Errr, have you considered putting 10 rows of unbound text boxes or
labels in the detail section and making them invisible? then if there is
data for them load it via VBA and make those that have data visible.

That is one method I'd try.
 
The first line of the code I suggested was
Private Sub Report_Page()
This suggests the code is in the Page event of the report, not the On Print
of the detail section.
 
Tec92407 said:
I have a report that has a similar look to an existing paper form. The paper
form has ten numbered rows of empty line items that are later filled in with
data as needed. My Access version of this report looks identical to the paper
form except that the item rows (detail section) only appear if there is data
(as expected).
.......
Is this the right approach or is there an easier way to do this?
Anyone have code to share?

Thanks in advance
Click to expand...

Hi Tec,

I have had exactly the same problem in the past and couldn't get a
solution. After trying several things, I managed to get the solution.
You first need to have a hidden field in the report with the Count(ids)
value. This will give you the total number of records returned by your
query. You will also need another hidden field which will have default
value 1 and will be a running sum (you set that from the "Data" tab of
the textbox's properties. The boxes that you need for the fields you
should have them in the Detail section of the box. Here is the psuedo
code for the OPrint event of the Detail section of the report:

First calculate the number of blank rows that you need:

Globals Initialised in the Open Event of the Report:
-------------------------------------------------------------------------
noOfBlankRowsNeeded = 10 - (Count Mod 10)
blankRowsInserted_global = 0

Psuedo Code of the OnPrint of the Detail:
------------------------------------------------------------
If runningSum < Count exit the event handler
Else
Loop over every control in the Detail and make it Hidden
blankRowsInserted++
If blankRowsInserted < noOfBlankRowsNeeded Then Me.NextRec =
False
Else Me.NextRec = True
End If
End If

That should do it. The key here is the NextRec method that will prevent
the framework from moving to the next record and therefore will
duplicate the last record of your query. However because you have made
all the controls hidden you will only see the drawn boxes of the detail
section.

I hope this helps,

Lefteris
 
Thanks

I'm going to give it a try. But I have a couple of questions.
See below.

[email protected] said:
Hi Tec,

I have had exactly the same problem in the past and couldn't get a
solution. After trying several things, I managed to get the solution.
You first need to have a hidden field in the report with the Count(ids)
value. This will give you the total number of records returned by your
query. You will also need another hidden field which will have default
value 1 and will be a running sum (you set that from the "Data" tab of
Click to expand...

So I'll need to put these two textbox fields in the footer of the detail
section, right?
the textbox's properties. The boxes that you need for the fields you
should have them in the Detail section of the box. Here is the psuedo
code for the OPrint event of the Detail section of the report:
Click to expand...

I'm a little confused here. I currently have a single row of textbox fields
in the detail section that are auto-populated when I run the report. Do you
mean replace these with 10 rows of text boxes? I'm not sure what you mean
here.
 
Ok, let me explain exactly what I meant.
So I'll need to put these two textbox fields in the footer of the detail
section, right?
Click to expand...

No you need to put these two hidden text boxes IN the detail section
and not in the footer. The running sum will not work in the footer.
I'm a little confused here. I currently have a single row of textbox fields
in the detail section that are auto-populated when I run the report. Do you
mean replace these with 10 rows of text boxes? I'm not sure what you mean
here.
Click to expand...

You need to have 1 text box in the detail section (plus the two hidden
boxes mentioned above). You also need to draw a box around this text
box using the line tool in the design view of the report (this will
still show as a box when you make the text box later hidden).

Then you display all the records returned from your query in the normat
way. However in the OnPrint method you need to check if the running sum
equals the counter (i.e. you are printing the last record from your
query). If you are and it is not the 10th record (in which case you
don't need to do anything special), you set the NextRec property to
FALSE. This will cause access, to repeat the last record. However, this
time you set the Visible property to false and you only see the drawn
box giving the impression that that another text box is there.

When you have printed 10 records in total (say 4 from your query and 6
times the last record repeated), you set the NextRec to TRUE and access
will try to grab the next record from the query. There won't be one and
therefore it will move on to print the Report Footer.

Make sure that the border in the text boxes is set to Transparent so
that you don't get double borders in the records returned from your
query (the text box's border and the drawn rectangle).

I hope this helps and sorry for taking so long to reply.

Lefteris
 
hi

I've tried to follow your description but am still having problems.

I had two data items rows in the database when debugging. A Item row is made
up of about 16 textbox fields placed side by side in the data section. I
turned off the border on each and drew a single long rectangle around them
and then drew verticle lines to separate the fields.

1. I can't get the txtTotalItems textbox to work corrrectly. Its located in
the detail section and if I use Count([ItemID]) in the control source,it
returns the total number of records in the table but not the record subset.
In otherwords, the Items table actually has 4 records, two each related to
two different parent records...so the value returned is 4...I need to get it
to return a value related to the current record set which is 2. I hardcoded
this so I could test.

I've placed the VBA code below in the On Print event of the detail section.
Can you check my code and advise?

Thanks in advance.

Here's the code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim DetailRowsMax As Integer ' max number of detail rows in detail section
Dim TotalItems As Integer ' Total data rows in detail section
Dim RunningSum As Integer ' Holder of running sum text box value
'Dim BlankRowsNeeded As Integer in "On open" event of report
'Dim BlankRowsAdded As Integer in "On open" event of report
Dim NoOfControls As Integer ' total number of textbox controls in detail
Dim Cnt As Integer ' count value

TotalItems = Me.txtTotalItems ' textbox in detail section
TotalItems = 2 ' hard coded value for debugging purposes
RunningSum = Me.txtRSum ' textbox in detail section
DetailRowsMax = 10
BlankRowsNeeded = DetailRowsMax - TotalItems
BlankRowsAdded = 0

If RunningSum <= TotalItems Then
GoTo Quit
Else
NoOfControls = Me.Section(acDetail).Controls.Count
If NoOfControls > 0 Then
For Cnt = 0 To NoOfControls - 1
Me.Controls(Cnt).Visible = False
Next Cnt
Else
Debug.Print vbTab; "(no controls)"
End If
BlankRowsAdded = BlankRowsAdded + 1
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
End If
End If

Quit:

End Sub
 
Hi,

I've had a look at your code and I have a couple of suggestions to
make. However, I am not sure that they will solve the problem since you
are not saying how it doesn't work (when you hard code TotalItems).

One comment is in the loop where you set the controls to invisible, I
have it as below:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Properties("ControlType") = acTextBox Then
ctl.Properties("Visible") = False
End If
Next ctl

I remember I had to put the check for the ControlType for some reason.
I also think that you need to change the setting of the Visible
property using the Properties collection as above.

The rest of your code seems ok to me and the logic is good. I also
assume that BlankRowsAdded and BlankRowsNeeded are global variables.

Now as to why the TotalItems gives you the wrong value I am not sure. I
have used it the same way and it worked fine. One difference is that in
my query I had two fields with the same name, so the one that I needed
to use in the count function was preceded by the table name (i.e.
Table.[my unique field]).

I suggest you try to get it to work first with the hardcoded value and
then try to sort out the Count problem.

By the way, I assume that the record source for your report is set to a
Query and not a Table.

Let me know how it goes,

Lefteris
 
Hi Lefteris!

First...let me say thanks for sticking with me on this. I really appreciate
your help.
:-)

I've rewritten my code after your comments and some further debugging.
The new code is shown below and works as intended...it prints data rows
followed by the required number of empty rows... but I'm now working out how
to fix a multi-page print problem. My original report was setup to print two
copies, an "originator" and "purchaser" copy before I set out to change the
report format to print the empty rows. That part stopped working correctly
but I think it has to do with page breaks.

I've answered some of your questions in the dialog text below the new code.

Here's the new code:

Global Declarations
=====================================
Public BlankRowsNeeded As Integer
Public DetailRowsMax As Integer
Public DataRows As Integer
Public TotalDetailRows As Integer
Public LastDataRow As Boolean
Public BlankRowsAdded As Integer

in Report "on open" event proc
=====================================

BlankRowsNeeded = 0
BlankRowsAdded = 0
DetailRowsMax = 10

in Detail "on print" event proc
=====================================
Dim RunningSum As Integer ' running sum text box value
Dim ctl As Control ' control object

DataRows = Me.txtTotalItems ' textbox in detail section
RunningSum = Me.txtRSum ' textbox in detail section
BlankRowsNeeded = DetailRowsMax - DataRows

If RunningSum = DataRows Then
If LastDataRow Then
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = False
End Select
End With
Next ctl
BlankRowsAdded = BlankRowsAdded + 1
Else
LastDataRow = True
End If
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
End If
End If
TotalDetailRows = TotalDetailRows + 1 'counter for debugging

[email protected] said:
Hi,

I've had a look at your code and I have a couple of suggestions to
make. However, I am not sure that they will solve the problem since you
are not saying how it doesn't work (when you hard code TotalItems).

One comment is in the loop where you set the controls to invisible, I
have it as below:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Properties("ControlType") = acTextBox Then
ctl.Properties("Visible") = False
End If
Next ctl

I remember I had to put the check for the ControlType for some reason.
I also think that you need to change the setting of the Visible
property using the Properties collection as above.
Click to expand...

Yup. I discovered that too. Unless your specific about which controls to
make hidden it will hide the lines as well (line objects are controls)
The rest of your code seems ok to me and the logic is good. I also
assume that BlankRowsAdded and BlankRowsNeeded are global variables.
Correct


Now as to why the TotalItems gives you the wrong value I am not sure. I
have used it the same way and it worked fine. One difference is that in
my query I had two fields with the same name, so the one that I needed
to use in the count function was preceded by the table name (i.e.
Table.[my unique field]).
Click to expand...

I changed the expression specified in the control source of the TotalItems
txtbox:
=DCount("[PRID]","PRItems","PRID = CurrentRecord")

It now works correctly.
Sheesh....
 
Hi Tec,

no problem for the helping. After all, I remember trying to get some
help with the exact issue and couldn't get any, so I know what it is
like ;)

Now as far as the problem that you are having now (possibly with the
page breaks) not sure why this is. However, I would like to make you
aware that I had some problems with printing the report, as it seemed
to misbehave when I "print previewed" the report before printing. In
particular, when I opened a report for print preview, then moved to the
second page and then tried to print it, I was getting blank records
(sometimes, not always). I guess that Access is a bit strange (and
possibly buggy?) with the way that some events are fired (this is more
the case for the Format event). The way around it, was to modify the
behaviour of the print button in the menu that I had, to first close
the report and then open it again in "Print Mode", i.e. print it. This
worked fine.

I don't know if this is your problem at the moment, but I am pointing
it out in case you run into it later on.

Good luck,

Lefteris

Ο/Η Tec92407 έγÏαψε:
Hi Lefteris!

First...let me say thanks for sticking with me on this. I really appreciate
your help.
:-)

I've rewritten my code after your comments and some further debugging.
The new code is shown below and works as intended...it prints data rows
followed by the required number of empty rows... but I'm now working out how
to fix a multi-page print problem. My original report was setup to print two
copies, an "originator" and "purchaser" copy before I set out to change the
report format to print the empty rows. That part stopped working correctly
but I think it has to do with page breaks.

I've answered some of your questions in the dialog text below the new code.

Here's the new code:

Global Declarations
=====================================
Public BlankRowsNeeded As Integer
Public DetailRowsMax As Integer
Public DataRows As Integer
Public TotalDetailRows As Integer
Public LastDataRow As Boolean
Public BlankRowsAdded As Integer

in Report "on open" event proc
=====================================

BlankRowsNeeded = 0
BlankRowsAdded = 0
DetailRowsMax = 10

in Detail "on print" event proc
=====================================
Dim RunningSum As Integer ' running sum text box value
Dim ctl As Control ' control object

DataRows = Me.txtTotalItems ' textbox in detail section
RunningSum = Me.txtRSum ' textbox in detail section
BlankRowsNeeded = DetailRowsMax - DataRows

If RunningSum = DataRows Then
If LastDataRow Then
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = False
End Select
End With
Next ctl
BlankRowsAdded = BlankRowsAdded + 1
Else
LastDataRow = True
End If
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
End If
End If
TotalDetailRows = TotalDetailRows + 1 'counter for debugging

[email protected] said:
Hi,

I've had a look at your code and I have a couple of suggestions to
make. However, I am not sure that they will solve the problem since you
are not saying how it doesn't work (when you hard code TotalItems).

One comment is in the loop where you set the controls to invisible, I
have it as below:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Properties("ControlType") = acTextBox Then
ctl.Properties("Visible") = False
End If
Next ctl

I remember I had to put the check for the ControlType for some reason.
I also think that you need to change the setting of the Visible
property using the Properties collection as above.
Click to expand...

Yup. I discovered that too. Unless your specific about which controls to
make hidden it will hide the lines as well (line objects are controls)
The rest of your code seems ok to me and the logic is good. I also
assume that BlankRowsAdded and BlankRowsNeeded are global variables.
Correct


Now as to why the TotalItems gives you the wrong value I am not sure. I
have used it the same way and it worked fine. One difference is that in
my query I had two fields with the same name, so the one that I needed
to use in the count function was preceded by the table name (i.e.
Table.[my unique field]).
Click to expand...

I changed the expression specified in the control source of the TotalItems
txtbox:
=DCount("[PRID]","PRItems","PRID = CurrentRecord")

It now works correctly.
Sheesh....
I suggest you try to get it to work first with the hardcoded value and
then try to sort out the Count problem.

By the way, I assume that the record source for your report is set to a
Query and not a Table.
Correct


Let me know how it goes,

Lefteris
Click to expand...
Click to expand...
 
Hi..

I found out what was wrong. Basically, the "on print" event will get
executed for each record in the underlying query and again for each page, so
the code has to be smart enough to detect this. In short, I have to make sure
this code is run an formats the page for every page I print. So I've modified
the code again to make sure I don't set the last record "hidden" and to reset
all variables when another page needs to be printed.
I did this using a new global boolean "LastDataRow" that detects when the
last data row has been processed. I also modified the txtRSum text box
running sum property to "over group" instead of over all so it would reset
when the next page begins to be processed.

This code works and I only have one issue left. When I print each page, that
last row contains data from the last record when it should be empty. I must
be leaving a row visible or something. Any suggestions are welcome..

Modified code below:

If RunningSum = DataRows Then
If LastDataRow Then
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = False
End Select
End With
Next ctl
BlankRowsAdded = BlankRowsAdded + 1
Else
LastDataRow = True
End If
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
BlankRowsAdded = 0
LastDataRow = False
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = True
End Select
End With
Next ctl
End If
End If
TotalDetailRows = TotalDetailRows + 1 'counter for debugging

[email protected] said:
Hi Tec,

no problem for the helping. After all, I remember trying to get some
help with the exact issue and couldn't get any, so I know what it is
like ;)

Now as far as the problem that you are having now (possibly with the
page breaks) not sure why this is. However, I would like to make you
aware that I had some problems with printing the report, as it seemed
to misbehave when I "print previewed" the report before printing. In
particular, when I opened a report for print preview, then moved to the
second page and then tried to print it, I was getting blank records
(sometimes, not always). I guess that Access is a bit strange (and
possibly buggy?) with the way that some events are fired (this is more
the case for the Format event). The way around it, was to modify the
behaviour of the print button in the menu that I had, to first close
the report and then open it again in "Print Mode", i.e. print it. This
worked fine.
Click to expand...

See my comments above. Also, I found when working between the VBA
environment and report design environment I sometimes would mistakely move a
textbox or modify a section in a report in a way the would make the report
span more than one page. This would cause the sequence of execution to change
when debugging in VBA. Maannn....was that ever confusing....:-)

Oh well....live and learn. ;-)
I don't know if this is your problem at the moment, but I am pointing
it out in case you run into it later on.

Good luck,

Lefteris

Ο/Η Tec92407 έγÏαψε:
Hi Lefteris!

First...let me say thanks for sticking with me on this. I really appreciate
your help.
:-)

I've rewritten my code after your comments and some further debugging.
The new code is shown below and works as intended...it prints data rows
followed by the required number of empty rows... but I'm now working out how
to fix a multi-page print problem. My original report was setup to print two
copies, an "originator" and "purchaser" copy before I set out to change the
report format to print the empty rows. That part stopped working correctly
but I think it has to do with page breaks.

I've answered some of your questions in the dialog text below the new code.

Here's the new code:

Global Declarations
=====================================
Public BlankRowsNeeded As Integer
Public DetailRowsMax As Integer
Public DataRows As Integer
Public TotalDetailRows As Integer
Public LastDataRow As Boolean
Public BlankRowsAdded As Integer

in Report "on open" event proc
=====================================

BlankRowsNeeded = 0
BlankRowsAdded = 0
DetailRowsMax = 10

in Detail "on print" event proc
=====================================
Dim RunningSum As Integer ' running sum text box value
Dim ctl As Control ' control object

DataRows = Me.txtTotalItems ' textbox in detail section
RunningSum = Me.txtRSum ' textbox in detail section
BlankRowsNeeded = DetailRowsMax - DataRows

If RunningSum = DataRows Then
If LastDataRow Then
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = False
End Select
End With
Next ctl
BlankRowsAdded = BlankRowsAdded + 1
Else
LastDataRow = True
End If
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
End If
End If
TotalDetailRows = TotalDetailRows + 1 'counter for debugging

[email protected] said:
Hi,

I've had a look at your code and I have a couple of suggestions to
make. However, I am not sure that they will solve the problem since you
are not saying how it doesn't work (when you hard code TotalItems).

One comment is in the loop where you set the controls to invisible, I
have it as below:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Properties("ControlType") = acTextBox Then
ctl.Properties("Visible") = False
End If
Next ctl

I remember I had to put the check for the ControlType for some reason.
I also think that you need to change the setting of the Visible
property using the Properties collection as above.
Click to expand...

Yup. I discovered that too. Unless your specific about which controls to
make hidden it will hide the lines as well (line objects are controls)
The rest of your code seems ok to me and the logic is good. I also
assume that BlankRowsAdded and BlankRowsNeeded are global variables.
Correct


Now as to why the TotalItems gives you the wrong value I am not sure. I
have used it the same way and it worked fine. One difference is that in
my query I had two fields with the same name, so the one that I needed
to use in the count function was preceded by the table name (i.e.
Table.[my unique field]).
Click to expand...

I changed the expression specified in the control source of the TotalItems
txtbox:
=DCount("[PRID]","PRItems","PRID = CurrentRecord")

It now works correctly.
Sheesh....
I suggest you try to get it to work first with the hardcoded value and
then try to sort out the Count problem.

By the way, I assume that the record source for your report is set to a
Query and not a Table.
Correct


Let me know how it goes,

Lefteris
Click to expand...
Click to expand...
Click to expand...
 
Hi

I've worked out the final bug and the report is working correctly. I can now
print multiple copies correctly.

Thanks for all your help

Here's the final version.
========================================
Dim RunningSum As Integer ' running sum text box value
Dim ctl As Control ' control object

DataRows = Me.txtTotalItems ' textbox in detail section
RunningSum = Me.txtRSum ' textbox in detail section
BlankRowsNeeded = DetailRowsMax - DataRows

If RunningSum = DataRows Then
If LastDataRow Then
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = False
End Select
End With
Next ctl
BlankRowsAdded = BlankRowsAdded + 1
Else
LastDataRow = True
End If
If BlankRowsAdded < BlankRowsNeeded Then
Me.NextRecord = False
Else
Me.NextRecord = True
BlankRowsAdded = 0
LastDataRow = False
End If
Else
For Each ctl In Me.Section(acDetail).Controls
With ctl
Select Case .ControlType
Case acTextBox
.Visible = True
End Select
End With
Next ctl
End If
 
Back
Top