IF HasData 2

[humvie]

I have a report which has several subforms. One particular subform has the HASDATA code so that it only shows up when data exists. This part works great and as it should.

My problem is that I have about 10 objects ranging from lines to labels to text boxes (all on that same report) that I want to disable the visible property WHEN the HasData code result is true.

In other words, if there is data in subform A then hide object 1, 2, 3, etc.... if no data then report stays as is.

I've been strugling with this for three days and would like a fresh "suggestion".

Thank you in advance.

 

[Eupher]

In the On Format event of the Detail section of the (sub)report, create an event procedure with some code:

If Me.HasData Then
     Me!FieldName1.Visible = False
     Me!FieldName2.Visible = False
     etc...
End If

Substitute your own report's field names as appropriate.

Ken S.

 

[humvie]

Thanks Ken but I'm still having problems.

Under my report I have the following code:

Private Sub Report_NoData(Cancel as Interger)
Me!qryFormNameSubform.visible = Me!qryFormNameSubform.Report.HasData
End Sub

and under the sub report's detail object I have :

Private Sub Detail_OnFormat()
If qryFormNameSubform.HasData then
Me!FieldName1.visible = false
Me!FieldName2.visible = false
End If
End Sub

Am I missing anything?

I included a toggle breakpoint on the first line of the Detail_Onformat and it didn't even trigger.

I'm using Access 2K for what it's worth.

 

[Eupher]

Where are the objects located that you want to hide? On the main form or subform? Which band? (report header, page header, detail, etc.). What are the actual names of your form, subform, and the objects you want to hide? (that will help in case of syntax errors)

Ken S.

 

[humvie]

The objects are on the main report located in the DETAIL section.

main report: rptBOL
subform: qryShipmentDimsSubform

Objects to hide:

box43
box134
line50
label58


Here's the scenario: if qryShipmentDimsSubform has data the objects listed above need to be hidden.

thanks

 

[Eupher]

The HasData property only applies to report objects, not forms, so you need to refer to the subform's recordset, i.e.:

Private Sub Detail_Format()
Dim Rs As Recordset
Set Rs = Me!qryShipmentDimsSubform.Form.Recordset
If Rs.RecordCount = 0 Then
     Me!box43.Visible = False
     Me!box134.Visible = False
     Me!line50.Visible = False
     Me!label58.Visible = False
End If
End Sub

Ken S.

 

[humvie]

Oh man it worked. What a relief! Thanks a million Ken.

 

[humvie]

Ken,

I got it to work on my laptop (Windows XP and Acess 2K) and converted the file to Access97. Copied onto another computer with Access97 and opened the file then compiled it before running it.

I get an error 2465 (raise error) and highlights this line:

Set Rs = Me!qryShipmentDimsSubform.Form.Recordset

Any idea what would have cause it to error?

 

[TLady]

Ok, and what about a STAR for the man who solved your 3 days headache ?
I'll do it, Ken.
TIA

 

[humvie]

Ouch. I keep forgeting about this STAR thing. But no need to bite. Touchy Touchy

... and Ken, I do appreciate what you've done.

Thanks again.

 

[Eupher]

Usually this kind of error is a references problem. Open any code window, select Tools->References from the menu, and check whether "Microsoft DAO 3.6 Object Library" shows up in the list (or at least DAO 3.51). If not, scroll down and find it in the list and check it. Then move it up in priority until it's about 3rd in the list. Click OK, then re-compile, and with luck you should be in business.

Ken S.

 

[humvie]

Hey Ken,

Tried that and I do have the references setup right. I was going over some other threads / other forums and noticed that several of the messages made mention of incorrectly named forms when refrencing them in the code.

Would Access97 see the following any different than Access2K?:

Set Rs = Me!qryShipmentDimsSubform.Form.Recordset

I tried brackets, no brackets, dot (. & !) etc ...

 

[Eupher]

As far as I know that syntax should work in Access 97; however, my experience with Access pretty much begins with 2000, so I can't say for sure. What is the description of error 2465? I'm not familiar with that one...

Ken S.

 

[humvie]

"Unexpected Error Occured. 2465 Application defined or Object-defined error."

 

[humvie]

I'm back !

I managed to get this working by changing the recordset code to this:

Set rs = Me.qryShipmentDimssubform.Form.RecordsetClone

(for Access 97)

But now I have the same problem with Access2k !!!!!!! It worked fine before I made a converted copy.

What I found though is this ...

If I set a toggle breakpoint at

If rs.RecordCount > 0 Then ....

while I have data in the recordset, it shows one (1) record and continues on to print the report as it should.

However, if I do the same but without any data in teh recordset,it highlights the HASDATA code.

Private Sub Report_NoData(Cancel as Integer)
Me!qryShipmentDimssubform.Visible = Me!qryShipmentDimssubform.Report.HasData
End Sub

What else can I do to narrow this error down?

 

[Eupher]

Re: the code in your NoData event procedure

Remember, the HasData property does not apply to a form object, so if qryShipmentDimssubform is a form, not a report, you'll get an error message when Access tries to execute that line of code. In any event, I'm not sure what the code is supposed to do. Is it:

1) If there is no data in the main report, hide the subform (even if there IS data in the subform)? If so, since you've placed your expression in the NoData event, the right half of the expression is redundant. You already know there's no data in the report, else the NoData event wouldn't have fired, so no need to test for it again. And, of course, HasData won't work with a form object. So use this instead:

Me!qryShipmentDimssubform.Visible = False

2) If there is no data in the main report, then show the subform if it has data and hide the subform if it has no data? Unnecessary. If there's no data in the subform nothing will display; conversely, if there IS data in the subform, it will display whether there is data in the main report or not. If this is what you want, then you can delete the whole NoData procedure.

If I've misunderstood your intent, I apologize.

HTH...

Ken S.