Refreshing Subform Graphs--HELP!

[LakotaMan]

Hi all,
I am having trouble refreshing a series of graphs in my Access 2007 application. Here’s the story:

I have five graphs on a Main Menu form, they are in five subforms. The user opens a Complaint form to enter information that these 5 graphs track. I would like the graphs to update when this form is closed and the user returns to the main menu.

I am actually having 2 problems: when to do the refresh and what the syntax is to get it done. Here’s what I’ve tried:

    Me!frmYearlyTransportationStatsPieChart.Requery

On the Main form’s Got Focus and Activate events –neither worked, also tried this behind a command button’s click event (on the Main Menu form), which did not work.

and

    Forms!frmMainMenu!frmYearlyTransportationStatsPieChart.Requery

on closing the Complaints form –did not work.

Refreshing subform information has always been hard for me to keep straight. I have used some past posts in the forum successfully and thought I had this problem under control until I put charts in the subforms!
Any help you can give me will be greatly appreciated.
LM

 

[dhookom]

I would open the complaint form in acDialog mode and then include lines of code following the DoCmd.OpenForm like:

   Me.frmYearlyTransportationStatsPieChart.Form.ChartCtrlName.Requery

I think the actual chart controls need to be requeried rather than the subform.

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,
I am trying to implement your code, but get the following messsage:

Run time error "2118"
You must save the current field before you run the requery action.

The run breaks at this code:

    Me.YearlyTransportationStatsPieChart.Form.Graph1.Requery

I never named the graph object on the subform, so that's it's name. . .
Any ideas?

 

[dhookom]

Can you provide all of the code as well as the event that it runs in?

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Here is the click event to open the Complaint form. This button is on the Main Menu:

Private Sub cmdNewComplaint_Click()
    Me.lstReview.Visible = False
    Me.lstRespond.Visible = False
    Me.lstUnfinished.Visible = False
    Me.lstReadyToclose.Visible = False
    Me.lblListLabel.Visible = False
    
    strUserType = "New"
    
    DoCmd.OpenForm "frmComplaints", acDialog, , , acFormAdd
    Me.YearlyTransportationStatsPieChart.Form.Graph1.Requery    
End Sub

I am trying the code to refresh only one of the graphs until I can get this solved.

 

[dhookom]

The acDialog is in the wrong place. Use the intellisense to determine where it belongs or press F1 to check Help.

Duane
Hook'D on Access
MS Access MVP

 

[LakotaMan]

Duane,

Here's the amended line of code:

    DoCmd.OpenForm "frmComplaints", acNormal, , , acFormAdd, acDialog

Same message came up.
BTW, thank you for taking so much time to help me with this.

 

[PHV]

I'd try this:

...
Me.Dirty = False
DoCmd.OpenForm "frmComplaints", acNormal, , , acFormAdd, acDialog
Me.Dirty = False
Me!YearlyTransportationStatsPieChart.Form!Graph1.Requery
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[LakotaMan]

PHV,

I tried your code, got this message:

You entered an expression that has an invalid reference to the property Dirty.

Run stopped at the first 'Dirty' line.

 

[LakotaMan]

Duane & PHV,

Though I hate to give up trying to find a solution to this, I am tempted to put all the graphs on the Menu form itself. Would refreshing be less of a problem then?

 

[LakotaMan]

Duane & PHV,

I've tried what I last mentioned and everything is working fine. It's beyond me why none of the other references worked.
I've always thought Access subforms were the spawn of Satan, now I'm certain.
Thanks to both of you for your help.

LM