Graph on Access 2003 form not updating till I manually click on another control

Hi Experts,

In my MS Access 2003 database I have a form with a control (cboCustomer - just used to select which customer details I want to view) and a MS Graph Chart 8 (graPurchasing). Basically when I select a customer the graph updates to show the purchasing that customer has done. The problem I have is that when I change customer the graph will only update if I click on the graph and then click back to any control on my form. I want the graph to update as soon as I change customer without having to click on the graph then back into the customer drop down or onto a button (basically any other control on the form). I have so far tried the following in the afterupdate of cboCustomer:

   Me.graPurchasing.Requery
   Me.graPurchasing.SetFocus
   Me.cboCustomer.SetFocus
   Me.Refresh
   Me.Repaint
   Me.Requery

I have also tried various SendKeys functions, but, nothing works. Even though I am replicating what I am actually doing manually to get it to refresh in code. Nothing seems to make a difference. Help with this is greatly appreciated.
Ian LeasonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
It is never fun
I have two graphs on a form
I have code in my Graphs Updated() events that pauses 2 seconds, tosses the focus to the graph, then to the other graph and then tosses the focus to a dummy control.

That simulates the user clicking and lingering slightly on each one.
It's not perfect, but 95% of the time the graphs display.
The pause is crtitical.  Without it, the code doesn't generate the desired effect
Private Sub gphJRPreviousWeek_Updated(Code As Integer)
On Error Resume Next

Dim Wait As Double
Wait = Timer
While Timer < Wait + 2
   DoEvents  'do nothing
Wend

Me.gphJRPreviousWeek.SetFocus
Me.gphJRYTD.SetFocus
Me.txtHolder.SetFocus
End Sub


Private Sub gphJRYTD_Updated(Code As Integer)
On Error Resume Next

Dim Wait As Double
Wait = Timer
While Timer < Wait + 2
   DoEvents  'do nothing
Wend
Me.gphJRYTD.SetFocus
Me.gphJRPreviousWeek.SetFocus
Me.txtHolder.SetFocus
End Sub

Open in new window

Ian LeasonAuthor Commented:
Thanks for your comment Nick, unfortunately in my DB that doesn't seem to make a difference.
Jeffrey CoachmanMIS LiasonCommented:
To be clear...
Changing the customer in the combobox, should change the Graph without using a Requery.

So First:
Post the code you are using
Second:
Are you running your code on the *AfterUpdate* event of the combobox? (this will trigger the Current event, and should refresh the chart)
Third:
Do you have both linking fields of the graph (Master and Child) set to CustomerID?

If so, then code like this works well for me in the sample db attached...
Private Sub cboCustomer_AfterUpdate()
    'Find the record that matches the combobox.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerID] = " & Str(Nz(Me![cboCustomer], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
   ' below line not needed
   ' Me.graPurchasing.Requery
End Sub

Private Sub Form_Current()
    'Sync the combobox to the current form record
    Me.cboCustomer = Me.CustomerID
End Sub

Open in new window


Finally, note that in large/complex recordsets the graph may take a while to update....

JeffCoachman
Access--EEQ28738114-SyncGrapg.mdb
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Nick67Commented:
The problem I have is that when I change customer the graph will only update if I click on the graph and then click back to any control on my form.

Agreed.

The trick is to build a code block that simulates that action.
You have to experiment, what works for me may not work exactly for you.
It took me a while to get it to work, and then to optimize it.

In broad strokes:
Set off the code that causes the graph to update
Pause to ensure that the update is complete
Switch focus to the graph
Pause to ensure that the graph realizes it is in focus
Switch the focus to a dummy control.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian LeasonAuthor Commented:
Jeff>> I am glad you included a sample database that I am assuming works perfectly at your end(?) when I use it I have the exact same problem that my graph form has and it will only update by manually clicking the graph and then clicking back into the customer drop down - that is very odd, and makes me wonder if there could there be an issue with my installed graph control, or some other part of the installation causing this.

Nick>> You are totally right I just think it comes down to finding the right combination that will work on my machine, unfortunately I haven't managed to find anything yet, though as you can see in my comment above (to Jeff) it appears to be a machine or installation issue.
Jeffrey CoachmanMIS LiasonCommented:
By all accounts it should work.

I do this all the time, ...but to be sure, I tested the sample fully.
Can you move this form, and recordsource, to another database, and test it there?

Are you running the Compact/Repair utility regularly?
Are you compiling your code?
Do you have all your Windows and Office updates installed?

If Nick's code works, then you can go with that, ...if you need to get this going ASAP.

But the basic code should work, and whenever you get the chance, you should investigate why it does not.

Jeff
Ian LeasonAuthor Commented:
You are totally right Jeff it should work, but, for some reason it just doesn't. The code I am using at the minute is simply in the after update of the customer drop down
   Me.graCustomer.SetFocus
   cboCustomer.SetFocus
   Me.Refresh
   Me.Repaint
I have tried every combination of code I can think of including Nicks, but nothing will get this to work. It identifies a problem at the machine level in my view though when your sample works in the same manner as my database, I have just tried moving the sample to another database and it doesn't.
In answer to your questions:
Are you running the Compact/Repair utility regularly? Yes
Are you compiling your code? Yes
Do you have all your Windows and Office updates installed? Yes

I am going to go over to a friends and try the DB on another machine just to see if there is an issue with my installation.
Jeffrey CoachmanMIS LiasonCommented:
bring my sample too, ...just to be sure...
Ian LeasonAuthor Commented:
Will definitely do that Jeff.
Ian LeasonAuthor Commented:
On my friends computer I had the exact same issue with the graphs in both databases. However this code worked a treat on that machine therefore I am accepting this comment as a solution.

Thank you both for your input on this.
Nick67Commented:
You're welcome.
I hope it works in production as well.
It's not perfect (+90%) but then end user knows what should happen, and supplies the click in the odd circumstance where it fails.

Makes a person crazy, though!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.