A report with variable pointer in each record!

[silversun]

Hello Covid-19 fighters
In my report I want to create a graphical representation showing a pointer that points to a multi-color bar according to one of the fields in my report for each record. I realized my report takes the value in the first record for pointer and uses the same value for all of the records in entire report.
I have a function that can calculate and assign a value to Me.myPointer.Top property so that my pointer is supposed to move in different locations (top to bottom) in each record dynamically.
Am I expecting something out of scope of Reports in MS Access?

 

[The_Doc_Man]

One complication is that you must realize that every detail line is in its own section which repeats for each detail record. Me.myPointer surely exists in a section - but in which one? And if you are trying to move stuff vertically, the section holding that stuff must be tall enough to accomodate your pointer move. So far as I recall, sections are like stripes that are the full width of the report, so you can't easily refer to something in common outside of the sections - because there is nothing outside of the sections in a report. It's all stripes.

I suspect you might need to approach this goal with another method. I'm going to take a really wild guess here. I make NO CLAIM that this would work either, but I hold no hope for the approach you summarized.

IF you could make a report that is one tall section and put a sub-report control on it that is less than the width of the main report section, you could have a tabular list in the sub-report and could scroll through it, then have code in the sub-report that looks to the .Parent of the sub-form's control (which will be a report section) to find where that pointer should go. Then you could adjust it accordingly based on which record is currently in focus. But it would be a complex process on its best day.

 

[silversun]

One complication is that you must realize that every detail line is in its own section which repeats for each detail record. Me.myPointer surely exists in a section - but in which one? And if you are trying to move stuff vertically, the section holding that stuff must be tall enough to accomodate your pointer move. So far as I recall, sections are like stripes that are the full width of the report, so you can't easily refer to something in common outside of the sections - because there is nothing outside of the sections in a report. It's all stripes.

I suspect you might need to approach this goal with another method. I'm going to take a really wild guess here. I make NO CLAIM that this would work either, but I hold no hope for the approach you summarized.

IF you could make a report that is one tall section and put a sub-report control on it that is less than the width of the main report section, you could have a tabular list in the sub-report and could scroll through it, then have code in the sub-report that looks to the .Parent of the sub-form's control (which will be a report section) to find where that pointer should go. Then you could adjust it accordingly based on which record is currently in focus. But it would be a complex process on its best day.

I think I was not clear enough at the beginning. I am not going out of each section neither vertically nor horizontally.
I am within my first record and want to have my pointer showing for example 7th colored box.
In next record the value (Risk_Level) that was used to place the pointer on the colorful bar/boxes, about the middle area, now has a different value because the Risk_Level in second record (next row in underlying table) for example is 3.
I would like to have my second record to show the pointer in middle of the first half of the bar. Please refer to attached image:

Risk value is 789 in first record and 4320 in second record according to my original table. The pointer in second record is pointing the same color as in first record. The first on is correct and I was expecting in second record to see my pointer moves towards red area with higher risk value.
The reason (I am not quite sure) is when Access generates the report uses the first value of Risk_Level in all records although it changes record by record.
I hope this clarifies me.
BTW, I changed my colorful bar from vertical to horizontal to save space.
Thank you for your time teaching me something.

 

[MajP]

Tryi setting the color on the OnPaint event. This happens when each record is painted.

 

[Deleted member 147267]

So why not show the function code (please use code tags from the forum menu bar)? Perhaps that has something to do with your issue.

 

[The_Doc_Man]

Ah... Thank you for the picture. From that description, you don't want Me.myPointer.Top , you want Me.myPointer.LEFT

 

[arnelgp]

if you are running your Report on Report view, then
you may or may not find the solution you are looking
for since not all Events are available on this view.

if you're report is in Print preview you can add 9 more
arrows. each pointing to each color box.
you then need to add code to the Detail's Format Event.

see clients Report in Print Preview.

 

[Deleted member 147267]

BTW, I changed my colorful bar from vertical to horizontal to save space.

Doc Man, I think you missed that statement - or it doesn't mean what I think it does, which is that it's only horizontal in the post. In that case, it wouldn't be Left.

 

[The_Doc_Man]

Yep, missed that statement. But the bars in the picture were indeed going to need .LEFT

 

[silversun]

if you are running your Report on Report view, then
you may or may not find the solution you are looking
for since not all Events are available on this view.

if you're report is in Print preview you can add 9 more
arrows. each pointing to each color box.
you then need to add code to the Detail's Format Event.

see clients Report in Print Preview.

Hi arnelgp,
I saw your sample . I appreciate your effort helping me on this issue.
I spent time and couldn't figure out how to do it. Unfortunately I couldn't apply your method to my case statement function.
Could you please help me to apply your method using following function and case statement?
Here is my case statement:

Private Sub myBar(riskRate)
Select Case riskRate
    'pointer location 1
   Case 15570 To 100000
   Me.myArrow1.Left = 3250
   Me.myArrow1.Visible = True
 
        'pointer location 2
   Case 13840 To 15570
   Me.myArrow2.Left = 3250
   Me.myArrow2.Visible = True

        'pointer location 3   
   Case 12110 To 13839
   Me.myArrow3.Left = 3800
   Me.myArrow3.Visible = True

        'pointer location 4
   Case 10380 To 12109
   Me.myArrow4.Left = 4440
   Me.myArrow4.Visible = True

        'pointer location 5
   Case 8650 To 10379
   Me.myArrow5.Left = 5000
   Me.myArrow5.Visible = True

        'pointer location 6
   Case 6920 To 8649
   Me.myArrow6.Left = 5560
   Me.myArrow6.Visible = True

          'pointer location 7
   Case 5190 To 6919
   Me.myArrow7.Left = 6120
   Me.myArrow7.Visible = True

          'pointer location 8
   Case 3460 To 5189
   Me.myArrow8.Left = 6700
   Me.myArrow8.Visible = True

        'pointer location 9
   Case 1730 To 3459
   Me.myArrow9.Left = 7300
   Me.myArrow9.Visible = True

          'pointer location 10
   Case 0 To 1729
   Me.myArrow10.Left = 7900
   Me.myArrow10.Visible = True
    
End Select
End Sub