Showing Changes in Updated Data

[Ddempsii]

Hello all,

Have been stuck on a situation that I am not sure is even possible, hoping for help. So I have a report made which shows the top 20 reps who have collected the most money daily as well as monthly. Now this report is a sub-report in a form. The form has a 30 second timer which will update all the data when equal to 0. Then the timer will restart back to 30. So basically its "live" data (every 30 seconds). I was wondering if it is possible to show some sort of color or image for the people that "pass" or "go up in rank". Assuming I would have to compare the data to the new data coming in some how. Maybe conditional formatting? Not sure if anyone has done this but help is very much appreciated.

Thank you

[ItsMe]

Sounds like you need to do some work in your Report Object while in Design View. If you have a Rank field in your report, it is likely you will not be able to use this variable for formatting. You might try using expressions in conditional formatting. The fact that you are using Report View in your Main Form might cause the Rank value to be available. But, I do not believe it will work if the value of Rank is determined after all of the records are retrieved.

I see your biggest issue as getting the Rank value on hand before the Report is rendered. So you would have to use a subquery. You might be able to use some VBA in a custom function and include the function in your query, as an alternative to a subquery.

Once you have the Rank value at the time of the OnLoad event of your Report, you should be able to use Conditional Formatting or VBA in the OnFormat event of the appropriate section(s).

[Ddempsii]

Thank you for the reply, everything does stay in order when the report loads already. I could add a rank to the query which isn't a problem, but where I get stuck is actually using the conditional formatting to show the changes on refresh. Do you have any ideas on what sort of expression is needed in this case?

[ItsMe]

...where I get stuck is actually using the conditional formatting to show the changes on refresh...

If you use the intrinsic Conditional Formatting tool, it should automatically refresh. Of course, in order for a subfor/subreport to stay in synch with its Master, you need to define the Link Master/Child properties of the subform/report.

An expression can be built using the Conditional Formatting Wizard and the dialog box provided. You may have to 'add' more than one expression. Alternatively, you can create logic using VBA. How to build code would depend on what it is you are trying to do and the structure of your data.

In general, some VBA might look like ...
If me.controlnameone.value > 12 then
me.controlnameoneTwo.backcolor = 255
end if

[CJ_London]

I think you need to populate your table with a 'previous rank' field which is updated through your timer event in some way, then your conditional formatting would be based on comparing 'current rank' with 'previous rank'

however on the basis that your current rank is a calculated value (sum of sales or whatever) you would need to save this to a temporary table to 'fix' it

alternatively you might be able to have a calculated value for previous value based on a timestamp

currentsales:sum(sales)
previoussales:sum(iif(timestamp<dateadd("n",-30,now()),sales,0))

and then have a separate ranking calculation

[ItsMe]

I think you need to populate your table with a 'previous rank' field which is updated through your timer event in some way, then your conditional formatting would be based on comparing 'current rank' with 'previous rank'
...

Hmmm, maybe a custom function that was called from a query and use that query as the Recordsource of the form?

[CJ_London]

Hmmm, maybe a custom function that was called from a query and use that query as the Recordsource of the form?

something like that - all depends in how the OP is updating at the moment. Also not sure what is meant by 'pass' - pass another salesperson in rank?

if the data is live then it is changing all the time - so you need to capture a position at a point in time to measure from to see if it is going up or down. I'm suggesting a table but could be an array.

[ItsMe]

...so you need to capture a position at a point in time to measure from...

Yup . . . .

[Ddempsii]

Hey everyone thank you again for the replies, going to revisit later today and let you know how its looking, and Yes "Pass" means passing in rank. Anything one Rep "passes" another in rank they should display something (Conditional formatting).

[Ddempsii]

Its been done! Thank you everyone for the different ideas. I ended up making a ranking query which ranked everyone's sales 1 min ago. Once that was done it was easy to conditional format everything to the difference between. Now on my report every min or so it will auto refresh and show the changes in data by comparison. Thank you again, this turned out awesome!

[orange]

This might be one of those cases to post your database and a description in the tutorials/samples or code snippets area.
It may be useful to someone else. And a clear description with a sample database is always a good start/reference for others.