Main report not allowing code from subreport

[RockKandee]

I am using Access 2013 on Windows 8

I have a subreport with the following code in the details on print event.

Public totalCount As Integer
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) '
    Dim students As Integer, maxR As Integer
    students = Me.Txcountrecords
    maxR = Me.txMaxStudent
    printBlankRecords Me, students, maxR
End Sub
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
    Me.txFull_Name.ForeColor = vbBlack
    Me.txeMail.ForeColor = vbBlack
    Me.txPhone.ForeColor = vbBlack
    totalCount = 0
End Sub
Public Sub printBlankRecords(rpt As Access.Report, usedRecords As Integer, MaxRecords As Integer)
    totalCount = totalCount + 1
    If totalCount = usedRecords Then
        rpt.NextRecord = False
    'once you get to the last record, stay on last record
    ElseIf totalCount > usedRecords And totalCount < MaxRecords Then
        rpt.NextRecord = False
        'MsgBox usedRecords & " " & MaxRecords & " " & totalCount
        'make the font and backcolor the same appearing to be empty record
        Me.txFull_Name.ForeColor = Me.txFull_Name.BackColor
        Me.txeMail.ForeColor = Me.txeMail.BackColor
        Me.txPhone.ForeColor = Me.txPhone.BackColor
    End If
End Sub

This code makes the report print blank lines. The number of blanks is determined by max students - number of records.

This is working when the sub report is open by its self. It doesn't work from the main report.

Any ideas how to get this to work from the main report?

Thanks for looking

[ADezii]

1. When the Report is being used as a Sub-Report, is it referencing the Txcountrecords and txMaxStudent Controls on the Main Form?
2. What do you mean exactly when you state
   

   It doesn't work from the main report.

3. Are no Blank Lines being generated when it exists as a Sub-Report?
4. Is the Sub-Report Linked to the Main Report or independent from it?

[RockKandee]

1. Txcountrecords is a txbox that counts records (=count(*)) in the group of the subreport and txMaxStudents is a field of the data source of the subreport. No, neither control is on the main report. Both are in the group header of the subreport.

2. When on the Main report - If Txcountrecord = 1 then 1 blank shows. If Txcountrecord = >1 then no blanks show. In both cases, the number of blanks is incorrect.

3. When the print preview of the sub report is open independently it shows the proper number of blanks which varies depending on Txcountrecord and txMaxStudent.

4. The only link is the parent child relationship between the 2 reports using the field coursenumber which is also how the sub report is grouped.

[ADezii]

I would imagine what the problem is relates to the case when the Report is used as a true Sub-Report with a Parent <==> Child Link based on the coursenumber Field. Regardless of the Code in the Sub-Report, only those Records that match the coursenumber in the Parent Report will be displayed.

[RockKandee]

I only want those records that match the parent report to display. The correct records are being displayed. The incorrect amount of blanks being displayed is the problem. Is there something I need to put into the main report so that the sub report will work? Or a better way to have a variable number of blanks display?

[NeoPa]

RockKandee
"I only want those records that match the parent report to display."
Do all the blank records match the parent record? Do they all have a [coursenumber] value that matches the [coursenumber] in the parent?

[RockKandee]

Yes

The blanks are just the last record printing the number of times requested with the font set to the background color so the text doesn't print.

[zmbd]

check,
can grow and can shrink properties in the parent report details/group section where the subreport lives.
Open main report in design view
Right click on the details bar of the main report
Select properties
Select format tab...

Sorry if this is a red herring; however, when I've had issues with my subreports not showing proper number of records, this has sometimes helped (and as often as has it's failed too).

It looks like you are using a variation of this:
ACC2000: How to Print a Constant Number of Lines Per Group

So where are the controls with your counts located on the report?

[RockKandee]

Can Grow = Yes
Can Shrink = No
Not sure what they should be set to or how they work.
I switched them, using every combo - didn't make a difference.
I am not needing so many lines as to not fit on a page, if that matters.

Didn't mind the red herring - it usually is something so simple that is over looked. Had my fingers crossed - lol

The controls for the count are in the header of the subform that I need the blank records for.

I tried putting them in the header of the main form - didn't help. Not sure I did it correctly or how to do it correctly. I am a bit lost when trying to get mains to talk to subs.

