Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Condtional Formatting Using VBA (Access 2000)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Condtional Formatting Using VBA (Access 2000)

    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
    ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •