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;”
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
Private Sub Graph_Updated (Code As Integer)
Me.Text11=”Click here to activate graph”
‘hides the activate message
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).