Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access Object Documenter


We usually prepare Reports on processed or raw data and take printouts to share the information with others.

But, sometimes we need details of the database Objects: Tables, Queries, and Scripts (Macros) or VB Modules and other objects. We can take a detailed listing of Objects, like Table Fields and their Properties, Data Types and Sizes, or the Control Names on Forms/Reports and their properties, User-Level & Group-Level Permission Settings, and so on.

Table Properties Listing

We will take a listing of a Table with Field Names, Data Type, Size of each field, and the Index information. I have used the Employees Table for our example, but you can try this with any Table in your Database. The sample listing is given below:

  1. Select Tools - - > Analyze - - > Documenter.

  2. Click on the Tables Tab.

  3. Put Check Mark on any of your Tables.

  4. Click on the Options button to open the Options Dialog Control.

  5. Change the settings as shown above and click OK to close it.

  6. Click OK on the first Dialog control to format the Listing based on selected options and open it in Print Preview.

Note: Each type of Object has its own option settings, which produce detailed information based on their selections.


Useful Report Functions

Page Number Function: PageNo()

Standard Functions that you can use in Report Header or Footer Sections while designing Reports are given below. Copy and Paste the VB Code into a Global Module of your database and save it.

Write the Functions in the form of a Formula in Text Boxes as shown in the Syntax.

Function to display formatted Page Numbers.

Function : PageNo()

Syntax : =PageNo([page],[pages])

Result: Page: 1 / XXX where XXX stands for the maximum number of pages of the Report.

Note : [page],[pages] are MS-Access built-in Report Variables and must be used as shown.


Public Function PageNo(ByVal pg As Variant, _ByVal pgs As Variant) As String
'Output   : Page: 1/25
'         : Call from a Report Text Box control
'Author   : a.p.r. pillai
'Date     : 01/09/2007
'Remarks  : The Formatted Text takes up 15 character space
Dim strPg As String, k As Integer

On Error GoTo PageNo_Err

pg = Nz(pg, 0): pgs = Nz(pgs, 0)
strPg = Format(pg) & "/" & Format(pgs)
k = Len(strPg)

If k < 15 Then
   strPg = String(15 - k, "*") & strPg
End If

strPg = "Page: " & strPg

For k = 1 To Len(strPg)
  If Mid(strPg, k, 1) = "*" Then
    Mid(strPg, k, 1) = Chr(32)

PageNo = strPg

Exit Function

Msgbox Err.Description,, "PageNo()"
PageNo = "Page : "
Resume PageNo_Exit
End Function

Report Period Function: Period()

Function to print Period with formatted Start-Date and End-Date on Report Header or Footer.

Function : Period()

Syntax : =Period([StartDate], [EndDate])

Result : Period: 15/09/2007 To 30/09/2007

Note: Format String in the Code may be modified for country-specific date format.


Public Function Period(ByVal prdFrm As Date, _ByVal PrdTo As Date) As String
'Output   : Period: dd/mm/yyyy To dd/mm/yyyy
'         : Call from Report control to insert date
'Author   : a.p.r. pillai
'Date     : 01/09/2007
'Remarks  : Modify Format String for Country specific date format.

On Error GoTo Period_Err

Period = "Period: " & Format(prdFrm, "dd/mm/yyyy") & " To " & Format(PrdTo, "dd/mm/yyyy")

Exit Function

MsgBox Err.Description,, "Period()"Resume Period_Exit
End Function

Report Date Function: Dated()

Function to print formatted System Date in Header or Footer of the Report.

Function : Dated()

Syntax: Dated()

Result : Dated: 15/09/2007


Public Function Dated() As String
'Output  : Dated: 20/08/2007
'        : Call from Report Text Box control
'Author  : a.p.r. pillai    Date    : 01/09/2007
'Remarks  : Change Format String for Country specific Date Format
On Error GoTo Dated_Err

Dated = "Date: " & Format(Date, "dd/mm/yyyy")

Exit Function

MsgBox Err.Description,, "Dated()"
Resume Dated_Exit
End Function 

