Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening dBase Files Directly

In the earlier Article Opening External Data Sources we have learned how to open another Microsoft Access Database and work with its Tables using Code. I have made a revision of the Code given there to display the Database Names loaded in WorkSpace(0) also on top of the list of Employee Names in the MsgBox. The Revised Code is given below. You may copy and replace the earlier Code and try them out.

Public Sub OpenSecondDatabase()
Dim wsp As Workspace, db As Database
Dim rst As Recordset, msg As String, x As Integer
Dim dbcount As Integer

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("c:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb")
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)

dbcount = wsp.Databases.Count - 1

msg = ""
For x = 0 To dbcount
 msg = msg & "Database(" & x + 1 & ") " & Dir(wsp.Databases(x).Name) & vbCr
Next
msg = msg & vbCr
With rst
x = 1
Do While x < 6 And Not .EOF
    msg = msg & ![LastName] & vbCr
   .MoveNext
   x = x + 1
Loop
   .Close
End With
MsgBox msg

Set rst = Nothing
Set db = Nothing
Set wsp = Nothing
End Sub

The statement wsp.Databases(x).Name gives the full Path Name of the File and I have enveloped it in the Dir() (Directory Function) to extract the Database Name alone to make it shorter in the MsgBox display. The Dir() Function checks for the presence of the Database in its specified Folder and if found returns the File Name alone.

Opening dBase File is comparatively a simple operation. Create an SQL string with a reference to the dBase Database Folder, the Table Name and the dBase Version (dbase III, IV or 5.0) of the Table and open the Recordset directly. The sample SQL String is given below:

strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];"

If you don't have a dBase file on your Machine to try this out you can Export one of your own Microsoft Access Tables to dBase III, IV or 5.0 Versions.

I have used the exported Employees Table from NorthWind.mdb sample database for our example. If you would like to try the Code given below without change you may Export the Employees Table from Northwind.mdb sample database. If you are not sure, where you can find this file, visit the Page Saving Data on Forms Not in Table, for location references.

  1. Create a Folder on your Disk C:\MydBase.
  2. Open the Northwind.mdb database.
  3. Select the Employees Table.
  4. Select Export from File Menu.
  5. Select dBase III or dBase IV or dBase 5 in the Save as Type Control in the Common Dialog Box.
  6. Browse to the Folder C:\MydBase.
  7. Type the File Name Employee in the File Name Control and Click Export.

Note: dBase Application File Names uses only 8 characters and 3 characters for File Name Extensions. When you Export the Employees Table it will shorten the name to 8 characters and saves as Employee.dbf. The exported Table's Field Names also will be truncated after the 10th character, if they are longer.

When the Employees Table Exported in dBase format, several files are created in the output Folder depending on the Version of dBase (III, IV or 5.0) you have selected. The list of files will look like the samples given below:

  1. EMPLOYEE.INF (contains the Index File Details)
  2. EMPLOYEE.DBF (the data except the Memo Field Values)
  3. EMPLOYEE.DBT (the Memo Field contents)
  4. LastName.NDX (LastName Field Index information if saved as dBase III)
  5. Postalco.NDX (PostalCode Field Index information if saved as dBase III)
  6. PRIMARYK.NDX (PrimaryKey Index information if saved as dBase III)

If you are exporting the Table in dBase IV or 5.0 Version then the information in the last three files will be saved into a single Multiple Index file with the file extension .MDX. The Export, Import or Link operations are influenced by the dBase Driver known as ISAM stands for Indexed System Access Method), a common method used by dBase, Foxpro (upto Version 3.0) etc.

When you attempt to link a dBase Table to your MS-Access Database it will look for all these related files to load information correctly into Access. Assume that you have deleted the File EMPLOYEE.DBT from the folder then the Table Import, or Link operation fails with the Error: cannot locate the XBase memo file.

You may Export the Employees Table into dBase IV and 5.0 Versions as well to try opening with SQL Syntax for these Versions also. But you can use dBase III Version syntax to open other Version Tables also.

We have gone through all the fundamentals that we need to know about dBase Files and it is time to open and work with the data. Copy and paste the Code given below into a Global Module of your Database and select File- - >Save to save the Module. Click in the middle of the Code and press F5 to Run the Code.

