Solved

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

Posted on 2004-09-15
19
303 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Get the ms access version 4 34
identify all unused queries, forms and reports 10 29
Loop within Select Case 3 27
In or Between 2 0
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 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

13 Experts available now in Live!

Get 1:1 Help Now