Conditional Formatting via Combo Box

[dl7320]

I am trying to highlight a field [town] (or the whole row) of a report.

I want the user to be able to select which town is to be highlighted from a form combo box, and then base the conditional formatting on the results of the combo box, right before they would select the print command button, each and every time they run the report.

I must be way off on trying to figure out the CF expression, (I tried [Forms]![Main Menu]![Combo 24].column(2)) or this is just not the way to do this. Maybe I am properly storing the temporary data(town to be highlighted).

Thanks for any input

 

[ProgramError]

Is the form still open when you run the form?


Ian Mayor (UK)
Program Error
If people say I have bad breath, then why do they continue to ask me questions and expect me to answer them?

 

[dl7320]

Yes, The form is still open when I run the report.

 

[MajP]

There are two ways I could think to do this.

1) have a constant format condition but change the value of the field. For example using Northwind's "Alphabetical List of Products" there is a text box called "CategoryName"

a. In a standard module make a global variable to assign to a choosen category
public glblCat as string
b. make a function that returns the global variabl
Public Function getGlobalCat()
getGlobalCat = glblCat
End Function
c. Choose from my form and pick a category
glblCat = me.lstOne (i.e. "seafood")
d. On the report's conditional formatting for CategoryName
Expression Is:
[CategoryName]=getGlobal()
e. In the reports close event
glblCat = ""

2)The more complicated method (but maybe more flexible) is to work with "formatConditions" collection in code. You can change the format conditions only in design view.

 Public Sub openReportWithFormat()
  Dim txtBxName As String
  Dim rptName As String
  Dim strProduct As String
  Dim txtBx As Access.TextBox
  Dim fmtCon As Access.FormatCondition
  rptName = "Alphabetical List of Products"
  txtBxName = "ProductName"
  'open form and get product from you list box
  ' I will hard code 'Chai' to demonstrate
  strProduct = "'Chai'"
  DoCmd.OpenReport rptName, acViewDesign, , , acHidden
  Set txtBx = Reports(rptName).Controls(txtBxName)
  Set fmtCon = txtBx.FormatConditions(0)
  fmtCon.Modify acFieldValue, acEqual, strProduct
  DoCmd.Save acReport, rptName
  DoCmd.Close acReport, rptName
  DoCmd.OpenReport rptName, acPreview
End Sub

 

[dl7320]

Thank you.

I understand the second one a little more than the first. So I guess that is the one I will try.

 

[MajP]

First one is way easier. Let me try explaining better. What part was confusing?

second version was for demonstration of formatConditions, but I would not recommend it unless you were doing something more complicated.

 

[MajP]

1. At the top of a standard module you should have:
Option Explicit
Compare Database

public glblTown as string

2. In the same module add a simple function that returns the global variable:

Public Function getTown()
getTown = glblTown
End Function

3. Have an event that sets the value of the variable. This is probably the after update event of the combobox on your form. Something like

private someControl_someevent()
glblTown = me.cmboTown
end sub

4.On the report in question, go to conditional formatting for the field/fields. Select:
Expression Is:
[Town]=getTown()

5. In the report's onclose event set the glblTown variable to "".
glblTown =

 

[PWise]

This works form me
Pwise
note:
1)Combobox index is zero based so column(2) is third column not second column
2)if you choses the town on the form
a)to CF the town field use field value is equle to [Forms]![Main Menu]![Combo 24].column(x)
b) to CF other fields us expression is [town]=[Forms]![Main Menu]![Combo 24].column(x)

let me know if this works

 

[MajP]

That is a little simpler, but basically the same idea. I suggested the global to allow you to open the report independantly without opening the from for highlighting. I think if the form is not open pwise's solution will throw and object not found error.