Condtional Formatting Using VBA (Access 2000)

Topic

New Reply

I am building a report using VBA with a from that accepts input data and calls a Crosstab query. I want to make invisible results that are zero. The report is dynamic changing every time it is loaded so I cannot set up a report form and use the OnPrint or Onformat events. I assume there is a way to use those procedures with VBA but have not been able to figure it out, can anyone help please.

Peter

Reply | Quote

Replies

Are you trying to simply make results that are zero invisible in the report, or are you trying to make entire sections of a report invisible if all cells in a given column (or row) are zero? The former would be most easily done by using the Nz function in the crosstab. The latter is the way that crosstabs run as long as you don’t use fixed column heads. If this isn’t your issue, give us more details.

Reply | Quote

only want to make invisible those entries that are zero as opposed to entire columns or rows. Must admit I am not familiar with the Nz function. I have sort of solved my probelm but it is very cumbersome and seems to take a long time to run. I have written a function as follows.

Function Detail_Format()
Dim rpt As Report, ctl As Control
‘circles through all the controls on the Report if a control is a textbox and is zero makes it invisible
For Each rpt In Reports
For Each ctl In rpt.Controls
If ctl.Name = “text1” Then
With ctl
.Width = xy
.FontWeight = 700
.Value = Description ‘Sets up dates of the week numbers in report
End With
‘ctl.Value = Description
End If
If ctl.ControlType = acTextBox Then
If ctl.Value = 0 Then
ctl.Visible = False
End If
End If
Next ctl
Next rpt
End Function

This is called after I close and reopen the report I have generated.

Thanks for your help

Peter

Reply | Quote

1. Why do you loop through all open reports? You only want to check controls on the current report, don’t you? Also, you probably want to check controls in the Detail section only. If so, remove “For Each rpt in Reports” and “Next rpt”, and replace “For Each ctl in rpt.Controls” by “For Each ctl in Detail.Controls”.

2. Why can’t you use the Conditional Formatting item from the Format menu? It doesn’t seem to me that the report being dynamic is an obstacle.

Reply | Quote

Hi Hans

If I dont use the loop for all Reports I get an error message when I try and use the report name itself. As the only report that is the one I am concerned with it is not a probelm, but I appreaciate that this is clumsy. Thank you for the suggestion about using a more precise definition of the section I am in, shows I am a novice. I have tried to figure out how to use conditional formatting using VBA without success. The report is dynamic as it ask the operator to select (from a claendar) a starting and ending date and builds the report of a series of weeks based on the input. I have used a template for the report but that is just to makes sure it is in landscape rather than portait format.

Thank you for your help, you might not recall but you helped me with the crosstap suggestion a few weeks back, which was for the same project.

Peter

Reply | Quote

I don’t know (or don’t remember) how you are creating a dynamic report. Are you using a fixes set of controls whose contents and properties you set dynamically in code, or are you creating controls on the fly?

With fixed controls, you should be able to set conditional formatting for them interactively, since your requirements are relatively simple: if the value is 0, the foreground color must be the same as the background color.

If you are creating controls in code, you can use FormatConditions, at least in Access 2002; I can’t test this in Access 2000:

Dim ctl As Control
…
‘ Add conditional formatting that tests whether the field value is 0
With ctl.FormatConditions.Add(acFieldValue, acEqual, 0)
‘ Set the text color
.ForeColor = vbWhite
End With
…

Reply | Quote

I don’t know (or don’t remember) how you are creating a dynamic report. Are you using a fixes set of controls whose contents and properties you set dynamically in code, or are you creating controls on the fly?

With fixed controls, you should be able to set conditional formatting for them interactively, since your requirements are relatively simple: if the value is 0, the foreground color must be the same as the background color.

If you are creating controls in code, you can use FormatConditions, at least in Access 2002; I can’t test this in Access 2000:

Dim ctl As Control
…
‘ Add conditional formatting that tests whether the field value is 0
With ctl.FormatConditions.Add(acFieldValue, acEqual, 0)
‘ Set the text color
.ForeColor = vbWhite
End With
…

