Count fields that are conditionally Formatted

[tucker61]

I have 15 fields that are conditionally formatted based on there value, and i need to count eh number of fields that are Red and the number that are Green.

Whats the easiest way to do this ?

 

[theDBguy]

Not sure about the easiest, but one way is to apply the same conditions you used for CF in a Count(), DCount(), or IIf().

 

[tucker61]

Would it be better to try and do the conditional format in VBA ? or carry on using the in built conditional format ?

I have tried to convert to VBA, but get the issue below where it formats the full field dependant on the last ctl.value - and then place a count in this code ?

Or just a Dcount etc...

Sub ValidateForm()
Dim MyForm As Form
Dim Ctl As Control
Set MyForm = Forms!FrmMeasurements

For Each Ctl In MyForm.Controls
      
Select Case TypeName(Ctl)
            Case "TextBox"
    If Ctl.Tag = "ConFormat" Then
        If Ctl.Value > 0 Then
            Debug.Print Ctl.Name
            Debug.Print Ctl.Value
            If Ctl.Value > ([TBControlMeasurement] + [Tolerance]) Then
                    Ctl.BackColor = vbRed
            ElseIf Ctl.Value < ([TBControlMeasurement] - [Tolerance]) Then
                    Ctl.BackColor = vbRed
            Else
                    Ctl.BackColor = vbGreen
            End If
        End If
    End If
 End Select
Next

End Sub

 

[theDBguy]

Yeah, unfortunately, the code you just posted is not the same as Conditional Formatting. If you really want to use VBA to create Conditional Formatting, you'll have to use the FormatCondition object.

FormatCondition object (Access) | Microsoft Docs

If your current Conditional Formatting works, I don't see any need to convert them into VBA. I was just saying you should be able to use the same conditions for each color to count them. For example:

Red: DCount("*","TableName","FieldName>(Measurement+Tolerance)")

 

[tucker61]

I am struggling with this, I have got this code but now get a syntax error.
Am i best putting this in VBA or in the control source ?

Tbpass = Nz(DCount("*", "TblQcMeasurements", "Id = '" & tbid & "' AND "TbSample1 >(Measurement+Tolerance)"), 0)

 

[plog]

1. DCount will never return NULL so no NZ() needed.

2. Single quotes and double quotes must have partners. I count an odd number in your criteria statement.

3. Everything inside quote marks in the criteria must be in the table you are DCounting. Are Id, TbSample1, Measurement and Tolerance fields in tblQcMeasurments?

 

[theDBguy]

I am struggling with this, I have got this code but now get a syntax error.
Am i best putting this in VBA or in the control source ?

Tbpass = Nz(DCount("*", "TblQcMeasurements", "Id = '" & tbid & "' AND "TbSample1 >(Measurement+Tolerance)"), 0)

It might go faster if you could post a sample db with test data.

 

[tucker61]

DB is massive with links into lots of other systems, so will take me days to pull together a sample.

So far i have got the enclosed, being called from the Forms Current Sub.

This seems to be working, thanks to the advise above.

Next questions -

1. would i need to do the below for all 15 TextBox's (TbSample1 to TbSample15) ?
2. Do i call this from the Forms Current Sub ?
3. Would i write a further 15 lines for the "tbSample1 >0 AND < (TbControlMeasurement-TbTolerance)" or can this be written into the below as a "OR" EG - AND tbSample1 >(TbControlMeasurement+TbTolerance)" OR AND tbSample1 <(TbControlMeasurement-TbTolerance)"))

Sub ValidateForm()
Dim i As Integer
i = DCount("*", "TblQcMeasurements", "Id = " & tbID & " AND tbSample1 >(TbControlMeasurement+TbTolerance)")
i = i + DCount("*", "TblQcMeasurements", "Id = " & tbID & " AND tbSample2 >(TbControlMeasurement+TbTolerance)")
i = i + DCount("*", "TblQcMeasurements", "Id = " & tbID & " AND tbSample3 >(TbControlMeasurement+TbTolerance)")
i = i + DCount("*", "TblQcMeasurements", "Id = " & tbID & " AND tbSample4 >(TbControlMeasurement+TbTolerance)")
i = i + DCount("*", "TblQcMeasurements", "Id = " & tbID & " AND tbSample5 >(TbControlMeasurement+TbTolerance)")

tbFail = i
End Sub

 

[plog]

tbSample5

You've set up your tables incorrectly. When you start suffixing field names with numbers, its time for a new table. Further, you do that and this issue you've posted about becomes trivial--just one DCount into that new table.

That table will have this structure:

tblSamples
SampleID, autonumber, primary key
IDTblQcMeasurements, number, foriegn key to TblQcMeasurements
SampleNumber, number, this will hold the suffix on the end of all those samples
SampleValue, ?, this field will hold whatever value is currently in all those sample fields

That's it. Those 4 fields can now hold an unlimited number of samples for your data. Instead of filling in 5 fields in TblQcMeasurements you would add 5 rows to tblSamples for them.