Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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

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
jesselavaca
Asked:
jesselavaca
  • 10
  • 9
1 Solution
 
shanesuebsahakarnCommented:
If you remove the stLinkCriteria part, does the chart update when you open it via the button?
0
 
jesselavacaAuthor Commented:
I changed it to just:

DoCmd.OpenForm stDocName

and the chart didn't update...

-Jess
0
 
shanesuebsahakarnCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
Requery didn't do it...

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

-Jess
0
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
OK, post back when you get back and we'll work through it from there.
0
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
jesselavacaAuthor Commented:
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
 
shanesuebsahakarnCommented:
No probs, glad we got there in the end :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now