format report group header

Question

Hello dear Thank you for your reply, I have a report then contain 7 group levels. I want if a field that is in groupheader7 (ThirdCenterCode) was null, it’s group header ( groupheader7) visible property be set to No. and it should be control for each rows. Because it my in another record, that field is not null and group header 7 should be shown. I try this code in format property of group header 7, but it not work and return the below error:

Private Sub GroupHeader7_Format(Cancel As Integer, FormatCount As Integer)
If IsNull([ThirdCenterCode]) Then
Me.GroupHeader7.Visible = False
End If
End Sub

Run-time error ‘2465’: Microsoft access can’t find the field ‘ThirdCenterCode’ referred to in your expression.

Thank you a lot With best regards Ehsan Shahidifar [email protected]

Answer 1

The error is usually true enough. Double check the spelling - it can be useful to use intellisense to find controls and fields associated with the current form or report, you can do this by typing Me. and seeing if it is listed.

Answer 2

In the VBA of a report, you can't refer to fields in the reports underlying recordsource unless there's a control on the report that uses that field as its Control Source. This was not always the case.

So, if you want to check the value of Me!ThirdCenterCode, then create a control and set that as its ControlSource, and then sets its .Visible property to No.

Of course, if you already have a control with ThirdCenterCode as its ControlSource, that is not the answer, and it's more likely a case of simply misspelling the name of the field.

Answer 3

Dear Thanks for your replay. I found that Cause of error message, was the wrong location of it. It should be locate on detail section of report. Then I locate it in OnFormat of detail section of report. I found that if only one record of first group be Null Access will not show the ThirdCenterCode Section for all rows of current first group data. And it is not that I want, because there are some rows of data that ThirdCenterCode field of them are not Null and should be shown. For solving this problem I locate my code in OnLoad section of report and it solve my first problem, But Led to another problem. In this section access doesn’t show me the ThirdCenterCode Group section if all of records were null and if Only one record was not null, Access show the ThirdCenterCode section. And it causes that one or more rows in my report became blank. Finaly for solving this problem I Copy and Paste code Of OnLoad section of report to OnPrint of Detail of report. Disbeliefly I found that when report is shown, my last problem is not solved. But when I print report, my problem is solved. ( It means that if ThirdCenterCode is Null Access don’t show it’s section group , and if it is not Null Access show it for each record.)

Best wishes Ehsan Shahidifar [email protected]