How can I show graph in a sub-form without having to double-click to activate it?

Some years ago I developed an Access database for my  school which teachers use to record the progress of pupils (with profound disabilities).

Recently, I have added a form called ‘FrmChartMain’ with a sub-form called ‘FrmChartSub’ which contains a graph called ‘Graph1’ .

The graph on the sub-form shows the progress of each pupil over time – the x axis being school years and the y axis the number of items (on a developmental assessment) achieved by that pupil in each year in school.

Pupil data (on the main form) is drawn from the query: ‘Pupil Q’ whilst developmental data is from the query: ‘Routes Q’.   The forms are synchronised using the field ‘PupilID’.  

An SQL statement in the ‘On current’ event of the main form loads the graph with data based on the PupilID shown in the sub-form:

Private Sub Form_Current()
Me!FrmChartSub!Graph1.Rowsource = “SELECT [Yr Group],[Achieved] FROM Routes Q WHERE PupilID=FrmChartSub.PupilID  ORDER BY  Date;”
End Sub

I simply want the chart  to update when the user moves between pupil records (using the navigation buttons on the main form).  I have tried everything I can think of but cannot get it to do so without first having to do one of the following:

- Double-click on the graph (which brings up the underlying chart – which I don’t want) –then it will show the graph correctly for all subsequent records
- Having disabled the double-click event for the graph (see below), you have to click in a control on the main and sub form in turn (again it will then show the graph correctly for all subsequent records)

The nearest I can get to what I want is an very inelegant work-around – a text box (‘Text11’) which tells the user to click in it, after which the message is made invisible (apart from a flashing carat).  It is then still necessary to move to another record but from there on the graph updates correctly for each subsequent record.  See code below:

Private Sub Graph1_DblClick(Cancel as Integer)
‘ disables the double-click
End Sub

Private Sub Graph_Updated (Code As Integer)
Me.Text11=”Click here to activate graph”
End Sub

Private SubText11_Click()
‘hides the activate message
Me.Text11.BackColor= -2147483633
Me.Text11.ForeColor= -2147483633
End Sub

I’ve tried requerying the graph and setting the focus elsewhere on both forms using VBA but to no avail. I would be really grateful if anyone can suggest changes which would make the graph immediately available on opening the form and on moving between records. (I'm using Access 2003).

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
I've not struck that issue, as I usually set the Parent/Child properties of the subform to the PupilID.

In your code
Private Sub Form_Current()
 Me!FrmChartSub!Graph1.Rowsource = “SELECT [Yr Group],[Achieved] FROM Routes Q WHERE PupilID=FrmChartSub.PupilID  ORDER BY  Date;”
 End Sub

Can you use a  Me!FrmChartSub!Graph1.Requery (or refresh)? I'm not sure if requery can be used there. Would come just before the End Sub.

Have a look here

Ultimately, delay is essential, as is emulating what the user actually has to do in the UI
Which is shift the focus to the graph--wait for the load/update--and then shift the focus away again.
I haven't been able to absolutely perfect this, and each situation is a little different, but the technique is the same: emulate what the user would need to do via SetFocus with enough delay built in that it works
PhilMartin2Author Commented:
Thanks for your coment Kelvin

I've tried .Requery in there and also in the sub-form events but it doesn't seem to make a difference.

Incidentally, the parent/child properties of the sub-form are set to PupilID so they synchronise whilst the SQL statement tells the graph what info to use based on the (child) PupilID value.

IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Kelvin SparksCommented:
OK, I must admit that I've only used graphs in reports and the parent/child property has managed the display perfectly. From memory (as I haven't done this in the last 7 years), I used to bind the graphs to the form(report), rather than using unbound graph objects. I think that this helps it somewhat.

As I said, it's many a year and I might be totally wrong here.

I think the problem is that the Graph objects aren't really like textboxes, combos et al.
Graph.Exe is actually a separate program
You can see it in Task Manager when you have a form with a graph on it open
One instance for each graph.

So I think, unless the graph gets the focus long enough for Windows to hit its thread, it doesn't update.
Hence the essentialness of delay in the UI emulation code you craft.
Jeffrey CoachmanMIS LiasonCommented:
You should not need to do anything on the current event.

The recordsource for the chart should be set already, ...the chart should refresh automatically, if the Chart's master/child links are set to the SudentID
You did not state how you created the chart, ...or what options you it is not clear why yours is not working...

Here is a sample of a mainform and a  subform with a chart.
Main form=Students
Chart shows grades per semester (progress of pupils)
No refreshing/requerying is needed...

If this is not exactly as you need, ...then it is sometimes better to post a small sample of your DB.
My guess is that a similar adaptation can be arrived at...
(For example, placing the chart on the main form instead of the subform might simplify things)

Jeffrey CoachmanMIS LiasonCommented:
PhilMartin2Author Commented:
Nick67 Thanks, I'm trying your solution - although can't get it to work as yet...

Jeff, when I open the form in your sample database, I get a similar issue to what I've had on mine.  As I move from record to record, the graph appears momentarily (while the mouse button is depressed) but disappears when no button is depressed.   (Strange).
PhilMartin2Author Commented:
And in fact the graph doesn't appear at all unless I click in it.  Double-clicking in it brings up the underlying chart and then it all works correctly...

Jeffrey CoachmanMIS LiasonCommented:
odd, there was a post a short while ago with the same symptom,...
Turns out it was an issue with the persons machine.

The fix was to force a requery.

But can you confirm that my sample works on another computer?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhilMartin2Author Commented:
I'll give it a try today and let you know. Thanks
The link above was the last one of these type questions where the same issue occurred

What version of Access exhibits the problem?
PhilMartin2Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for PhilMartin2's comment #a41273424

for the following reason:

sample db provided and suggestion to use another pc enabled me to show my code was not at fault.
So did that fix the problem on the original machine?
I suspect not.

Close the Q if you wish.

PhilMartin2Author Commented:
Thanks to everyone for taking the time to help problem-solve this issue.  Jeffrey's db worked perfectly today on another computer running Access 2010 - as did my own original code without any workarounds, delays, setfocus or requery actions.  Therefore I conclude that this must have been a problem either with Access 2003 or my laptop.

Kelvin SparksCommented:
Pleased that you have identified the issue.
The issue occurs in A2003.
If you are inter-operating with both versions, you will need to take steps to remedy the issue in down-level versions of Access, because it will always occur there.
Jeffrey CoachmanMIS LiasonCommented:
You are accepting your own post as the solution, ...yet you stated that my post (and sample DB) helped identify the issue...
Jeffrey CoachmanMIS LiasonCommented:
I know that it can sometimes be confusing on how to close a question.
So my intention here is just to make this Q more valuable as a PAQ.

Ultimately the OP should "accept" a post(s) that actually lead to a solution. (Directly or indirectly)
PhilMartin2Author Commented:
Really sorry I didn't close correctly and for the delay - when picking up the notifications on my (android) tablet I wasn't able to view the latest comments) .  Nick, thanks for your clarification.  That being the case, I think the fairest result would be to accept multiple solutions.  Again, thanks everyone.

Hope I'm following the right protocol here.

Jeffrey CoachmanMIS LiasonCommented:
Just remember, ...whenever in doubt, the "Request Attention" link
...enjoy the weekend
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.