Conditional Formatting (1 Viewer)

[abenitez77]

I have conditional formatting working in a subform. But I have another form with another subform and it is not working there. Do you see anything wrong here:

Dim objFrc As FormatCondition
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = DBEngine(0)(0)
GBL_Username = Environ("username")

strSQL = "Select * from CondFormat Where (UserName = '" & GBL_Username & "' OR Cond_Type = 'Global') AND ReportListID = " & ProjID
Forms("Conditionals").Form.RecordSource = strSQL

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

If Not rs.EOF Then
rs.MoveFirst


'Get the saved values for conditional formatting.
Do While Not rs.EOF
'Forms("Conditionals")("Conditionals_Sub").Controls("Cond_BackColor").FormatConditions.Delete
'Forms("Conditionals")("Conditionals_Sub").Controls("Cond_ForeColor").FormatConditions.Delete
'mycondition = "= Cond_FieldName =" & Chr(34) & rs!Cond_FieldName & Chr(34) & " AND UserName =" & Chr(34) & rs!UserName & Chr(34) & " AND FormName =" & Chr(34) & rs!FormName & Chr(34)

mycondition = "= CondFormatID = " & Chr(34) & rs!CondFormatID & Chr(34)
mycondition = "= CondFormatID = " & rs!CondFormatID

If Nz(rs!Cond_ForeColor, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Controls("Cond_ForeColor").FormatConditions(0).ForeColor = rs!Cond_ForeColor
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_ForeColor").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls("Cond_ForeColor").FormatConditions.Add(acExpression, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_ForeColor").ForeColor = rs!Cond_ForeColor
End If

If Nz(rs!Cond_FontUnderline, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Controls("Cond_FontUnderline").FormatConditions(0).FontUnderline = rs!Cond_FontUnderline
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_FontUnderline").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls("Cond_FontUnderline").FormatConditions.Add(acExpression, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_FontUnderline").FontUnderline = rs!Cond_FontUnderline
End If

If Nz(rs!Cond_BackColor, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Controls("Cond_BackColor").FormatConditions(0).BackColor = rs!Cond_BackColor
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_BackColor").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls("Cond_BackColor").FormatConditions.Add(acExpression, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls("Cond_BackColor").BackColor = rs!Cond_BackColor
End If

rs.MoveNext
Loop
End If

Forms("Conditionals").Refresh

 

[MarkK]

it is not working there. Do you see anything wrong here:

Not working? Maybe get more specific.

 

[Ranman256]

You shouldnt need ANY code to do this.
Why all the code?
Each form/subform should connect to a query. Zero code.

 

[BlueIshDan]

For my own eyes:

Dim objFrc As FormatCondition
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = DBEngine(0)(0)
GBL_Username = Environ("username")

strSQL = "Select * from CondFormat Where (UserName = '" & GBL_Username & "' OR Cond_Type = 'Global') AND ReportListID = " & ProjID
Forms("Conditionals").Form.RecordSource = strSQL

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

If Not rs.EOF Then
rs.MoveFirst


'Get the saved values for conditional formatting.
Do While Not rs.EOF
'Forms("Conditionals")("Conditionals_Sub").Control s("Cond_BackColor").FormatConditions.Delete
'Forms("Conditionals")("Conditionals_Sub").Control s("Cond_ForeColor").FormatConditions.Delete
'mycondition = "= Cond_FieldName =" & Chr(34) & rs!Cond_FieldName & Chr(34) & " AND UserName =" & Chr(34) & rs!UserName & Chr(34) & " AND FormName =" & Chr(34) & rs!FormName & Chr(34)

mycondition = "= CondFormatID = " & Chr(34) & rs!CondFormatID & Chr(34)
mycondition = "= CondFormatID = " & rs!CondFormatID

If Nz(rs!Cond_ForeColor, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Control s("Cond_ForeColor").FormatConditions(0).ForeColo r = rs!Cond_ForeColor
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_ForeColor").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_ForeColor").FormatConditions.Add(acExpressi on, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_ForeColor").ForeColor = rs!Cond_ForeColor
End If

If Nz(rs!Cond_FontUnderline, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Control s("Cond_FontUnderline").FormatConditions(0).FontUn derline = rs!Cond_FontUnderline
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_FontUnderline").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_FontUnderline").FormatConditions.Add(acExpr ession, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_FontUnderline").FontUnderline = rs!Cond_FontUnderline
End If

If Nz(rs!Cond_BackColor, "") <> "" Then
'Forms("Conditionals")("Conditionals_Sub").Control s("Cond_BackColor").FormatConditions(0).BackColo r = rs!Cond_BackColor
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_BackColor").FormatConditions.Delete
Set objFrc = Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_BackColor").FormatConditions.Add(acExpressi on, , mycondition)
Forms("Conditionals")("Conditionals_Sub").Controls ("Cond_BackColor").BackColor = rs!Cond_BackColor
End If

rs.MoveNext
Loop
End If

Forms("Conditionals").Refresh

 

[BlueIshDan]

I'm guessing your form name in your table isnt matching your form. Same with the controls. Maybe the first control or the form name? Just a wild guess with what information we've been provided.

 

[abenitez77]

Not working? Maybe get more specific.

The cells are not being filled with the color (backcolor).

 

[vbaInet]

Can you copy and paste your code again enclosing it in code tags, that makes it easy to read. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=240420