You can design the full Page Footer of a Report with the Date and Page Number at one go with a single Function. Read my earlier Article: Reports . . . Page Border. Use the Function DrawPageFooter() Code & procedure explained there.

You can add frequently used Expressions or Routines as Public Functions into a Global Module of your Database and Run from where you need them (Forms, Reports, Query Expressions, etc.) rather than repeating the code everywhere.

Library Database with User-Defined Functions

You can further enhance the use of all the common Functions that you have, by organizing them into a separate Database and linking this as a Library File to your New Projects. In this case, if you have any common Form (like MS-Access Form Wizard) that you have designed then that also can be placed in the Library Database. When you call the Library Function from your Project, which uses the common Form, MS-Access first checks for the Form in the Library Database, if not found there then opens it from your current Project.

Follow the procedure explained in the earlier Post Command Button Animation for linking the Essential Library Files to your Project. Your Library Database's name may not appear in the installed library files list. Browse the location of the database, attach it to the library list and select it.

You can save this Library file into a compiled state by converting it into an MDE File. Select Tools - - > Database Utilities - - > Make MDE File to convert and save the current database into MDE Format. You cannot edit the code in the MDE database. Preserve the MDB file for future changes and compilation, if it becomes necessary.

When you install your Project don't forget to install your common Library Database also along with it and attach it to your Project in the new location. That rule goes for other built-in Library Files also in the new location. It is likely that the built-in Library File Versions are different on the new machine, from what you have used at design time. This can happen if other Visual Basic-based Applications are already installed earlier in the target machine. Such items will show as MISSING in the selected Library Files' List and you must attach the available Version of the file to the installed location.

If your Project is shared by different Versions of MS Office Applications, then it is a good idea to attach an older version of the built-in library file (if available) to the project.

Refer to the earlier Post on Sharing an Older Version Database under the topic: Ms-Access Security.

Earlier Post Link References:


Reminder POPUPs


The Reminder Popup is a Report that opens and displays its contents automatically and plays a background sound to remind about something that needs the User's immediate attention. The Report is exported into an external independent file format (Access Snapshot File Format, need MS-Office2000 or later) and opens in an external window.

We can open and view Access Reports exported into Access Snapshot File Format independently without the use of Microsoft Access and easily transport the Report alone through e-mails and share it with others.

The reminder popup can be of any future event that falls between the current date and within the next 7 days or so that we need advanced information about, like appointments, conferences, Birthdays, etc.

This action is programmed into the Control Screen module with the help of the Form_Timer() Event Procedure.

The Demo Project

We will try an example with the Employees Table from the Northwind.mdb database. Import the Employees Table from the Northwind.mdb sample database. If you are not sure about the exact location of the sample database, go to the page Saving Data on Forms not in Table for the location references.

After importing the Employees table create a Query with the SQL String given below. Open a new Query in the design view (don't select any table or Query from the displayed list) and display the SQL Window. Copy and paste the SQL String given below into the SQL Window of the Query and save it with the name Birthday_Reminder.

SELECT Employees.EmployeeID,
 [TitleofCourtesy] & " " & [FirstName] & " " & [LastName] AS Name,
 "dd-mm") & "-" & Year(Date())) AS BirthDay,
 [BirthDay]) AS age
FROM Employees
WHERE (((DateValue(Format([birthdate],"dd-mm") & "-" & Year(Date()))) Between Date() And Date()+30));

Open the Query manually in normal view and check whether there is any record present in the Query output based on the criteria set that we have specified for testing. If there are no output records, then open the Employees Table and change a few records' Months in the Birth Date field to the current month or the month that falls within the next 30 days. At the end of the Query String, you can see an expression Date()+30 set in the Criteria part for testing purposes. After opening the Query in the design view you can change this value according to your needs, like +7 or +15, etc., depending on how many days in advance you would like to know about the event.

Design a Report using Birthday_Reminder Query as Record Source, like the sample given below, and save the Report with the name Birthday_Reminder:

Copy and paste the following Code into the VB Module of the Control Screen or the Main Screen of your Application.

