Solved

Access 2000: Updating SubForm Charts...need new event?

Posted on 2004-09-15
19
304 Views
Last Modified: 2012-05-05
Experts,

This has been bugging me -

I made a form that displays all the employees, and a subform displays a chart that graphs where each employee's expenses are categorized.  To get the chart to update I had to set the Chart Query/DataSource to:

--------
SELECT Category, Sum([TransactionsQuery].Cost) AS SumOfCost, [TranactionsQuery].EmployeeID
FROM [TransactionsQuery]
WHERE ((([TransactionsQuery].EmployeeID)=[Forms]![frmEmployeeSpendCharts].[EmployeeID]))
GROUP BY [TransactionsQuery].Category, [TransactionsQuery].EmployeeID;
--------

The Where Clause was my favorite part...

The next part to figure out was how to get the chart to update as I cycle through employees - so I searched on here and found that I could use the On Current Event for the Form, and it worked.  (love this site)

----
Private Sub Form_Current()
Forms(frmEmployeeSpendCharts).Refresh
End Sub
-------

But...

I put a button on the main form to open the frmEmployeeSpendCharts, and when the form updates I can cycle through the employees, but the chart does not update anymore.  Is this because the form opens Modal or something?  Is there another event I can use to make the chart update again again?  When I open the form just by double clicking on it in the Access window, it works fine....I think the problem lies in how the main form handles it:

-----------------------
Private Sub cmdOpenEmployeeSpendCharts_Click()
On Error GoTo Err_cmdOpenEmployeeSpendCharts_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmEmployeeSpendCharts"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenEmployeeSpendCharts_Click:
    Exit Sub
Err_cmdOpenEmployeeSpendCharts_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenEmployeeSpendCharts_Click
End Sub
-----------------


-Jess

PS - I'm sure you'll need more info from me, so just ask and I'll respond...
0
Comment
Question by:jesselavaca
  • 10
  • 9
19 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12067741
If you remove the stLinkCriteria part, does the chart update when you open it via the button?
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12067853
I changed it to just:

DoCmd.OpenForm stDocName

and the chart didn't update...

-Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12067969
Oh hold on a second, let me get this straight - you've got a main form based on the employees table. In this form is a subform containing your chart. Your code to refresh the subform is in the OnCurrent event of the *main* form, is that right?
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12068069
That's pretty much correct...

-Main Form: has buttons to link to all the other forms
-EmployeeSpend Form: shows the employees
-sfrmEmployeeSpend: has the chart that updates for each employee

-Jess

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068095
OK, to update the subform, try changing your refresh line to this:

Me!MySubformControl.Form.Refresh

MySubformControl is the name of the *control* that the chart subform is held in - design the main form, click on the subform, click View-Properties and check the Name property on the Other page. This might (but probably won't) be the same as the name of the subform that it contains.
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12068154
Thanks for the specific directions...but that's still not it yet.

Me![qryID-Name-Cost-KSB1Charts subform].Form.Refresh

is under the
"Private Sub Form_Current()" piece of the EmployeeSpendForm

Another thought: would it matter if I declared this as Public?

-Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068235
No, it shouldn't make a difference - the Current event of the main form should be fired whenever you move to a new record there.  Is the chart still not refreshing? Try changing the Refresh to Requery, and see if that makes a difference.

Also, is the subform linked to the main form by any child/master fields?
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12068326
Requery didn't do it...

and yes, there is a child/master link - EmployeeID.

-Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068370
I noticed this on your query:

[Forms]![frmEmployeeSpendCharts].[EmployeeID]

Try changing it to:
[Forms]![EmployeeSpendForm]![qryID-Name-Cost-KSB1Charts subform].Form![EmployeeID]

The reason is that, if frmEmployeeSpendCharts is open as a subform, you can't reference it directly - it isn't part of the Forms collection, so you need to refer to it indirectly via its parent/subform control.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:jesselavaca
ID: 12068552
Things got more weird.

So I realized the chart did not have a child/master link...another subform displaying a datasheet of the values did.  I added it, and now when I load the form from the menu it works and updates - but only once.  Odd.

However, when I changed the query, it gave me a crazy error..."Object yadayada error", I CTRL+BREAK'ed it, Debugged, and it was the Requery statement, so I changed it to Refresh and the problem went away.

So now the question is, why does it only update itself once?

-Jess

PS - I leave for the day in another 10 minutes, won't be back until Friday morning...
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12068639
OK, post back when you get back and we'll work through it from there.
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12084733
and I'm back!

So where I left off...the main form links to the charts, it loads the data and charts the first employee's spend, then I advance one record and the chart updates again...but then it no longer updates no matter which direction I go.

Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12085403
I'm not sure why it only updates once - could you try modifying the chart's query so that it gets its criteria directly from the main form (in other words, include a criteria in its query on the EmployeeID field, something like = Forms!MyMainForm!txtEmployeeID), and then remove the master/child links. Does that make a difference?

Before you do that though, just a thought - try requerying the chart directly, something like:
Me![qryID-Name-Cost-KSB1Charts subform].Form!MyChartControl.Requery
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12088413
So I made the latter change first - the Requery...it didn't change anything so far as I could tell...

Then I removed the master link and now the updating isn't happening at all (not even that first one I was getting).  

Weird,
Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12088431
Did you include a requery when the master/child link was removed? You must include the criteria in the query as well for it to (hopefully) work.
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12088529
I did - the Chart SQL is now:

-----------
SELECT [qryID-Name-Cost-KSB1Charts].SpecificName, Sum([qryID-Name-Cost-KSB1Charts].Cost) AS SumOfCost, [qryID-Name-Cost-KSB1Charts].EmployeeID
FROM [qryID-Name-Cost-KSB1Charts]
WHERE ((([qryID-Name-Cost-KSB1Charts].EmployeeID)=[Forms]![frmEmployeeSpendCharts].[EmployeeID]))
GROUP BY [qryID-Name-Cost-KSB1Charts].SpecificName, [qryID-Name-Cost-KSB1Charts].EmployeeID;
------------

and the Event OnCurrent on the main form is:

Me![qryID-Name-Cost-KSB1Charts subform].Form.Requery


More thoughts?

Jess
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 500 total points
ID: 12088573
This is really a rather long shot now, but try this in the main form's OnCurrent event:

Me![qryID-Name-Cost-KSB1Charts subform].Form!MyChartObject.RowSource=Me![qryID-Name-Cost-KSB1Charts subform].Form!MyChartObject.RowSource

Change MyChartObject to the name of the chart control. If this doesn't work, I'm really rather stumped. There are other people here who have done more work with the chart object than I have though, I'll see if I can get someone in to this Q to take a look.
0
 
LVL 1

Author Comment

by:jesselavaca
ID: 12088678
Well, I'll be damned....

It worked.

Congrats on another obscure question answered...and it's a shame they cap these at 500 points.

-Jess
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12088735
No probs, glad we got there in the end :)
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now