A MsgBox will open up displaying the LastName field contents from the Employee.dbf File. If you are trying with one of your own dBase file then change the Code to insert your Table Name and Field Name.

Public Sub OpenDirectDBF()
'Open DBase File directly and read contents
Dim db As Database, rst As Recordset
Dim strSql As String, i As Integer
Dim msg As String

strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];"

'Syntax for dBase IV & dBase V
'strSql = "SELECT Employe4.* FROM Employe4 IN 'C:\MydBase'[DBASE IV;];"
'strSql = "SELECT Employe5.* FROM Employe5 IN 'C:\MydBase'[DBASE 5.0;];"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

i = 0
With rst
msg = ""
Do While Not .EOF And i < 5
   msg = msg & ![LastName] & vbCr
   i = i + 1
   .MoveNext
Loop
MsgBox msg
.Close
End With

Set rst = Nothing
Set db = Nothing
End Sub

Tips: Even if you use the Table Name with more than 8 characters in the SQL Syntax (say Employees having 9 characters), it will ignore the extra character s after the 8th character and will open the file correctly. You may enable the SQL statements given in the Code for other version of dBase by removing the single quote (') character at the beginning and try running the Code. If you have FoxPro version 2.5 or 3.0 installed on your machine then replace [DBASE III;] with [FoxPro 2.5;] or [FoxPro 3.0;] to try with these files. Later Versions of FoxPro uses DSN based Syntax.

Displaying Excel Value directly on Access Form is next.

Share:

Opening External Data Sources

Linking of external data sources like dBase, Excel or Tables from another Microsoft Access Database, in a Back-End, Front-End scenario, is the most common and efficient method to work with data, besides Tables from within the Database. If such Data Sources are linked with Access database then it is easy to build queries on them to process and prepare customized Reports, or Design Forms to Add/Modify Data from within Access. The Source data can remain in the parent Application for updating information. We can work with the linked Tables with the same ease of using another Table of MS-Access except modifying the structure of the linked Table.

To link a Table manually, highlight Get External Data in File Menu and select Link Tables from the displayed Menu. The Common Dialog Box will be displayed and you can browse to the location of another MS-Access Database, dBase File or Excel File and select it. They will expose Objects that can be linked to the MS-Access Database.

If the source data is an Excel Database then it is better to give a Range Name (using Insert- -> Name - -> Define Option) to the whole Range of Data Area before attempting to link with MS-Access Database. The main issue with the Excel based data sources is that if the data is copied and pasted from other sources like Word or Text file then the Data Type will get mixed up in columns and the data will show #Error in those cells when we open it in Access.

Even though Excel Cells can be set with Data Validation Options for manual data entry, like accept only integer values or values within a Range, or set the limit for accepting number of characters in a Cell maximum to 25, or accept Date or Time only and so on, but these are seldom used.

So far we were discussing about working with external data tables after manually linking them to MS-Access. We will see later how to Link external files using VBA without using the Menu Options highlighted above.

If we don't need a permanent link with the external table but we need data from it then we can open it directly from Access and work with the data.

To start with this method, we will try to open another Microsoft Access Database and read data from a Table with Code. We will see later examples of opening dBase File and Excel Databases to read data from these data sources as well.

Normally, after launching Microsoft Access we will open only one Database and that is always visible on the Application Surface, unless we hide it through the Startup Option. We already knew that Microsoft Access is a component based Application and it consists of several Objects and all of them are organized in hierarchy Order. Application Object is the top most one and we are opening it manually every time when we want to do something in Microsoft Access. We can create Microsoft Access Application Object from other Systems like Visual Basic and work with Microsoft Access Databases. The second in command is the Data Access Objects (DAO) at the top of all the other Objects like DBEngine (known as Jet Engine), Workspaces Collection, Databases Collection, User Groups, Users, Containers, Documents, Table Definitions, Query Definitions and so on. Let us look into a graphical representation of the arrangement of some of these components, a few of the topmost ones, because we are going to use them to open our second database and to read the contents of a Table from it.

The Jet Database Engine is the driving force behind our work with MS-Access Databases and it supports several WorkSpaces. We normally Load or Creates our Database(s) in Workspace(0).

The Database Security details like UserGroups, Users, PersonalIDs (PIDs), Passwords are maintained by Data Access Objects (DAO) in a separate Workgroup Information Database with .mdw Extention. The active Workgroup File Name associated with the current Workspace can be checked by reading the DBEngine.SystemDB property. The DBEngine holds the Default UserID and Default Password associated with the active Workspace.

We will go back to the topic of Workspace(0) and proceed further. We must set a reference to DBEngine.Workspaces(0), where our current database resides, to open a second Microsoft Access Database within the same Workspace to read/write data from/to its Tables. If you want to add another field into the Table you can do it as well.

To demonstrate a simple example we will open the Employees Table from the sample NorthWind.mdb Database and display the LastName Field contents in a MsgBox control with the following few lines of Code:

Public Sub OpenSecondDatabase()
Dim wsp As Workspace, db As Database
Dim rst As Recordset, msg As String

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("c:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb")
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)

