CODEWITHSUNDEEP
ms-accessvba
Mark Pelletier
Mark Pelletier

Reputation: 1359

MS Access: Conditional Formatting in Dynamically Created Datasheet

I have a datasheet in a subform, created dynamically at runtime using a querydef as the subform's SourceObject (this part is working fine and the datasheet is created). I do not know the actual list of datasheet columns in advance, but Column "Role" is always present:

Dim db As Database
Dim qd As QueryDef
Dim strSQL As String
Dim rs As DAO.Recordset

Set qd = CurrentDb.QueryDefs("q_XO")
Set db = CurrentDb

qd.SQL = "SELECT * FROM XO_Table;"

Forms!mainform.[XO_Table subform].SourceObject = ""
Forms!mainform.[XO_Table subform].SourceObject = "query.q_XO"

Next, I'd like to add Conditional Formatting to simply set a column's BackColor when the cell's value is not NULL (a single condition).

Problem: I cannot see the datasheet in design view (created at runtime) to set conditions via the typical MS Access interface. I need to do this using VBA, I suspect.

The following is my current unsuccessfull approach. Error: "Invalid Reference to the property FormatConditions" (err: 2455), on the Delete line below:

Dim objFrc As FormatCondition

Forms![mainform]![XO_Table subform].Form!Role.FormatConditions.Delete
Set objFrc = Forms![mainform]![XO_Table subform].Form!Role.FormatConditions.Add(acFieldValue, acGreaterThan, "")

Am I heading down the right path on this?

Thanks!

Upvotes: 0

Views: 2032

Answers (1)

June7
June7

Reputation: 21379

If you want to use Conditional Formatting, SourceObject of subform/subreport container cannot be a query or table, must be form or report because only textboxes and comboboxes have Conditional Formatting.

Empty string is not the same as Null.

Also, cannot compare anything to Null. Null means there is nothing to compare to. Review http://allenbrowne.com/casu-12.html

Here is code from my procedure that works. Note that ctrGames is name of container control which is different from the form object held by the container.

Sub test12()
Dim objFC As FormatCondition
Forms!Main.ctrGames.Form.StartTime.FormatConditions.Delete
Set objFC = Forms!Main.ctrGames.Form.StartTime.FormatConditions.Add(acExpression, , "[StartTime] Is Not Null")
Forms!Main.ctrGames.Form.StartTime.FormatConditions(0).BackColor = vbYellow
End Sub

So suggest your code:

Set objFrc = Forms![mainform]![XO_Table subform].Form.Role.FormatConditions.Add(acExpression, , "[Role] Is Not Null")

More info at https://blogs.msdn.microsoft.com/frice/2004/06/08/adding-additional-conditional-formatting-in-access-with-vba/

Upvotes: 1

Related Questions