You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's Power Tip is contributed by Philippe Bonnardell, an Access developer and contributor to the French Access forum Developpez.net. He was inspired by the new Sparkline feature of Microsoft Excel, which enables you to create tiny graphs that summarize data across many cells. Philippe worked up some relatively simple VBA functions that let you add Sparkline-like graphs in Access reports.
The following illustrations show examples of charts that were created by using his code (the overlays show closeups of the graphs):
Figure 1: Line charts
Figure 2: Bar charts
Figure 3: On/Off charts
If you are interested in seeing how it works, you can download Philippe's sample database:
IMPORTANT: To view the charts, you must open the reports in Print Preview. In the Navigation Pane, right click the report that you want to view, and then click Print Preview.
For each report section that contains a graph, there is a label control in which the graph is drawn. In the Format event for the report section, there is a call to one of the functions in the module ReportSparks. For example, to create the line charts shown in Figure 1, the Format event of the Detail section contains this function call:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Call SparkLine(Me.Spark, Me.Jan.Value, Me.Feb.Value, Me.Mar.Value, Me.Apr.Value, _ Me.May.Value, Me.Jun.Value, Me.Jul.Value, Me.Aug.Value, _ Me.Sep.Value, Me.Oct.Value, Me.Nov.Value, Me.Dec.Value)End Sub
In this example, the label control that contains the chart is named Spark, and is supplied as the first argument. The remaining arguments supply the actual data values, and the functions do the rest. You can copy the module to your own database, add the labels and the function calls, and use them yourself.
Thanks for the tip, Philippe!
I tried to look at all three reports in both versions. Unfortunately I cannot open them in Print Preview, because an error arises during the communication between MS Access application and OLE server or ActiveX control. Could anybody help me?
Thank you for your reply.
We haven't seen that problem after running it on a few machines, but a problem like that could be caused by a missing reference. Try the following:
1. Press Alt+F11 to open the Visual Basic Editor.
2. On the Debug menu, click Compile SparkLines. Access will tell you if there are any missing references.
3. To add a reference, on the Tools menu, click References, and then select the one(s) you want to add, and then repeat Step 2.
4. Close the Visual Basic Editor.
5. On the Database Tools tab, click Compact and Repair Database.
6. Try the reports again.
Hope this helps!
Hi every one,
I just tried the .mdb version on Access 2007 and it worked very well :) thanks to all for the tip.
I have tried your proposal unfortunately with the same result. Actually I think it is not necessary to hold a reference to OLE Automation and MS VB for Applications Extensibility 5.3 libraries. The code is compiled properly without them too. I had to create new database and copy all the tables and module into it. If I copied the reports too, the same error arised. So the solution was to create three new reports from scratch.
Thanks for the tip.
Great--thanks for the comment MehdiH, and also thanks to David Z, for the workaround.