With rst
msg = ""
Do While Not .EOF
    msg = msg & ![LastName] & vbCr
    .MoveNext
Loop
   .Close
End With
MsgBox msg

Set rst = Nothing
Set db = Nothing
Set wsp = Nothing
End Sub

Copy and Paste the above Code into the Global Module of your Database and save it. Place the Cursor anywhere within the Code and press F5 to execute the Code. The LastName Field values of the Employees Table from Northwind.mdb will be displayed in MsgBox control after closing the database. The reading of records is put in a loop to read all the nine records in that table. If you are using a different table with more records then limit the reading cycle by changing the condition in the Do While Not .EOF statement with the support of a Variable like the following Code snippet.

With rst
msg = ""
X=1
Do While X < 10
    If Not.EOF then
        msg = msg & ![LastName] & vbCr
       .MoveNext
    End If

    X=X+1
Loop
   .Close
End With

The reading statements are placed within an If . . .Then Statement so that the program will cycle through the Loop and terminate normally without running into Error if you have less than 9 records in your Table. The last three statements releases the Memory occupied by the Objects.

The first line sets a reference to the Workspace(0) Object and the second line opens the Northwind.mdb Database within the same workspace. Even though this is not visible we can visualize now that there is a partner database within our own Workspace, side by side. The location of the database shown above is for MSAccess2003 Version. Other Versions may have some difference in location addresses after the \Microsoft Office\ part, but it will be in the \Samples\ Folder. You may find the correct location of your Version of this file and change the Pathname given above.

Next we will see how to open a dBase File directly and work with it.

Share:

PIE CHART OBJECT AND VBA

This is the continuation of a discussion on working with Chart Object in VBA. We have created a small Table and a Report with a Chart Object on it, in the earlier Post Working with Chart Object in VBA and gone through few simple Property changes on the Chart Object to get prepared for the Demo of the VBA Programs. Please go through Steps 1 to 7 explained in the earliest Post, by following the link above, and continue.

You may have a look at the Column Chart and VBA topic at your convenience as well.

