By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Main report not allowing code from subreport

P: 89
I am using Access 2013 on Windows 8

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

Expand|Select|Wrap|Line Numbers
  1. Public totalCount As Integer
  2. Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) '
  3.     Dim students As Integer, maxR As Integer
  4.     students = Me.Txcountrecords
  5.     maxR = Me.txMaxStudent
  6.     printBlankRecords Me, students, maxR
  7. End Sub
  8. Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
  9.     Me.txFull_Name.ForeColor = vbBlack
  10.     Me.txeMail.ForeColor = vbBlack
  11.     Me.txPhone.ForeColor = vbBlack
  12.     totalCount = 0
  13. End Sub
  14. Public Sub printBlankRecords(rpt As Access.Report, usedRecords As Integer, MaxRecords As Integer)
  15.     totalCount = totalCount + 1
  16.     If totalCount = usedRecords Then
  17.         rpt.NextRecord = False
  18.     'once you get to the last record, stay on last record
  19.     ElseIf totalCount > usedRecords And totalCount < MaxRecords Then
  20.         rpt.NextRecord = False
  21.         'MsgBox usedRecords & " " & MaxRecords & " " & totalCount
  22.         'make the font and backcolor the same appearing to be empty record
  23.         Me.txFull_Name.ForeColor = Me.txFull_Name.BackColor
  24.         Me.txeMail.ForeColor = Me.txeMail.BackColor
  25.         Me.txPhone.ForeColor = Me.txPhone.BackColor
  26.     End If
  27. 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
Dec 29 '13 #1
Share this Question
Share on Google+
14 Replies

Expert 5K+
P: 8,607
  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?
Dec 30 '13 #2

P: 89
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.
Dec 30 '13 #3

Expert 5K+
P: 8,607
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.
Dec 30 '13 #4

P: 89
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?
Dec 30 '13 #5

Expert Mod 15k+
P: 31,273
"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?
Dec 30 '13 #6

P: 89

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.
Dec 30 '13 #7

Expert Mod 5K+
P: 5,287
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?
Dec 30 '13 #8

P: 89
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.
Dec 30 '13 #9

Expert Mod 5K+
P: 5,287
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. (^_^)
Dec 30 '13 #10

P: 89
I should have said group header rather than just header. I said it correctly in my earlier post. I was slacking this time, sorry.
Dec 30 '13 #11

P: 89
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?
Dec 31 '13 #12

Expert Mod 5K+
P: 5,287
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...
Dec 31 '13 #13

P: 89
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."


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."
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Page()
  2. Dim intRows As Integer
  3. Dim intLoop As Integer
  4. Dim intTopMargin As Integer
  5. intRows = 24
  6. intDetailHeight = Me.Section(0).Height
  7. intTopMargin = 360
  8. For intLoop = 0 To intRows
  9. Me.CurrentX = 20
  10. Me.CurrentY = intLoop * intDetailHeight + intTopMargin
  11. Me.Print intLoop + 1
  12. Me.Line (0, intLoop * intDetailHeight + intTopMargin)- _
  13. Step(Me.Width, intDetailHeight), , B
  14. Next
  15. 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.
Dec 31 '13 #14

P: 89
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If Me.Active = True Then
  3.         Me.rptRegistryStudentsSub.Visible = True
  4.     Else
  5.         Me.rptRegistryBlankSub.Visible = False
  6.     End If
  7. End Sub
I thought of the overlaying subforms solution first so I used it.
Jan 25 '14 #15

Post your reply

Sign in to post your reply or Sign up for a free account.