'Global Declaration
Dim T As Long
Private Sub Form_Load()
Dim RCount

On Error GoTo Form_Load_Err

T = 0
RCount = DCount("*", "BirthDay_Reminder")
'if no records in BirthDay_Reminder Query then
'control is not passed into the Timer Event procedure

If RCount > 0 Then
    Me.TimerInterval = 250
End If

Exit Sub

MsgBox Err.Description, , "Form_Load"
Resume Form_Load_Exit
End Sub

Private Sub Form_Timer()
On Error GoTo Form_Timer_Err

T = T + 1
Select Case T
    Case 20
        'Me.Timerinterval = 0
    Case 260
    Case 261
      T = 21
End Select

Exit Sub

MsgBox Err.Description, , "Form_Timer"
Resume Form_Timer_Exit
End Sub

Private Function REMPOPUP()
Dim strPath As String, i As Integer, mplayerc As String
Dim mplayer As String, soundC As String
On Error GoTo REMPOPUP_Err

mplayerc = "C:\Program Files\Windows Media Player\mplayer2.exe " 'WindowsXP

soundC = "C:\Windows\Media\notify.wav" 'WindowsXP

'if media player2 not found then don't play sound
If Len(Dir(mplayerc)) > 0 Then
     mplayer = mplayerc & soundC
    Call Shell(mplayer, vbMinimizedNoFocus)
End If

strPath = "C:\Windows\Temp\BirthDay_Reminder.snp"

DoCmd.OutputTo acOutputReport, "BirthDay_Reminder", _"SnapshotFormat(*.snp)", strPath, True, ""

'if snapshot format is not available 
'disable the previous line and enable next line

'DoCmd.OpenReport "BirthDay_Reminder", acViewPreview

Exit Function

MsgBox Err.Description, , "REMPOPUP"
Resume REMPOPUP_Exit
End Function

The Trial Run

When the Main Form with the Code is open, the Form_Load() Event Procedure checks the presence of any record in the Birthday_Reminder Query. If there are records in the Source Query then the Form_Timer() Event Procedure is invoked with a quarter of a second interval incrementing the globally declared variable T. When the value in variable T reaches 20 (5 seconds) the REMPOPUP() Procedure runs and the Birthday_Reminder Report will open in Access Snapshot Format. Windows Media Player will play the notify.wav file as an accompaniment to the event.

