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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

"lkororos@xxxxxxxxx" 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


.

Relevant Pages

  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Dim oRpt As New CrystalReport1() failing
    ... I do not work with strongly typed reports - I create report files ... Dim paramFields As New ParameterFields ... > Dim paramFields As New ParameterFields ... > reference to "Dim oRpt as New CrystalReport(), ...
    (microsoft.public.vb.crystal)
  • RE: Custom Print, close, email ribbon
    ... This way, regardless of whether I open the report from code, or via the ... Dim cbr As Object 'As CommandBar ... Dim rpt As Report, strRptName As String ...
    (microsoft.public.access.reports)
  • pausing and resume
    ... hello i am using this code for generating word report, ... Dim appWord As Word.Application ... Dim rowCount As Long ... 'create a table to display the testscript names and their Outcome ...
    (microsoft.public.word.vba.general)
  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)