Reply | Quote

Hi Hans

If I dont use the loop for all Reports I get an error message when I try and use the report name itself. As the only report that is the one I am concerned with it is not a probelm, but I appreaciate that this is clumsy. Thank you for the suggestion about using a more precise definition of the section I am in, shows I am a novice. I have tried to figure out how to use conditional formatting using VBA without success. The report is dynamic as it ask the operator to select (from a claendar) a starting and ending date and builds the report of a series of weeks based on the input. I have used a template for the report but that is just to makes sure it is in landscape rather than portait format.

Thank you for your help, you might not recall but you helped me with the crosstap suggestion a few weeks back, which was for the same project.

Peter

Reply | Quote

1. Why do you loop through all open reports? You only want to check controls on the current report, don’t you? Also, you probably want to check controls in the Detail section only. If so, remove “For Each rpt in Reports” and “Next rpt”, and replace “For Each ctl in rpt.Controls” by “For Each ctl in Detail.Controls”.

2. Why can’t you use the Conditional Formatting item from the Format menu? It doesn’t seem to me that the report being dynamic is an obstacle.

Reply | Quote

Sorry, the Nz funtion was a momentary brain fade – it only will check for null, not zero. In its basic form it goes the other way, turning a Null into a zero. I see how you have approached the problem, but it seems to be quite cumbersum to me as well. I’ve worked a fair bit with reports based on crosstab queries and not encountered one where I needed a report to be dynamic, and in fact for a whole series of reports to be dynamic. Some additional background on what problem you are solving would be helpful – often when one looks at the bigger picture, there is an alternative approch that is much less work. In fact Hans may have hit on the solution with the use of Conditional Formatting. Let us know if you need further background on that approach.

Reply | Quote

Hi Wendell

You will see I have replied to Hans re his suggestions. The input form offers 2 calendars from witch a start and end date are selected. The date part function is used to determine the week numbers for start and end and this is fed to a query which is the bae for the crosstab query that selects the required weeks, hence depending on the selected weeks the report is dynamic. As I said in my reply to Hans I cannot figure out how to write the code to use conditional formatting.

Thanks for your help.

Peter

Reply | Quote

Hi Wendell

You will see I have replied to Hans re his suggestions. The input form offers 2 calendars from witch a start and end date are selected. The date part function is used to determine the week numbers for start and end and this is fed to a query which is the bae for the crosstab query that selects the required weeks, hence depending on the selected weeks the report is dynamic. As I said in my reply to Hans I cannot figure out how to write the code to use conditional formatting.

Thanks for your help.

Peter

Reply | Quote

Sorry, the Nz funtion was a momentary brain fade – it only will check for null, not zero. In its basic form it goes the other way, turning a Null into a zero. I see how you have approached the problem, but it seems to be quite cumbersum to me as well. I’ve worked a fair bit with reports based on crosstab queries and not encountered one where I needed a report to be dynamic, and in fact for a whole series of reports to be dynamic. Some additional background on what problem you are solving would be helpful – often when one looks at the bigger picture, there is an alternative approch that is much less work. In fact Hans may have hit on the solution with the use of Conditional Formatting. Let us know if you need further background on that approach.

Reply | Quote

only want to make invisible those entries that are zero as opposed to entire columns or rows. Must admit I am not familiar with the Nz function. I have sort of solved my probelm but it is very cumbersome and seems to take a long time to run. I have written a function as follows.

Function Detail_Format()
Dim rpt As Report, ctl As Control
‘circles through all the controls on the Report if a control is a textbox and is zero makes it invisible
For Each rpt In Reports
For Each ctl In rpt.Controls
If ctl.Name = “text1” Then
With ctl
.Width = xy
.FontWeight = 700
.Value = Description ‘Sets up dates of the week numbers in report
End With
‘ctl.Value = Description
End If
If ctl.ControlType = acTextBox Then
If ctl.Value = 0 Then
ctl.Visible = False
End If
End If
Next ctl
Next rpt
End Function

This is called after I close and reopen the report I have generated.

Thanks for your help

Peter

Reply | Quote