After that, the Reminder Popup opens at hourly intervals. If this repetition is not required, then enable the statement (remove the ' character at the beginning).

Me.Timerinterval = 0

During the Form_Timer() event procedure, in that case, the Popup opens only once per Session.

Download Demo Database


MS-Access and Graph Charts2

Continuation of Last Week's discussion

If you have landed straight on this page please go through the earlier Post MS-Access & Graph Charts and then continue.

Sample Data for Pie-Chart
Desc Veh Sales Parts Sales Service Sales
Total Sales 450000 645000 25000
  1. Create a Table with the above structure and data and save it with the name pie_Table.
  2. Open a new Report in the design view. Select Object . . ., from the Insert Menu, select Microsoft Graph-Chart, and then Click OK. A Chart Object with default values is inserted into the Report.
  3. Click outside the chart on the report to de-select the chart and to come out of Edit Mode. Click again on the chart to select it, display the property sheet, and change the following values:
    • Size Mode = Zoom
    • Row Source Type = Table/Query
    • Row Source = Pie_Table
    • Column Heads = Yes
    • Left = 0.3"
    • Top = 0.3"
    • Width = 6.0"
    • Height = 4.0"
  4. Double Click on the Chart. The Chart Formatting Toolbar will appear on the top. Select a 3-D Pie Chart from the Chart Type Toolbar Options or Right-Click on an empty area within the Chart, click on the Chart Type option from the Shortcut Menu, and select a 3-D Pie Chart.
  5. Open the Pie_Table, click at the left top corner of the Grid and select Copy from Edit Menu.
  6. Click on the top left corner of the Chart Datasheet and Paste the copied value into the Grid.
  7. Delete the extra rows or columns of the sample data left in the Datasheet.
  8. Click on the shaded area around the Pie to select it, then click and drag the right bottom corner sizing control to make the Pie a little larger. Right-Click on the shaded area around the Pie and select Format Plot Area.  Select None from the Area options list, and select None under the Border Options.
  9. Right-click on an empty area of the chart and select Chart Options from the Shortcut Menu.
  10. Select Title Tab and type Total Revenue in the Chart Title control.
  11. Click on the Data Labels tab and select Percentage from Label Contains options and click OK to update the Chart Options.

Chart with more than one set of Bars

Desc Qtr1 Qtr2 Qtr3 Qtr4
A_Revenue 25000 35000 20000 40000
B_Expenses 15000 20000 13000 17000
C_Income 10000 15000 7000 23000

Create a Table with the above Field Structure and Data and save it with the name Table1. Go through the same procedure that we have used in the earlier Post MS-Access and Graph Charts from Step 4 onwards to create the Chart below. In Step-5 change the Property Row source = Table1.

Given below is the Finished Bar Chart created with the above sample data, for monitoring the Quarterly performance of individual Areas: Revenue, Expenses, and Income.

Customizing Chart Y-Axis Scale Values

The Y-Axis Scale of the Chart and the Major Unit Value intervals (0, 5000, 10000, etc.) are also calculated automatically by MS-Access and displayed on the Chart. There are times that we need to modify the Y-Axis Scale Major Unit intervals to smaller values when there are smaller values of the Chart items. We can customize the Scale Values if we need smaller units on the Scale.

Double-Click on the Chart to edit it. Right-click on the Y-Axis Line (the vertical line near the scale values) and select the Format Axis option to display the Menu. Select Scale Tab and change the values as follows:

  • Minimum = 0
  • Maximum = 51000
  • Major Unit = 3000

Leave the other values unchanged. Click OK to update the new scale settings on the Chart.

Once you change these settings manually it remains unchanged even if the actual value of the Chart crosses the Maximum value settings on the Scale. When it does, then you must change the value manually to show the Chart Values correctly or put a checkmark on all settings to calculate the scale values automatically by MS-Access.

Formatting Data Labels.

You can change the Alignment of the Chart Data Labels. Right-click on one of the Labels and select Format Data Labels from the Shortcut Menu. Select the Alignment Tab and click on one of the diamond-like symbols in the Orientation options. Experiment with the other options on the Alignment Tab.

We can display the actual data table Values that we have used for the Chart along with the Chart. Double-Click on the Chart. Right-click on an empty area of the Chart, away from the plot area, and select Chart Options. Select the Data Table Tab and put a checkmark in the Show Data Table option.

Secondary Y-Axis Usage

There are times that we have to use very small values along with very large values on the same Chart. Assume that the Income Values on the above Chart in all four Quarters are less than the 3000 mark and the Bars or Lines (in Line Graph) of those sets of values may not show big enough to compare them properly.

In such situations, we can use the Secondary Y-Axis to calculate the scale of the smaller values and the visibility of the Bars or Lines will be good on the Chart. The presence of Data Labels on the Graph is very important. A sample Image of the above Chart with the Income Values (Blue Colored Bars) plotted on the Secondary Y-Axis is given below:

Adjusting Bar Width

To reduce the thickness of the Blue Bars to make them as narrow as the other Bars we have to increase the gap between Bars. Double-Click on the Chart to invoke edit mode. Right-click on one of the Blue Bars.  Select Format Data Series, and change the Graph Width Value to 340 in the Options Tab. Click OK to update the change on the Graph.

The Image of a Chart plotted with the same values in Custom Chart Type Tubes is given below:

  1. MS-Access and Graph Charts
  2. MS-Access and Graph Charts-2
  3. Working With Chart Object in VBA
  4. Column Chart and VBA
  5. Pie Chart Object and VBA
  6. Missing Lines in Line-Chart
  7. Pie Chart and Live Data on Form
  8. Scaling Chart Object
  9. Cross-Tab Union Queries for Chart




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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