VBA to conditional format report controls

[hehrerh]

I have a report that contains about 60 date controls (GradingPlanApprved, WetlandStudyComplete, etc)



In the source table, each date field is accompanied by a corresponding 'status' field. The 'status' field has the same name as the date field, but with 'Status' attached to the end.

e.g.

GradingPlanApproved
GradingPlanApprovedStatus

WetlandStudyComplete
WetlandStudyCompleteStatus
I want to apply conditional formatting to each date field on the report based on the values of the corresponding status fields.

Right now, I am applying the below conditions to each individual control using the conditional formatting wizard. I am using a keyboard/mouse macro which makes swapping out the status field and applying the conditions much faster, but there must be some VBA I can apply instead.

Here are the conditions I am currently applying to each field:

[WetlandStudyCompleteStatus]="Estimate" And  DateValue(Date())+14>=DateValue([WetlandStudyCompleteStatus])

[WetlandStudyCompleteStatus]="Estimate"

[WetlandStudyCompleteStatus]="Action Item" And DateValue(Date())+14>=DateValue([WetlandStudyCompleteStatus])

[WetlandStudyCompleteStatus]="Action Item"

[WetlandStudyCompleteStatus]="Complete"

[WetlandStudyCompleteStatus]="N/A"

Here is the conditional formatting attached to each condition:



There must be a way for each control to reference its control source, attach 'Status' to the end, and use that to apply the formatting. Hopefully one of you code junkies can figure this one out!

Thanks,
- Howie

[June7]

Sorry, I don't understand the issue. What do you mean by 'swapping? A CF rule can be an expression that references multiple fields. Why would you use DateValue function on a text field? Or if that is a date field then how can it be = "Estimate" or any other text? Are you referencing wrong field name in DateValue?

[CJ_London]

you can use vba to apply conditional formatting - see this link

https://support.microsoft.com/en-us/...tings-to-all-f

be aware with that amount of conditional formatting, it is likely your report will be extremely slow to execute.

I find your coding difficult to understand. For example

[WetlandStudyCompleteStatus]="Estimate" And DateValue(Date())+14>=DateValue([WetlandStudyCompleteStatus])

-WetlandStudyCompleteStatus as advised by June7 cannot equal 'Estimate' and be a date at the same time. I suspect this is a typo, which means we are not looking at your actual code.

-the Date function returns a date - using the datevalue function adds nothing

how are you displaying 60 (120?) controls - why not use a datasheet or continuous report? Implication is your table is not normalised which makes it difficult to suggest a solution.

There must be a way for each control to reference its control source, attach 'Status' to the end

Again, not clear what you mean - if you are using vba, you would simply add 'Status' to the end of the control name you are formatting


if you mean pull back a value to use in conditional formatting perhaps something like this in a general module

function getStatus(frm as form, ctl as control) as string

    getStatus=frm(ctl.name & "Status")

end function

and would be called

getStatus([FORM], [WetlandStudyComplete])

however no idea if it would work for reports or in conditional formatting

[hehrerh]

I will correct my code when I get back to work. The datevalue function should call WetlandStudyComplete, a date field. The text fields are the status fields - WetlandsStudyCompleteStatus.

Example data is
WetlandsStudyComplete: 9-18-18
WetlandsStudyCompleteStatus: Estimate

I set up the datavase like this because this table is a task management, checklist table. The same fields apply to every job. The reports are easier to write this way, as opposed to working with data structured in a table maybe like this:

Job: phase 1, phase 1
Task: wetlands study, grading complete
Date: 9-18-18, 9-28-18
Status: estimate, complete

...or the same structure with foreign keys for task and status etc.

The problem I see with normalizing the data is that my report then becomes one giant list that is too long for the compact, horizontal form I am trying to achieve. Again pics will come tomorrow. Perhaps I should normalize the structure and use subreports to make the final repodt more horizontal?

Sorry if this reads poorly. Stream of consciousness plus on my phone. Thank you for the replies so far.

[CJ_London]

from your description, 60 columns does not sound compact - means each column can't be more than about 8mm wide.

More confused - this

working with data structured in a table maybe like this:

Job: phase 1, phase 1
Task: wetlands study, grading complete
Date: 9-18-18, 9-28-18
Status: estimate, complete

is how your initial description sounds (i.e. denormalised) - so what is different?

Perhaps I should normalize the structure and use subreports to make the final repodt more horizontal?

So cannot answer since I have no idea of your data structure.

[hehrerh]

Please look at the attached pics - apologies for not using real tables and reports, but I'm not at work so this might help.

I believe the structure is abnormal, but I don't know how to generate the report in the attached picture from the normalized database structure (which I have proposed in the db structure picture)

Also - when inputting dates and statuses in a form, it's much easier to work off of a text control box and a combo box for each tracked item than it is to scroll through and update the tasks in a table-style sub form.

Let me know if these pics help or if you require more - I know it's frustrating to teach/explain things to someone who knows a lot less than you do, so I thank you for bearing with me.

Thanks!

Current vs. normalized table structures



Example/rough structure of the report (actual report includes about 9 columns and 6 rows of data per project)

[June7]

It is a balancing act between normalization and ease of data entry/output. Normalize until it hurts, denormalize until it works.

Manipulating data from normalized to non-normalized can be challenging but it is possible. I have a db where I have to manipulate data both ways.

That said, I still don't understand issue with CF.

[hehrerh]

The issue is that I have to go to each individual cell and apply formatting through the wizard:

