PhilMartin2
asked on
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.Rows
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
Cancel=1
End Sub
Private Sub Graph_Updated (Code As Integer)
Me.Text11.SetFocus
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).
Phil
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.Rows
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
Cancel=1
End Sub
Private Sub Graph_Updated (Code As Integer)
Me.Text11.SetFocus
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).
Phil
Have a look here
https://www.experts-exchange.com/questions/28738114/Graph-on-Access-2003-form-not-updating-till-I-manually-click-on-another-control.html
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
https://www.experts-exchange.com/questions/28738114/Graph-on-Access-2003-form-not-updating-till-I-manually-click-on-another-control.html
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
ASKER
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.
Phil
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.
Phil
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.
Kelvin
As I said, it's many a year and I might be totally wrong here.
Kelvin
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.
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.
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 selected...so 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
Subform=Grades
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)
JeffCoachman
Database131.mdb
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 selected...so 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
Subform=Grades
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)
JeffCoachman
Database131.mdb
ASKER
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).
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).
ASKER
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...
Phil
Phil
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll give it a try today and let you know. Thanks
@boag2000
The link above was the last one of these type questions where the same issue occurred
@PhilMartin2
What version of Access exhibits the problem?
The link above was the last one of these type questions where the same issue occurred
@PhilMartin2
What version of Access exhibits the problem?
ASKER
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.
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.
Nick67
I suspect not.
Close the Q if you wish.
Nick67
ASKER
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.
Phil
Phil
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.
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.
You are accepting your own post as the solution, ...yet you stated that my post (and sample DB) helped identify the issue...
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)
;-)
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)
ASKER
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.
Phil
Hope I'm following the right protocol here.
Phil
Just remember, ...whenever in doubt, ...click the "Request Attention" link
...enjoy the weekend
...enjoy the weekend
In your code
Private Sub Form_Current()
Me!FrmChartSub!Graph1.Rows
End Sub
Can you use a Me!FrmChartSub!Graph1.Requ
Kelvin