Conditional Formatting on Subform

[Helen R]

I have a form with a subform(we'll call it A), and the subform also has a
subform (B). On the main form there is an optional field that the user can
select, and when they select it, I want to Highlight the rows on the subform
A that are associated with that field. The associated data for a row in
subform A is found in subform B.

Let me give an example. Subform A has a list of contractors and Subform B
has a row for each state that the contractor does business with. On the main
form there is a listbox with states, and if the user selects a state, I want
each row on Subform A that has that state in Subform B to be highlighted.

Any help would be appreciated!

 

[Dale Fye]

What is the Record Source for Subform A?

For subform B?

What is the linking field between Subform A and Subform B?

The way I would do this is to create a computed field in Record Source of
Subform A. So the query that defines the record source for subform A would
be something like:

SELECT A.Field1, A.Field2, A.Field3, Sum(IIF(B.State =
Forms!MainForm.lstState, 1, 0)) as IncludesState
FROM Table_A as A
LEFT JOIN Table_B as B
ON A.ContractorID = B.ContractorID
GROUP BY A.Field1, A.Field2, A.Field3

Then, I would create a control (textbox probably) that is bound to the
IncludesState field. I would then set the conditional formatting of that
control so that the forground and background colors are Green if that
control has a value >= 1, and the same as the forms background as the
standard value. This won't highlight each row in subform A, but should give
you a quick visual indicator where there is a match. You could also modify
that SQL so that is sorts by IncludesState Desc, which would put all of
those contractors that do business in that state at the top of the list in
Subform A.

Lastly, I would requery subform A whenever you change the value in the State
listbox. The format for this would be something like:

me.SubformControlName.Form.Requery

HTH
Dale

 

[Helen R]

Thanks! That works beautifully. However, when you click on a new state, the
control that is bound to IncludesState shows #Name? momentarily in the entire
column. Also, as you scroll down the subform, the value in the column shows
up as either a 0 or one for a second or 2 until the conditional formatting is
applied. Is there any way around that?

 

[Dale Fye]

Helen,

Take a look at the Echo method. This method (docmd.Echo False) basically
prevents screen updates until it incounters another command: docmd.Echo True

It's been a while, so I'd check out that format. The challenge here is that
you need to make sure you have an error handler active because if an error
occurs, you will not get a screen update. Turn it off (false) in the click
event of the listbox, then turn it back on after you requery the subform.

HTH
Dale