MS Access Form Chart - Click to show records

Hi, is it possible to click on a chart on a form (not in design mode) and go to the underlying records in a report.  If so, are there any VBA samples out there that would assist me?
Who is Participating?

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

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.

Jeffrey CoachmanMIS LiasonCommented:
Yes, this is easy if the recordsource is a table or a saved query.

On the click event of the chart, do something like this:

DoCmd.OpenTable "YourTable"
DoCmd.OpenQuery "YoiurQuery"

Jeffrey CoachmanMIS LiasonCommented:
But let's be clear...
You say:
is it possible to click on a chart on a form (not in design mode) and go to the underlying records in a report.

I presumed that you wanted to see the Form's records.

If you want to see the same data in a report, then create the report first.
Then use code like this to click the form chart and view the report:
    DoCmd.OpenReport "rptData", acViewPreview
Jeffrey CoachmanMIS LiasonCommented:
If the report's data chaned with each record, ...then you can use code like this to display the form with only the current record.
    DoCmd.OpenReport "rptData", acViewPreview, , "ID=" & Me.ID "ID" is the primary key number field.

So, to summarize, here are all three code snippets and a sample db:
''Open source table
'DoCmd.OpenTable "tbldata"

''Open source Query
'DoCmd.OpenQuery "Query1"

''Open report for all records
'DoCmd.OpenReport "rptData", acViewPreview

''Open report for current record only
'DoCmd.OpenReport "rptData", acViewPreview, , "ID=" & Me.ID

Open in new window can un-comment out each snippet (one at a time) to test them individually.


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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hamed NasrRetired IT ProfessionalCommented:
Try this, Assuming:
Form: chart - chart object name: Graph0
Report: chart

In form chart design mode, properties sheet, events, On click -Event Procedure

Private Sub Graph0_Click()
    DoCmd.OpenReport "chart", acViewPreview, , , , Graph0.RowSource
End Sub

Open in new window

For report Open event:
Private Sub Report_Open(Cancel As Integer)
       Me.RecordSource = Me.OpenArgs
End Sub

Open in new window

padrepio2Author Commented:
Thanks for the feedback, I won't be able to try this out for a few weeks.  I'll get back to you then.
Jeffrey CoachmanMIS LiasonCommented:
"a few weeks"?
...or did you mean "a few days"?

You will be prompt to act on all inactive Questions after 21 days...
padrepio2Author Commented:
Sincerest apologies for delay, been on holiday and have been extremely busy at work since.  Many thanks for feedback.
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.