If you have already tried out the Code given in the earlier Posts then you are familiar with most of the Code Segments that you see here and will have no difficulty in understanding them. The difference is only in the formatting method of individual PIE Slices and few property changes also. In this example we will try three types of Pie Charts: 1. 3D Pie, 2. 3D Pie Exploded, 3. Pie of Pie.

  1. Copy and Paste the Code given below into a Global Module in your Database.
    Public Function PieChart(ByVal ReportName As String, ByVal ChartObjectName As String)
    '---------------------------------------------------
    'Author : a.p.r. pillai
    'URL    : http://www.msaccesstips.com
    'Date   : July-2008
    'Customized Source Code : from Microsoft Access Help
    '---------------------------------------------------
    Dim Rpt As Report, grphChart As Object
    Dim msg As String, lngType As Long, cr As String
    Dim ctype As String, typ As Integer, j As Integer
    Dim db As Database, rst As Recordset, recSource As String
    Dim colmCount As Integer, chartType(1 To 5) As String
    Const twips As Long = 1440
    
    On Error GoTo PieChartChart_Err
    
    chartType(1) = "3D Pie Chart"
    chartType(2) = "3D Pie Exploded"
    chartType(3) = "Pie of Pie"
    chartType(4) = "Quit"
    chartType(5) = "Select 1-4, 5 to Cancel"
    
    cr = vbCr & vbCr
    msg = ""
    For j = 1 To 5
      msg = msg & j & ". " & chartType(j) & cr
    Next
    
    ctype = "": typ = 0
    Do While typ < 1 Or typ > 4
     ctype = InputBox(msg, "Select Chart Type")
     If Len(ctype) = 0 Then
        typ = 0
     Else
        typ = Val(ctype)
     End If
    Loop
    
    Select Case typ
        Case 4
            Exit Function
        Case 1
           lngType = xl3DPie
        Case 2
           lngType = xl3DPieExploded
        Case 3
           lngType = xlPieOfPie
    End Select
    
    DoCmd.OpenReport ReportName, acViewDesign
    Set Rpt = Reports(ReportName)
    
    Set grphChart = Rpt(ChartObjectName)
    
    grphChart.RowSourceType = "Table/Query"
    recSource = grphChart.RowSource
    
    If Len(recSource) = 0 Then
       MsgBox "RowSource value not set, aborted."
       Exit Function
    End If
    
    'get number of columns in chart table/Query
    'if Table or SQL string is not valid then
    'generate error and exit program
    Set db = CurrentDb
    Set rst = db.OpenRecordset(recSource)
    colmCount = rst.Fields.Count
    rst.Close
    
    're-size the Chart
    With grphChart
        .ColumnCount = colmCount
        .SizeMode = 3
        .Left = 0.2917 * twips
        .Top = 0.2708 * twips
        .Width = 5 * twips
        .Height = 4 * twips
    End With
    
    'activate the chart for modification.
    grphChart.Activate
    
    'Chart type, Title, Legend, Datalabels,Data Table
    With grphChart
         .chartType = lngType
        .HasLegend = True
        .HasTitle = True
        .ChartTitle.Font.Name = "Verdana"
        .ChartTitle.Font.Size = 14
        .ChartTitle.Text = chartType(typ) & " Chart."
        .HasDataTable = False
    End With
    
    'format Pie slices with gradient color
    With grphChart.SeriesCollection(1)
        .HasDataLabels = True
        .DataLabels.Position = xlLabelPositionBestFit
        .HasLeaderLines = True
        .Border.ColorIndex = 19 'edges of pie shows in white color
        For j = 1 To .Points.Count
            With .Points(j)
                .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
                .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
                .DataLabel.Font.Name = "Arial"
                .DataLabel.Font.Size = 10
                .DataLabel.ShowLegendKey = False
                '.ApplyDataLabels xlDataLabelsShowValue
                .ApplyDataLabels xlDataLabelsShowLabelAndPercent
            End With
        Next
    End With
    
    'Chart Area Border
    With grphChart
        .ChartArea.Border.LineStyle = xlDash
        .PlotArea.Border.LineStyle = xlDot
        .Legend.Font.Size = 10
    End With
    
    'Chart Area Fill with Gradient Color
    With grphChart.ChartArea.Fill
        .Visible = True
        .ForeColor.SchemeColor = 17
        .BackColor.SchemeColor = 2
        .TwoColorGradient msoGradientHorizontal, 2
    End With
    
    'Plot Area fill with Gradient Color
    With grphChart.PlotArea.Fill
        .Visible = True
        .ForeColor.SchemeColor = 6
        .BackColor.SchemeColor = 19
        .TwoColorGradient msoGradientHorizontal, 1
    End With
    
    grphChart.Deselect
    
    DoCmd.Close acReport, ReportName, acSaveYes
    DoCmd.OpenReport ReportName, acViewPreview
    
    PieChart_Exit:
    Exit Function
    
    PieChart_Err:
    MsgBox Err.Description, , "PieChart()"
    Resume PieChart_Exit
    End Function
    
  2. Press Ctrl+G to display the Debug Window (Immediate Window) in the VBA Editing View, type the following Command in the Debug Window and press Enter:

PieChart "MyReport1", "Chart1"

Sample run image of Option-1 is given below:

Sample run image of Option-2:

Sample run image of Option-3:

If you prefer to run this command from the On_Click() Event Procedure of a Command Button on your Form, you may do so. The Program will open myReport1 in Design View, resize the Chart1 Chart Object, big enough for Printing or Viewing, and formats the Chart elements. The Program saves the Report and re-opens it in Print Preview.

The Analysis of Values appearing in a PIE Chart is done differently than other form of Charts. That is why PIE Charts take only one set of Values.

