Accurately detecting wether a subreport has data 2

[Minkers]

I need a method for accurately detecting wether a subform has data. If a certain subform has no data, parts of the main form will appear. If that subform has data, parts of the main form will be invisible.

Please help!
Minkers

 

[cghoga]

Have you looked at the On No Data event of the subreport?

 

[Minkers]

When I use the On No Data event to try and refer back to the main form the lines were invisible wether the form had data. I think that has to be detected in the OnFormat event of the main report.

 

[RoyVidar]

You are talking about form/subform, but this is the report forum - have you tried the HasData property of the subreport? It can be referenced from the main report, for instance like this:

[tt]=iif([rptMySub].Report.HasData,"yes","no")[/tt]

Roy-Vidar

 

[Minkers]

Actually another person helped me on the Utter Access forums. The working code is below:

If DCount("*", "qryCostAddedFirst3") <= 0 Then
Me.PartsLine1.Visible = True
Me.PartsLine2.Visible = True
Else
Me.PartsLine1.Visible = False
Me.PartsLine2.Visible = False
End If

 

[dhookom]

Minkers,
Roy has provided the optimal solution. Using DCount() opens another recordset which isn't necessary.

Me.PartsLine1.Visible = (Me.srptControl.Report.HasData = False)
Me.PartsLine2.Visible = (Me.srptControl.Report.HasData = False)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

Your right, that works alot better than the code I was using, however I find it is causing problems with one part. I have two subreports that I have squished as small as they can go to fit on the page and make it one page if the sub reports have no data. I inserted two page breaks so that if the reports have data, the subreports will be on a new page. Code is below. Page breaks are still staying visible, though opening the new recordset using the code I posted above was working (well mostly, it didn't work all the time). Any idea how to fix it? Using the code below gives me two blank pages in my report per record.

Me.PageBreak92.Visible = (Me.Child85.Report.HasData = False)
Me.DelPageBreak.Visible = (Me.Child87.Report.HasData = False)

 

[dhookom]

Is the code in the proper section event?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

The code is in the Details_Format section, the same place as the other codes that are making certain parts visible/invisible. It seems to be the only two lines not working correctly. This part of the report has been giving me a headache.

If there is a better way, I will go for that too. What happens is that I shortened the subforms that they code is referring to (child 85 and 87) to almost nothing and added them to the end of the first page. When there is no data I don't have the blank pages. When the data is there I get the header at the bottom of the first page and the rest on the new page.

I think the code is malfunctioning because of my queries. I use one query to restrict the data that is in the first subform on the page, and then use a second query to pick up the remainder onto the next subform. When I click on the second query, it will flash the full results for a moment and then go to whatever results it is supposed to have. I don't know a better way of doing what I am trying to do though. Perhaps we can find a work around. Queries posted below.

Thanks in advance!
Mink

SELECT TOP 3 tblCost.CostID, tblCost.ECNID, tblCost.Quantity, tblCost.UnitOfMeasure, tblCost.Phase, tblCost.Workstation, tblCost.UnitCost, tblCost.PartID, tblCost.ModelID, tblCost.StandardOrOptionalID
FROM tblCost
WHERE (((tblCost.ECNID) Like [Reports]![rptECN]![ECNID]) AND ((tblCost.PartAddDel)=0));

SELECT tblCost.CostID, tblCost.ECNID, tblCost.Quantity, tblCost.UnitOfMeasure, tblCost.Phase, tblCost.Workstation, tblCost.UnitCost, tblCost.PartID, tblCost.ModelID, tblCost.StandardOrOptionalID, tblCost.PartAddDel
FROM tblCost LEFT JOIN qryCostAddedFirst3 ON tblCost.CostID = qryCostAddedFirst3.CostID
WHERE (((tblCost.ECNID) Like [Reports]![rptECN]![ECNID]) AND ((tblCost.PartAddDel)=0) AND ((qryCostAddedFirst3.ECNID) Is Null));

 

[dhookom]

I assume these queries are the Record Sources of your two subreports.

I don't understand why you would use "Like" rather than "="?

Also, have you tried removing the ECNID=[Reports]... and using the Link Master Child Properties? Both properties on both subreport controls would be [Blue]ECNID[/Blue]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

These are the two record sources, the top to the one that is on the first page, the bottom to the one that goes on the last page.

I use Like because I am not very good at programing databases, and it worked somewhere else.

If I removed the ECNID= part then the subreport misbehaves. Unfortunately it seems to need that reliance to keep three records on the front page and the rest on the second subform. I don't quite understand why it needs this but it doesn't work without it.

 

[dhookom]

Setting the Link Master/Child properties works with reports and subreports exactly like it works with forms and subforms. The value from the main report is used to automatically filter the records in the subreport.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

The Master/Child links are set up properly for the report. When I remove the ECNID= part of the query, it leaves that sureport blank. I don't know why, but it does.

 

[dhookom]

So your sql of the first subreport is this?

SELECT TOP 3 tblCost.CostID, tblCost.ECNID, tblCost.Quantity, tblCost.UnitOfMeasure, tblCost.Phase, tblCost.Workstation, tblCost.UnitCost, tblCost.PartID, tblCost.ModelID, tblCost.StandardOrOptionalID
FROM tblCost
WHERE tblCost.PartAddDel=0;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

Yes, I tried that and the top subreport comes out completely blank. Master Field=ECNID, Child Field =ECNID. Subform on a query somewhat the same works just fine. Subreport will not.

 

[dhookom]

This assumes there is a field ECNID in the main report and the data types are the same.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

The field ECNID is the PK for the main report, and is used as a FK in the subreport. In the main table ECNID is an autonumber, FK is a long interget. The relationship functions fine on other forms.

 

[dhookom]

I think I know... The TOP 3 doesn't filter for the appropriate ECNID value. The only way this would work is if you were reporting your minimum ECNID value in the main report.

I generally don't use TOP 3. I would create a running sum in the subreport and cancel the formatting of the subreport section when the running sum value was greater than 3.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Minkers]

I believe your correct that TOP 3 is not filtering correctly, but I have tried the running sum/cancel thing and couldn't get it to work. I am at my deadline today, though this report problem is the last I need to fix. (knock on wood) Other than opening a recordset again, is there a way to fix this report? I can't have it printing blank pages.

 

[dhookom]

I add a text box to the detail section of the "top 3" subreport:
Name: txtCount
Control Source: =1
Running Sum: Over All
Visible: No
Then add code to the On Format event of the detail section of the subreport:

  Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Me.txtCount > 3
  End Sub

Make sure you remove the TOP 3 from the subreport's record source. This should allow the LInk Master/Child to determine the records. The code will cancel the printing of subreport records beyond the 3rd one.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]