Hiding a Control in a Multipe column report 1

[vegasvic21]

Hey all

I have a report that has data in the detail section like this:
Customer Name
Actual Budget Variance
Telephone 50.00 60.00 10.00
Gas 30.00 20.00 -10.00

Now the report is a 3 column report so in the detail section there are thre customers. My problem is the text box attached to the field in my query that show the words telehone and gas. They show up across the report in each column . I only want them to show up on the left side of the report in the 1st column.

any ideas

thanks
Vic

 

[lameid]

You mean that you have 4 controls on the report and one column in the Report's Page Setup area right?

If not perhaps, you are after a Crosstab query as the recordsource of the report as I described.

If that does not help, I am not sure I grasped your issue.

 

[dhookom]

If I understand correctly, check this How to Print Labels on the Left Margin of a Report

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vegasvic21]

close i cant use a crosstab because i need 2 colum headings. the other idea wont work because i only have 3 controls.

I have one query the report needs to be laid out like this

Comp A Comp B Comp C
Actual Budg Var Actual Budg Var Actual Budg Var
Salary 10 20 10 20 10 -10 30 20 10
Exp 20 10 -10 10 20 10 40 20 -20

now the headings on the left come from the query so they needs to be dynamic as they might change. comp a etc also comes from the query and that also has to be dynamic as the customer may get deleted or added. The problem is when I lay out the report to multi column the field on the left repeats in each column. I dont want it to.

thanks for you r help I hope i explained it better this time

Vic

 

[dhookom]

There are some sample crosstab reports at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

One has dynamic column headings.

There is a solution for creating multiple value crosstabs at faq701-4524.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[vegasvic21]

is there a way in VBA to say if a controls appears in a certain column hide it?

 

[lameid]

I Googled determining the column and came up with this...

http://support.microsoft.com/kb/q123712/

In a nutshell the left property of the report changes for each column.

Using that you might do something like test the left property and set the visible property of the control on the detail section's on format event.

The catch here is going to be whether the left property is available or not.

I'm curious to how it turns out.

 

[vegasvic21]

thanks lameid !!! your suggestion worked!!

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rpt As Report
Dim ctl As Control
Set ctl = Me.txtDESC
Set rpt = Me

If rpt.Left < "1000" Then

Me![txtDESC].Visible = True

Else
Me![txtDESC].Visible = False

End If

End Sub


thanks again

Vic