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

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?

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.

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

" wrote:

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?

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.

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 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

" wrote:

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.

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]).


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

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

" wrote:

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.

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]).


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

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

" wrote:

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.

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

" wrote:

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.

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]).


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

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

Hi Tec,

I'm glad that you managed to get it to work correctly.