[WetlandStudyCompleteStatus]="Estimate" And  DateValue(Date())+14>=DateValue([WetlandStudyComplete])

[WetlandStudyCompleteStatus]="Estimate"

[WetlandStudyCompleteStatus]="Action Item" And DateValue(Date())+14>=DateValue([WetlandStudyComplete])

[WetlandStudyCompleteStatus]="Action Item"

[WetlandStudyCompleteStatus]="Complete"

[WetlandStudyCompleteStatus]="N/A"

I want to come up with VBA code that does this formatting automatically when the report is opened, instead of applying conditional formatting to each individual control.

I don't know how to write the code, but the logic would look like this:

for each control on report x...

get control's data name (WetlandStudyComplete), save as a variable (c)

Add 'Status' to variable c (WetlandStudyCompleteStatus), save as variable (d)

use a dlookup maybe? point to the source table, find this project's WetlandStudyCompleteStatus value (complete), save as variable (s)

iif s = "Action Item" AND c <= 14 days from now, [code that applies certain formats control accordingly]
iif s = "Action Item", [code that applies certain formats control accordingly]
iif s = "Estimate" AND c <= 14 days from now, [code that applies certain formats control accordingly]
iif s = "Estimate", [code that applies certain formats control accordingly]
iif s = "Current", [code that applies certain formats control accordingly]
iif s = "N/A", [code that applies certain formats control accordingly]

next control

Any VBA experts that can help write this?

Thanks!

[June7]

Well, it's only a one-time effort.

I have seen code that can set CF rules but never used. I see Ajax has provided link for that. It was useful back when only 3 rules were allowed for a control, now can have 50. Code in detail Format event can directly set properties of controls, however, Format event does not trigger for ReportView.

Why would you need DLookup if data is in report RecordSource?

[CJ_London]

Also - when inputting dates and statuses in a form, it's much easier to work off of a text control box and a combo box for each tracked item than it is to scroll through and update the tasks in a table-style sub form

you have taken an excel principle of combining presentation and data entry into one, leading to a denormalised structure which makes it difficult to do anything with.

actual report includes about 9 columns

again you need to be clear in your terminology - I see 11 columns in your example. OK - so first 'column' consists of a description and date. But what about the other columns in your example? a description and two dates. From your initial post these two date columns would need to be named the same.

Do the description/dates apply to all projects or only some (your examples implies the former)? Is there a logic to the 'column' groupings?

moving to your normalised structure, that looks to be the sort of structure it should be (subject to the sorts of questions above). But suggest for tblTasks - if intended to appear in a particular column - include some sort of column identifier (phase?)

Once normalised you can easily design forms and reports - using subforms for each column. You only need one subform object, placed on your form/report multiple times and using the linkchild/master properties to show the relevant data.

[hehrerh]

you have taken an excel principle of combining presentation and data entry into one, leading to a denormalised structure which makes it difficult to do anything with.

again you need to be clear in your terminology - I see 11 columns in your example. OK - so first 'column' consists of a description and date. But what about the other columns in your example? a description and two dates. From your initial post these two date columns would need to be named the same.

Do the description/dates apply to all projects or only some (your examples implies the former)? Is there a logic to the 'column' groupings?

yes - it should be grouped as shown in the attached database/image

moving to your normalised structure, that looks to be the sort of structure it should be (subject to the sorts of questions above). But suggest for tblTasks - if intended to appear in a particular column - include some sort of column identifier (phase?)

Once normalised you can easily design forms and reports - using subforms for each column. You only need one subform object, placed on your form/report multiple times and using the linkchild/master properties to show the relevant data.

I've attached the database.

The report in-question is rpt_PT_dev.

I am ALL FOR moving to a normalized structure. The way I THINK this should work would result in (20????) sub-reports???

Do you think it would be best to move to a normalized structure, then somehow use crosstab queries to massage the normalized data into a query that looks like the current source table?

If you want to provide an example database or an edit to mine, that would be awesome.

[June7]

A CROSSTAB might not be suitable. Sometimes VBA writing data to a 'temp' table is called for. Review https://www.accessforums.net/showthread.php?t=21972

[CJ_London]

this is suffering from 'mission creep'. Each time the situation becomes more complex. I don't think a crosstab would work for this case because sometimes you have one date column, sometimes two, three or four. Plus what looks like additional fields which look like they are 'two columns' wide and sometimes multiple rows deep. Looking at the database, some fields are dates, some are volumes.

So, although I strongly believe in maintaining a normalised structure, I don't have the time to invest in defining what that structure should look like in this case. My advice is to stick with what you have, difficult as it is to complete, and use the earlier recommendations around automating conditional formatting or just do it manually. It is a one time exercise after all.

[hehrerh]

this is suffering from 'mission creep'. Each time the situation becomes more complex. I don't think a crosstab would work for this case because sometimes you have one date column, sometimes two, three or four. Plus what looks like additional fields which look like they are 'two columns' wide and sometimes multiple rows deep. Looking at the database, some fields are dates, some are volumes.

So, although I strongly believe in maintaining a normalised structure, I don't have the time to invest in defining what that structure should look like in this case. My advice is to stick with what you have, difficult as it is to complete, and use the earlier recommendations around automating conditional formatting or just do it manually. It is a one time exercise after all.

Okay great. However, I still don't know how to code the conditional formatting VBA requested in my earlier posts. Help?

[June7]

In Detail section Format event set control properties. Like:

Me.controlname.FontBold = <your criteria that evaluates to true/false>

So if you have 60 controls and you want to manage 4 property settings - well you do the math on how many lines of code to write.