Make Lines Visible Based on Data 1

[shelby55]

Hi

I'm using Access 2003.

Thanks to MajP I was able to create a report where header fields are visible or not depending on whether there is data for the month selected.

I added vertical lines to the report and was able to make the lines invisible in the group header and footer sections based on the same information MajP gave me. For the footer I used:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.ln1_C.Visible = Not IsNull(Me.qryM2_A_Cases)
Me.ln2_C.Visible = Not IsNull(Me.qryM3_A_Cases)
Me.ln3_C.Visible = Not IsNull(Me.qryM4_A_Cases)
Me.ln4_C.Visible = Not IsNull(Me.qryM5_A_Cases)
Me.ln5_C.Visible = Not IsNull(Me.qryM6_A_Cases)
Me.ln6_C.Visible = Not IsNull(Me.qryM7_A_Cases)
Me.ln7_C.Visible = Not IsNull(Me.qryM8_A_Cases)
Me.ln8_C.Visible = Not IsNull(Me.qryM9_A_Cases)
Me.ln9_C.Visible = Not IsNull(Me.qryM10_A_Cases)
Me.ln10_C.Visible = Not IsNull(Me.qryM11_A_Cases)
Me.ln11_C.Visible = Not IsNull(Me.qryM12_A_Cases)
End Sub

MajP also gave me the following code for the detail sections for data fields that are empty:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Access.Control

For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
    ctl.Visible = Not IsNull(ctl)
    End If
Next ctl
End Sub

The problem with using the same code for the detail section is that if there is data for the whole month but no data for one of the groups then a vertical line doesn't show up for that group and I don't get a solid line for the entire detail section which is what I want.

What do I need to do so the line is solid in the detail section? Thanks.

 

[MajP]

startdate is a global date variable. If it is not set to anything then by default it equals 0. In vb a date of 0 is 12/30/1899, so that appears correct. You can set it manually in the immediate window for test purposes.
startdate = #some date here#

 

[shelby55]

Hi

Thanks very much...but then why is it not working to make the lines invisible?

 

[MajP]

can you post your hidelines code?

 

[shelby55]

Hi

The code for hidelines that I placed in the Report Module is:

Public Sub hideLines()
Dim maxDate As Date
maxDate = getMaxDate


Me.ln1_A.Visible = (maxDate >= addMonth(startDate, 1))
Me.ln2_A.Visible = (maxDate >= addMonth(startDate, 2))
Me.ln3_A.Visible = (maxDate >= addMonth(startDate, 3))
Me.ln4_A.Visible = (maxDate >= addMonth(startDate, 4))
Me.ln5_A.Visible = (maxDate >= addMonth(startDate, 5))
Me.ln6_A.Visible = (maxDate >= addMonth(startDate, 6))
Me.ln7_A.Visible = (maxDate >= addMonth(startDate, 7))
Me.ln8_A.Visible = (maxDate >= addMonth(startDate, 8))
Me.ln9_A.Visible = (maxDate >= addMonth(startDate, 9))
Me.ln10_A.Visible = (maxDate >= addMonth(startDate, 10))
Me.ln11_A.Visible = (maxDate >= addMonth(startDate, 11))
Me.ln12_A.Visible = (maxDate >= addMonth(startDate, 12))

End Sub

I actually have this code per line section i.e. there are lines B for detail section and lines C for the footer. Thanks.

 

[MajP]

Please modify and post back

Public Sub hideLines()
Dim maxDate As Date
maxDate = getMaxDate
'Add code for debugging
debug.print "maxDate " & maxDate
debug.print "Ln1 " & addMonth(startDate, 1)
debug.print "Ln1 " & maxDate >= addMonth(startDate, 1)
...

If the results are as expected, then change the event that calls this to the detail format event.

 

[shelby55]

Hi

When I try to run the sub the macro window shows up but nothing happens because there is no macro to select. In my previous tests, the info appeared in the immediate window.

 

[shelby55]

Hi MajP

I just went ahead and moved the code to the format event of each section and now it works!

Thanks so much for your patience and assistance!!

 

[MajP]

since hidelines is in a reports module it will not show up, because you have to run it from the report. Only code in standard modules will show.

You can test code in a report/form module by fully qualifying it. Example

Public sub testReportCode()
'If this is in a standard module you can call code in an open form/report
'By fully referencing the report or form
reports("yourReportName").hideLines
end sub