If, four of us join together and decided to buy a Cake worth $100 and each one of us take the share of the Cost $6 (I will take the first share amount, if no icing), $60, $20 and $14, the claim on the Cake is in relation to the share of Amount. The Pie Slices will show the size of each one of our share of Cake.

In the earlier examples the Formatting of all the Bars of a particular Data Series are applied together as a single collection of Objects with higher level reference grphChart.SeriesCollection(). But in Pie Chart, individual element of a Data Series (Pie Slices) is referenced by getting one step further deep into the collection of Objects as grphChart.SeriesCollection().Points() and formats them separately with different set of Gradient Colors.

Tips: If you would like to format individual Bars of a Bar Chart you can use this method. But you must remove the following Properties from the Code, used for PIE Chart:

.DataLabels.Position = xlLabelPositionBestFit
.HasLeaderLines = True
.Border.ColorIndex = 19 'showing edges of Pie slices with white color
.ApplyDataLabels xlDataLabelsShowLabelAndPercent

X-Axis and Y-Axis Titles are not used in Pie Charts and these Code segments are removed from the Program. Chart Area and Plot Area are formatted with a different set of light Colors, different from our earlier examples. On these elements also we can use the Rnd() Function to generate different set of values in each Run, for Gradient Color formatting. But, it may not come up with the correct color combination every time, good enough for viewing or Printing. If you do not like the current Scheme Color Values you may try out with different Color Values from the Color Chart given on the first Post Working with Chart Object in VBA.

Share:

Column Chart and VBA

In our earlier example, Working with Graph Chart in VBA, we have made preparations to try out Charts with VBA and did sample Runs for three Types of Charts viz. Column Clustered, Line Chart and Pie Chart. If you have landed on this page for the first time you may go to the earlier Post by clicking on the Link above. Go through the simple preparations explained there, by following Steps 2 to 7, to try out the Code given here.

Earlier, we could change the Chart type through Code to one of three Types with common property settings that can be applied to all the three. But we have conditionally skipped some properties not relevant to PIE Chart, like Axis Titles. We will deal with PIE Chart separately later and will see how to format individual Pie Slices and change other settings as well.

Here, we will try only one Category of Chart viz. Column Type and will run the Code for 4 different designs. This type of Chart with vertical Bars is the most commonly used Charts and loosely referred to as Bar Chart. The Category Description of the Values plotted on the Chart are placed horizontally along the X-Axis, below the Chart and the Scale calculated automatically and placed vertically at the left side of the Chart (on Primary Y-Axis) with fixed intervals of values. This can be set manually too, if needed.

When very small Values are plotted along with Large Values on the Chart the variations of the small values over a period of time may not be clearly visible and may become difficult for any meaningful analysis purposes. In such situations these can be plotted on the Secondary Y-Axis and the Ups or Downs of values will be clearly visible. With the help of data labels the values on the chart become more meaningful.

When 3D Chart Type is selected Y-Axis gives way to Z-Axis for displaying Tick Labels and Axis Titles. The Charts with Horizontal Bars are the real Bar Charts.

We have created options in the Code to run for the following Options:

  1. Column Clustered.
  2. Column Clustered (Reverse Plot Order - flipped upside down)
  3. 3D Column Clustered.
  4. 3D Column Stacked.

There are optional properties in this Code that you can use to change the shape of the Bars to Cone or Cylinder.

In this example we are turning On the HasDatatable Property of Chart Object that will display the Source Data Table on the Chart. The X-Axis Category Labels Qrtrl to Qrtr4 becomes part of the Data Table display and if we attempt to use the X-Axis Tick-Label Property to set Font or Font Size we will run into errors. We have used it conditionally without eliminating the code altogether so that you will know the difference.