Yes - I am using something like the link you posted - only my blank lines are a variable not a constant.

I am trying to remake the form not using a subform but I am pretty sure this didn't work before, but that might have been before the code was completely working right. I have lost track now. Been at this for close to 40 hours now. I am really not liking normalization much :(

I am really grateful for you guys.

[zmbd]

Bingo:

The controls for the count are in the header of the subform that I need the blank records for.

The report header of a subreport is suppressed.
relocate these controls to a group header or a page header of the subreport. Refer to the link in my last post and note where the control was/is located. (^_^)

[RockKandee]

I should have said group header rather than just header. I said it correctly in my earlier post. I was slacking this time, sorry.

[RockKandee]

I don't understand this.

At one point, before the code was working, I had this subform in the main form footer. I thought I would try it this way again. To get the data to display higher up on the form I had to make the footer bigger (longer). On accident, I made the subform bigger and it made the empty lines show. So I went back to my other main form where the sub is in the details and made the sub longer and all is good.

It is almost always something really stupid.

Now I just need to figure out how to get blanks when there are no records. Should I start a new thread?

[zmbd]

details section of the sub... can grow = true?

This code makes the report print blank lines. The number of blanks is determined by max students - number of records. (...)Now I just need to figure out how to get blanks when there are no records. Should I start a new thread?

No, I think we're still in the same topic...

[RockKandee]

No - the sub report details can grow = NO
Even if I change it to Yes - it does not help.
The sub report has to be longer for the blanks to show.
I don't get why 5 records could show on one page, but on another page only one record and one blank would show. Doesn't make me think size problem, but it must be. Thanks for the red herring, probably would not have looked at print preview when I made the sub bigger if you hadn't had the size/fitting lines on my mind.

So big, wet, sloppy kisses for zmbd!

So now I am not sure how to handle getting blanks when the sub report has no records. This will be the case a lot of the time.

My understanding is that the sub report doesn't show and can't be made to show if no records exist. Since I need the sub report for when there are records, my thinking is to create a (temp)record when none exist, do the print job, then delete the (temp) record. Not sure if this is the best plan or not. Nor do I know how to do this with code, but at least it is keeping with what I already have.

I found this suggestion: "somehow get something into the subreport's RecordSource. Changing the subreport's RecordSource to a UNION query that contains at least one blank record would be one approach."

---OR---

I thought about using code in the main report footer to make blanks (not using records) but when their are no records in the sub report, I would lose my counters. I don't know how to make my counters be on the main report.

I found this: "You can print lines or rectangles in the On Page event of the report. The following code will print 25 numbered lines on a page regardless of the number of records on the page."

Private Sub Report_Page()
Dim intRows As Integer
Dim intLoop As Integer
Dim intTopMargin As Integer
intRows = 24
intDetailHeight = Me.Section(0).Height
intTopMargin = 360
For intLoop = 0 To intRows
Me.CurrentX = 20
Me.CurrentY = intLoop * intDetailHeight + intTopMargin
Me.Print intLoop + 1
Me.Line (0, intLoop * intDetailHeight + intTopMargin)- _
Step(Me.Width, intDetailHeight), , B
Next
End Sub

then I would need to adjust the code to be a variable and how to get counters on the main report and how to count when there is no sub report.

I am not sure what the best path to start down. Maybe neither of those thought processes are valid.

I would love some thoughts on this. Thanks in advance.

[RockKandee]

My solution to the second part of this topic was to create a new subform with a shared record source of the main form where the record source could only equal 1 record and made this not visible. I included the same controls and code in the new subform to get the correct number of rows to print. I then duplicated the lines and labels from the original sub form.

I placed the new sub form under the original subform.

I changed the lines and labels on the original sub form to not visible.

This results in the new sub form always printing the correct number of always blank lines and the original subform fills in the lines when it has records.

I was going to use the code below to switch between the 2 sub forms depending on whether the original subform had records or not. However, I couldn't figure out how to get the control on the main form to determine if the original subform had records or not without an error when the original subform did not have records.

Private Sub Form_Current()
    If Me.Active = True Then
        Me.rptRegistryStudentsSub.Visible = True
    Else
        Me.rptRegistryBlankSub.Visible = False
    End If
End Sub

I thought of the overlaying subforms solution first so I used it.