Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting in Report (Access 97)

    Currently the report FY05 Capital can be opened via seveal different forms. (2) of which are: Preliminary and another is ReportForm.

    What I would like to do is to tell the report to color the font of field [Project Classification] to red if the field [Deleted] = True. However, only do this if the report is opened via the form: Preliminary

    Now if is not possible to tell the report to only format if opened via certain form, I can work around that. But I do need the [Project Classification]'s field to be Red (or any color except black) if [Deleted] = True

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

    Re: Conditional Formatting in Report (Access 97)

    You can use the IsFormLoaded function from <post#=311628>post 311628</post#> to test if Preliminary is open. Of course, this does not guarantee that the report was opened from it. Alternatively, you could declare a global boolean variable in a standard module. Set it to True just before you open the report from Preliminary. You can turn it off again when you close the report.

    Access 97 does not have true conditional formatting, but you can use code in the On Format event of the section that contains ProjectClassification for this purpose. Here is an example using a global variable:

    <img src=/w3timages/blueline.gif width=33% height=2>

    In a standard module:

    Public blnCalledFromPreliminary As Boolean

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the module behind the Preliminary form:

    Private Sub cmdReport_Click()
    blnCalledFromPreliminary = True
    DoCmd.OpenReport ReportName:="FY05 Capital", View:=acViewPreview
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the On Format event of (for example) the Detail section of the report:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If blnCalledFromPreliminary = True And Me.Deleted = True Then
    Me.[Project Classification].ForeColor = vbRed
    Else
    Me.[Project Classification].ForeColor = vbBlack
    End If
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    In the On Close event of the report:

    Private Sub Report_Close()
    blnCalledFromPreliminary = False
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting in Report (Access 97)

    Now that was quick and easy!
    Thank You!!!!!

    I have been trying items like:
    if Me.Deleted = True then
    Me.Project Classification.Font = 255
    endif
    and other versions of the above. However, I kept coming up with "Unknowns" and it was driving me nuts. Therefore, I used your second suggestion:

    New Module Named: Preliminay Test
    Only one line in Module: Public blnCalledFromPreliminary As Boolean

    Then in Report, I placed the following right above the DoCmd line that opens the report (There are several conditions and select cases, so I here is the line I added)
    blnCalledFromPreliminary = True

    Next in the report, in the detail section:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If blnCalledFromPreliminary = True And Me.Deleted = True Then
    Me.[Project Classification].ForeColor = vbRed
    Else
    Me.[Project Classification].ForeColor = vbBlack
    End If
    End Sub

    Finally in the On Close of the Report:
    blnCalledFromPreliminary = False


    I have opened the report via all forms and it only highlights in red if it is opened via the Preliminary.

    Thank you!

    PBrown

  4. #4
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting in Report (Access 97)

    A slight problem has risen.....
    Since in my form there are select cases for "Active", "Deleted" and "All Items" I had to place the "blnCalledFromPreliminary = True" in each case like
    ....
    Select Case Me.Criteria
    Case 1 (Brings only Active items so formatting is not needed)
    strWhere = "[Deleted] = False"
    blnCalledFromAllBudget = False
    Case 2 (Brings only deleted itmes so formatting would be redundant)
    strWhere = "[Deleted] = True"
    blnCalledFromAllBudget = False
    Case 3 (Here is where all items are pulled therefore the formmatting is needed)
    strWhere = "[Deleted] <=0" ' nothing needed to get all records"
    blnCalledFromAllBudget = True
    End Select
    strWhere = strWhere & " and [Plant] = 'Base Trim & Chassis'"
    DoCmd.RunMacro "PlantCombo"
    DoCmd.OpenReport "Budget Reports", acViewPreview, , strWhere

    The problem is that if an area does not have any deleted items and the user choosed case 2 the following message appears:
    You entered an expression that has no value
    then the debug goes directly to: if blncalledfrompreliminary = true and me.Deleted = true
    inside the report.

    Is there away to have a msgbox appear if there are no "Deleted" records or any other way to avoid the error?

    Thank you again!

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

    Re: Conditional Formatting in Report (Access 97)

    You can test if there are records satisfying the conditions before opening the report. Say that tblSource is the record source of the report (it can be a query too, this is just for illustration purposes.)

    Select Case Me.Criteria
    ...
    End Select

    strWhere = strWhere & " And [Plant] = 'Base Trim & Chassis'"

    If DCount("*", "tblSource", strWhere) = 0 Then
    MsgBox "There are no records to display", vbInformation
    Else
    DoCmd.RunMacro "PlantCombo"
    DoCmd.OpenReport "Budget Reports", acViewPreview, , strWhere
    End If

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting in Report (Access 97)

    In a different form there are 2 Select Case's. The first is the same as the others with the Me.Criteria, however the other (Me.grpSortBy) selects the actual report and how to sort the report. Now if Case 2 from the grpSortBy is choosen everything works as it should. But if case 1 is choosen the conditional formatting does not appear. At first I though it was because the if statement in the report was wrong. Therefore I copied the one report that was working correctly and renamed it to the the report that was not formatting correctly. However, the conditioning still did not work, which makes me believe I am missing something in the code below:
    ........
    Select Case Me.Criteria
    Case 1
    strWhere = "[Deleted] = False"
    blnCalledFromComposition = False
    Case 2
    strWhere = "[Deleted] = True"
    blnCalledFromComposition = False
    Case 3
    strWhere = "[Deleted] <=0"
    blnCalledFromComposition = True

    End Select

    DoCmd.RunMacro "PlantCombo"
    If DCount("*", "Plant Combo Capital Budget List", strWhere) = 0 Then
    MsgBox "There are no items to display", vbInformation
    Else
    PlantSort = "[Plant] = 'Altima Trim & Chassis'"
    strWhere = strWhere & " and " & PlantSort

    strRptName = IIf(Me!grpSortBy > 1, "General Info By Code", "General Info")
    DoCmd.OpenReport strRptName, acViewPreview, , strWhere
    Reports(strRptName).Visible = False

    Select Case Me!grpSortBy
    Case 1
    Reports(strRptName).OrderBy = "Y1Sum DESC"
    Case 2
    Reports(strRptName).OrderBy = ""

    End Select

    Reports(strRptName).OrderByOn = True
    Reports(strRptName).Visible = True
    End If

    I have even tried making this a giant if then statement by placing if Me.grpSortBy = 1 then......
    Elseif me.grpSortBy = 2 then inwhich the second Select Case was removed from the code, howeve the same problem persists. The conditioning works for report "General Info By Code" but not for "General Info". Even when I copy ...By Code and Rename it to "General Info" the formatting does not work.

    Any suggestions or ideas?

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

    Re: Conditional Formatting in Report (Access 97)

    Sorry, no idea without seeing the database.

  8. #8
    Star Lounger
    Join Date
    Oct 2003
    Location
    Nashville, Tennessee, USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Formatting in Report (Access 97)

    Thanks anyways.....
    But I did a little re-formatting of the code and got it to work, but had to "hard code" a few things in the report.

Posting Permissions

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