DataLabels Orientation Property gives us the ability to display the Data Labels tilted to a specified Angle in Degrees, besides normal Horizontal or Vertical formats. To try out the examples do the following:

  1. Copy and Paste the following Code in a Global Module of your Database and save it. If you have already gone through the earlier Post and gone through Step-2 to 7 then you are ready to run the Code.
    Public Function ColumnChart(ByVal ReportName As String, ByVal ChartObjectName As String)
    '---------------------------------------------------
    'Author : a.p.r. pillai
    'Date   : June-2008
    'URL    : http://www.msaccesstips.com
    'Customized Source Code : from Microsoft Access Help
    '---------------------------------------------------
    Dim Rpt As Report, grphChart As Object
    Dim msg As String, lngType As Long, cr As String
    Dim ctype As String, typ As Integer, j As Integer
    Dim db As Database, rst As Recordset, recSource As String
    Dim colmCount As Integer, chartType(1 To 6) As String
    Const twips As Long = 1440
    
    On Error GoTo ColumnChart_Err
    
    chartType(1) = "Clustered Column"
    chartType(2) = "Reverse Plot Order"
    chartType(3) = "3D Clustered Column"
    chartType(4) = "3D Stacked Column"
    chartType(5) = "Quit"
    chartType(6) = "Select 1-4, 5 to Cancel"
    
    cr = vbCr & vbCr
     msg = ""
    For j = 1 To 6
      msg = msg & j & ". " & chartType(j) & cr
    Next
    
    ctype = "": typ = 0
    Do While typ < 1 Or typ > 4
     ctype = InputBox(msg, "Select Chart Type")
     If Len(ctype) = 0 Then
        typ = 0
     Else
       typ = Val(ctype)
     End If
    Loop
    
    Select Case typ
        Case 5
            Exit Function
        Case 1,2
           lngType = xlColumnClustered
        Case 3
           lngType = xl3DColumnClustered
        Case 4
           lngType = xl3DColumnStacked
    End Select
    
    DoCmd.OpenReport ReportName, acViewDesign
    Set Rpt = Reports(ReportName)
    
    Set grphChart = Rpt(ChartObjectName)
    
    grphChart.RowSourceType = "Table/Query"
    recSource = grphChart.RowSource
    
    If Len(recSource) = 0 Then
       MsgBox "RowSource value not set, aborted."
       Exit Function
    End If
    
    'get number of columns in chart table/Query
    Set db = CurrentDb
    Set rst = db.OpenRecordset(recSource)
    colmCount = rst.Fields.Count
    rst.Close
    
    With grphChart
        .ColumnCount = colmCount
        .SizeMode = 3
        .Left = 0.2917 * twips
        .Top = 0.2708 * twips
        .Width = 5 * twips
        .Height = 4 * twips
    End With
    
    grphChart.Activate
    
    'Chart type, Title, Legend, Datalabels,Data Table
    With grphChart
         .chartType = lngType
         If typ = 3 Or typ = 4 Then 
    ' for 3D Charts only
           .RightAngleAxes = True
           .AutoScaling = True
         End If
        .HasLegend = True
        .HasTitle = True
        .ChartTitle.Font.Name = "Verdana"
        .ChartTitle.Font.Size = 14
        .ChartTitle.Text = chartType(typ) & " Chart."
        .HasDataTable = True
        .ApplyDataLabels xlDataLabelsShowValue
    End With
    
    'apply gradient color to Chart Series    
    For j = 1 To grphChart.SeriesCollection.Count
          With grphChart.SeriesCollection(j)
            '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
            .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
            .Fill.Visible = True
            .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
            '.Barshape = xlCylinder 
    ' xlCylinder, xlConeToPoint, xlBox, xlPiramidtoMax
            If typ = 1 Then
                .Interior.Color = msoGradientVertical
            End If
            .DataLabels.Font.Size = 10
            .DataLabels.Font.Color = 3
            If typ = 1 Then
                .DataLabels.Orientation = xlUpward
            Else
                '.DataLabels.Orientation = xlHorizontal
                .DataLabels.Orientation = 45 'titlted angle in degrees
            End If
            End With
        Next
    
    'Y-Axis /(Z-Axis for 3D)Title
    With grphChart.Axes(xlValue)
       If typ = 2 Then
        .ReversePlotOrder = True
     'flips upside down
       Else
        .ReversePlotOrder = False
       End If
        .HasTitle = True
        .HasMajorGridlines = True
        With .AxisTitle
            .Caption = "Values in '000s"
            .Font.Name = "Verdana"
            .Font.Size = 12
            .Orientation = xlUpward
        End With
    End With
    
    'X-Axis Title
    With grphChart.Axes(xlCategory)
        .HasTitle = True
        .HasMajorGridlines = True
        .MajorGridlines.Border.Color = RGB(0, 0, 255)
        .MajorGridlines.Border.LineStyle = xlDash
        With .AxisTitle
            .Caption = "Quarterly"
            .Font.Name = "Verdana"
            .Font.Size = 10
            .Font.Bold = True
            .Orientation = xlHorizontal
        End With
    End With
    
    'Primary Y/Z Axis values label's font size
    With grphChart.Axes(xlValue, xlPrimary)
         .TickLabels.Font.Size = 10
    End With
    
    'X-Axis category Labels (Qrtr1, Qrtr2...)
    If grphChart.HasDataTable = False Then
        With grphChart.Axes(xlCategory)
            .TickLabels.Font.Size = 8
        End With
    Else
        grphChart.DataTable.Font.Size = 9
    End If
    
    'Chart Area Border
    With grphChart
        .ChartArea.Border.LineStyle = xlDash
        .PlotArea.Border.LineStyle = xlDot
        .Legend.Font.Size = 10
    End With
    
    'Chart Area Fill with Gradient Color
    With grphChart.ChartArea.Fill
        .Visible = True
        .ForeColor.SchemeColor = 2
        .BackColor.SchemeColor = 19
        .TwoColorGradient msoGradientHorizontal, 2
    End With
    
    'Plot Area fill with Gradient Color
    With grphChart.PlotArea.Fill
        .Visible = True
        .ForeColor.SchemeColor = 2
        .BackColor.SchemeColor = 42
        .TwoColorGradient msoGradientHorizontal, 1
    End With
    
    grphChart.Deselect
    
    DoCmd.Close acReport, ReportName, acSaveYes
    DoCmd.OpenReport ReportName, acViewPreview
    
    ColumnChart_Exit:
    Exit Function
    
    ColumnChart_Err:
    MsgBox Err.Description, , "ColumnChart()"
    Resume ColumnChart_Exit
    End Function
    
  2. Insert a new MS-Office Graph Chart Object on a new Report, change the basic property values as given in the earlier Article (Working with Graph Chart in VBA) and save the Report with the name myReport2. We can use the same Table that we have created for earlier examples.

    NB: Always create a new Chart Object for new set of examples rather than using the same Chart created earlier. Some property changes found not reflecting correctly when re-used for different Chart Types.

  3. Run the Code either from a Command Button Click Event Procedure or open the Code Module (Alt+F11), display the Debug Window (Immediate Window) with Ctrl+G and type the following and press Enter Key. ColumnChart "myReport2", "Chart1"
  4. A Menu will appear with four Column Chart Options. Type 1 in the Text Box and press Enter key to run the first option.

    The Program Opens the Report myReport2 in Design View, modifies the Chart Properties, saves the Report with the Chart and then re-opens it in Print Preview. A sample run image is given below.

  5. Run the Program again and this time select Option number 2.

