Formatting at Runtime

[Jeff Harbin]

I have a report that I am displaying data - some of which is calculated
when the report is opened. The report has 2 Groupings (Name and Level).

The Group Header for Name is empty but I have a calculated field and a
series of checkboxes in the Group Header for Level. I've got it set to
calculate the field upon the OnFormat Event for the Level Group Header.

I've also set the Visible property of the named checkboxes (Checkbox1,
Checkbox2, Checkbox3,...Checkbox10) to False. What I want to do is turn
the Visible property for some of the boxes to True based on the value of
the calculated field (ie., if the Calculated field value is 4 I want the
first 4 Checkboxes to be visible. If the value is 7, the first 7
checkboxes would be visible.)

So far the calculated field is updating correctly for each of the Level
Groups. However, I cannot get the proper number of Checkboxes to become
visible. It seems as though it works for the first page and then all
subsequent Level Groups have the same number of Checkboxes Visible as
the last group on the first page.

Here's the code that I'm using to set the Visible property to True. It
does turn some of them visible but not the correct number.

 

[Marshall Barton]

I have a report that I am displaying data - some of which is calculated
when the report is opened. The report has 2 Groupings (Name and Level).

The Group Header for Name is empty but I have a calculated field and a
series of checkboxes in the Group Header for Level. I've got it set to
calculate the field upon the OnFormat Event for the Level Group Header.

I've also set the Visible property of the named checkboxes (Checkbox1,
Checkbox2, Checkbox3,...Checkbox10) to False. What I want to do is turn
the Visible property for some of the boxes to True based on the value of
the calculated field (ie., if the Calculated field value is 4 I want the
first 4 Checkboxes to be visible. If the value is 7, the first 7
checkboxes would be visible.)

So far the calculated field is updating correctly for each of the Level
Groups. However, I cannot get the proper number of Checkboxes to become
visible. It seems as though it works for the first page and then all
subsequent Level Groups have the same number of Checkboxes Visible as
the last group on the first page.

Here's the code that I'm using to set the Visible property to True. It
does turn some of them visible but not the correct number.

I can not decipher that procedure, especially the reference
to the number of open reports (Reports.Count).

From your description, I think all you need is for the Level
group header's Format event:

For k = 1 To 10
Me("Checkbox" & k).Visible = (k <= [Calculated field])
Next k

 

[Jeff Harbin]

I have a report that I am displaying data - some of which is calculated
when the report is opened. The report has 2 Groupings (Name and Level).

The Group Header for Name is empty but I have a calculated field and a
series of checkboxes in the Group Header for Level. I've got it set to
calculate the field upon the OnFormat Event for the Level Group Header.

I've also set the Visible property of the named checkboxes (Checkbox1,
Checkbox2, Checkbox3,...Checkbox10) to False. What I want to do is turn
the Visible property for some of the boxes to True based on the value of
the calculated field (ie., if the Calculated field value is 4 I want the
first 4 Checkboxes to be visible. If the value is 7, the first 7
checkboxes would be visible.)

So far the calculated field is updating correctly for each of the Level
Groups. However, I cannot get the proper number of Checkboxes to become
visible. It seems as though it works for the first page and then all
subsequent Level Groups have the same number of Checkboxes Visible as
the last group on the first page.

Here's the code that I'm using to set the Visible property to True. It
does turn some of them visible but not the correct number.

I can not decipher that procedure, especially the reference
to the number of open reports (Reports.Count).

From your description, I think all you need is for the Level
group header's Format event:

For k = 1 To 10
Me("Checkbox" & k).Visible = (k <= [Calculated field])
Next k

I'm gonna try you're suggestion after dinner but in the meantime...the
iReportCount = Reports.Count

isn't necessary. I got the idea of trying what I did from some code
supplied in the Help file w/ACCESS. I modified it to do what I want but
you are correct that line of code doesn't serve any real purpose. It
doesn't hurt anything but it's not doing anything important.

I'll let you know what happens.

Thanks.

Jeff

 

[Jeff Harbin]

I tried the sample code you supplied and it is indeed a much simpler way
to do what I tried doing w/the code I copied in the original email.

However, both methods are yielded the same results. The first page had
the correct boxes visible and all subsequent pages did not. The had the
maximum number made visible from page 1. I realized that I not only
needed make the correct boxes visible based on the calculated number but
I had to change the visible property of any boxes that had previously
been made visible.

Long story short...thanks for the line on the abbreviated method and
issue resolved.

Thanks

I have a report that I am displaying data - some of which is calculated
when the report is opened. The report has 2 Groupings (Name and Level).

The Group Header for Name is empty but I have a calculated field and a
series of checkboxes in the Group Header for Level. I've got it set to
calculate the field upon the OnFormat Event for the Level Group Header.

I've also set the Visible property of the named checkboxes (Checkbox1,
Checkbox2, Checkbox3,...Checkbox10) to False. What I want to do is turn
the Visible property for some of the boxes to True based on the value of
the calculated field (ie., if the Calculated field value is 4 I want the
first 4 Checkboxes to be visible. If the value is 7, the first 7
checkboxes would be visible.)

So far the calculated field is updating correctly for each of the Level
Groups. However, I cannot get the proper number of Checkboxes to become
visible. It seems as though it works for the first page and then all
subsequent Level Groups have the same number of Checkboxes Visible as
the last group on the first page.

Here's the code that I'm using to set the Visible property to True. It
does turn some of them visible but not the correct number.

I can not decipher that procedure, especially the reference
to the number of open reports (Reports.Count).

From your description, I think all you need is for the Level
group header's Format event:

For k = 1 To 10
Me("Checkbox" & k).Visible = (k <= [Calculated field])
Next k

 

[Marshall Barton]

I don't understand. The code I posted should set the
visible property of every check box according to what I
thought your rule was. As I read your question, all check
boxes numbered less than or equal to the calculated value
should be visible and the check boxes numbered greater than
the calculated value should be invisible.

It seems to me that either I misunderstood the rule, or that
you have something else going on that is causing some kind
of trouble (e.g. the calculated value is not changing when
you think it is???)
--
Marsh
MVP [MS Access]

I tried the sample code you supplied and it is indeed a much simpler way
to do what I tried doing w/the code I copied in the original email.

However, both methods are yielded the same results. The first page had
the correct boxes visible and all subsequent pages did not. The had the
maximum number made visible from page 1. I realized that I not only
needed make the correct boxes visible based on the calculated number but
I had to change the visible property of any boxes that had previously
been made visible.

Long story short...thanks for the line on the abbreviated method and
issue resolved.

I have a report that I am displaying data - some of which is calculated
when the report is opened. The report has 2 Groupings (Name and Level).

The Group Header for Name is empty but I have a calculated field and a
series of checkboxes in the Group Header for Level. I've got it set to
calculate the field upon the OnFormat Event for the Level Group Header.

I've also set the Visible property of the named checkboxes (Checkbox1,
Checkbox2, Checkbox3,...Checkbox10) to False. What I want to do is turn
the Visible property for some of the boxes to True based on the value of
the calculated field (ie., if the Calculated field value is 4 I want the
first 4 Checkboxes to be visible. If the value is 7, the first 7
checkboxes would be visible.)

So far the calculated field is updating correctly for each of the Level
Groups. However, I cannot get the proper number of Checkboxes to become
visible. It seems as though it works for the first page and then all
subsequent Level Groups have the same number of Checkboxes Visible as
the last group on the first page.

Here's the code that I'm using to set the Visible property to True. It
does turn some of them visible but not the correct number.


Sub CheckBoxOnOff(iCntr As Integer)
Dim i As Integer, iReportCount As Integer
Dim iControlCount As Integer, rpt As Report
Dim j As Integer, k As Integer

iReportCount = Reports.Count

If iReportCount > 0 Then
For i = 0 To iReportCount - 1
Set rpt = Reports(i)
iControlCount = Report.Count
For j = 0 To iControlCount - 1
For k = 1 To iCntr
If rpt(j).Name = "Checkbox" & CStr(k) Then
rpt(j).Visible = True
End If
Next k
Next j
Next i
End If

End Sub

I can not decipher that procedure, especially the reference
to the number of open reports (Reports.Count).

From your description, I think all you need is for the Level
group header's Format event:

For k = 1 To 10
Me("Checkbox" & k).Visible = (k <= [Calculated field])
Next k

 

[Jeff Harbin]

Your code works perfectly for the first page where I've set all the
checkboxes Visible property to FALSE manually in Design mode. When the
code runs for the first page it is turning some of the checkboxes
properties to TRUE.

Now when Page 2 formats instead of having 10 checkboxes w/a Visible
property setting of FALSE, it has 7 (or whatever the highest calculated
value was for Page 1) checkboxes that are visible and 3 that are still
invisible. Therefore, I had to add code to what you supplied to turn
off the Visible property.

ie...

For i = 1 TO CalculatedValue
Me("Checkbox" & Cstr(i)).Visible = TRUE
Next i
Me("Checkbox" & Cstr(i)).Visible = FALSE
For j = CalculatedValue + 1 TO 10

Next j

 

[Marshall Barton]

I am still confused about what you are doing here. The code
I posted should take care of that without any additional
code.

Could you post a Copy/Paste ot the code you have now so I
can see exactly what you are using?

 

[Jeff Harbin]

I am still confused about what you are doing here. The code
I posted should take care of that without any additional
code.

Could you post a Copy/Paste ot the code you have now so I
can see exactly what you are using?

The following code is the entirety of the Event that works. The
variable iCntr is pass from the call procedure and represents the number
of checkboxes that need to be made visible.


Dim i As Integer, j As Integer

For i = 1 To iCntr
Me("Checkbox" & CStr(i)).Visible = True
Next i

For j = iCntr + 1 To 10
Me("Checkbox" & CStr(j)).Visible = False
Next j

 

[Marshall Barton]

The following code is the entirety of the Event that works. The
variable iCntr is pass from the call procedure and represents the number
of checkboxes that need to be made visible.


Dim i As Integer, j As Integer

For i = 1 To iCntr
Me("Checkbox" & CStr(i)).Visible = True
Next i

For j = iCntr + 1 To 10
Me("Checkbox" & CStr(j)).Visible = False
Next j

The code I posted would translate to just this:

Dim i As Integer

For i = 1 To 10
Me("Checkbox" & CStr(i)).Visible = (i <= iCntr)
Next i

It sets the ones you want visible and the others invisible
all in one loop.