Hide Sub Reports with Select Case

[Alan Forman]

Hi Guys

I have a main report called rpt_Presentation, and lots of sub reports which I only what to show one at a time depending on a user choice by changing a field called 'Type_of_Business_Code' which has numerical values 1 to 10.

The following code I've put on a 'Report Open'

I've tried .....

Select Case Me.Type_of_Business_Code

Case "1"
Reports!rpt_Presentation!rpt_Package.Visible = False
Reports!rpt_Presentation!rpt_Commercial_Combined.Visible = True

Case "2"
Reports!rpt_Presentation!rpt_Package.Visible = True
Reports!rpt_Presentation!rpt_Commercial_Combined.Visible = False

End Select

But that returns a .....

Run-Time Error "2427", You entered an expression that has no value

If I rem out the code, the numeric field has got data in.

Any help would be great.

Thanks

 

[vbaInet]

Where did you put the code?

Are you supposed to hide it depending on the value on each record or it's meant to happen just once?

If that field is a numeric field then you should get rid of the quotes plus it may return Null values so here:

Select Case Nz(Me.Type_of_Business_Code, 0)

Case[COLOR=Red][B] 1[/B][/COLOR]

 

[Alan Forman]

Hi

I tried "Select Case Nz(Me.Type_of_Business_Code, 0)", but that returns ab Run-time error -2147452567 (80020009).

I've put the code on a 'Report Open'

The button that opens the report 'filters' to a report to one record, then this code supposed to hide the sub reports not for that report.

Thanks for the help.

Alan

 

[vbaInet]

Open event is too early. Records haven't been pulled at that stage. Put it in the Load event of the main form.

 

[Alan Forman]

Re: Hide Sub Reports with Select Case [SOLVED]

Solved it due to your help, i was putting the select case in too early, on the report itself rather than the code to open the report.

Thanks very much, I've been working on that for about 20 hours.

Thanks again

Alan

 

[vbaInet]

20 hours At least you didn't give up easily. I like your tenacity.

Happy to hear you resolved the problem.