In this run we are setting the ReversePlotOrder property value to True to Flip the Chart upside down. The sample image is given below.

Every time when you run the Code, the Chart Bar Colors are selected at Random order from the Color Chart given in the earlier Post (Working with Graph Chart in VBA) and will display the Chart Bars differently.

If you plan to save the Chart and don't like the Color combination currently displayed you may run the option more than once till you get the color combination to your liking. If you want to save the current run of the Chart you may make a copy of the Report or Export the Report into MS-Access Snapshot Viewer Format (MS-Access 2000 or later).

Chart Area and Plot Area are set with light Gradient Color so that they will look better when printed.

Sample Run of Option 3.

Sample Run of Option 4.

In Option 3 and 4 Runs you can see that the Plot Area of the Chart is extended to display the Data Table and X-Axis Title. But, in the first two runs these are appearing outside the Plot Area.

You can copy and paste the finished Chart into Power Point Presentations. Before transporting the Chart to Power Point page, copy and paste the Values from the Table into the Data Grid of the Chart so that you can edit the values in power point itself, if needed.

  1. Double-Click on the Chart Object in Report Design View to activate the Chart and to display the Data Grid.
  2. Open the Table in Datasheet-View, click on the left top corner of the Table to highlight all the records.
  3. Select Copy from Edit Menu.
  4. Right-Click on the left-top corner cell of the Data Grid and select Paste from the displayed shortcut Menu.
Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts