MS-Access Object Documenter

Introduction.

Typically, we prepare reports based on processed or raw data and print them for sharing or documentation purposes.
However, there are times when we need detailed information about the database objects themselves, such as Tables, Queries, Macros, VBA Modules, and other elements.
In such cases, we may require comprehensive listings that include:

• Table fields along with their data types, sizes, and properties

• Control names and properties on Forms or Reports

• User-level and group-level permission settings

• Macro and Module content summaries

These object-level reports can be invaluable for documentation, auditing, or troubleshooting purposes.

Table Properties Listing.

We will generate a detailed listing of a table that includes field names, data types, field sizes, and index information.

In this example, we've used the Employees table, but you can apply the same procedure to any table in your database.

A sample listing format is shown below for reference:

Click to Enlarge

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

2. Click on the Tables Tab.

   

   Click to Enlarge

3. Put a 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.

• Custom Menus and Tool Bars
• Calendar and Toolbars
• Animated Floating Calendar
• Startup Screen Design
• Control Screen Menu Design

Useful Report Functions

Page Number Function: 'PageNo()'

The following are standard functions that you can use in the Report Header or Footer sections when designing reports.

To use them, simply copy and paste the VB code into a global module in 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.

Code:

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)
Next

PageNo = strPg

PageNo_Exit:
Exit Function

PageNo_Err:
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.

Code:

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")

Period_Exit:
Exit Function

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

---

Report Date Function: Dated()

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

Function : Dated()

Syntax: Dated()

Result : Dated: 15/09/2007

Code:

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")

Dated_Exit:
Exit Function

Dated_Err:
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 commonly used functions by organizing them into a separate library database and linking it to your new projects. This way, any shared functions or even reusable forms—such as a custom Form Wizard—can be centrally maintained in the library database.
When you call a library function that references a form, Microsoft Access will first look for the form in the library database. If it’s not found there, it will then check in the current project and open it from there.

Follow the procedure outlined in the earlier post, Command Button Animation, to link the essential library files to your project. If your library database does not appear in the list of installed references, use the Browse button to locate it manually. Once found, attach it to the references list and select it to make its functions and objects available in your project.

You can save this Library file in 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 installing your project, ensure that the common library database is included and properly linked to your project in the new location. The same rule applies to any other built-in library files used by your application. Keep in mind that the version of certain built-in libraries on the target machine may differ from those used during development. This can occur if other Visual Basic–based applications were previously installed on the system. In such cases, these libraries may appear as MISSING in the References list. You must then reattach the correct or available version of the library from the installed location to resolve the issue.

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
• MS-Access Graph Charts
• MS-Access Graph Charts-2
• Report Page Border
• Highlighting Reports

Reminder POPUPs

Introduction.

The Reminder Popup is a specially designed report that opens automatically, displays important content, and plays a background sound to draw the user's immediate attention. It is exported in Access Snapshot File Format (available in Microsoft Office 2000 and later), allowing it to open in a separate window independent of Microsoft Access.

Reports in Snapshot format can be viewed without Access, making them ideal for sharing via email or transporting as standalone files.

This reminder pop-up is useful for upcoming events—such as appointments, conferences, or birthdays—that fall within the next 7 days and require advance notice. The pop-up functionality is triggered through the Form_Timer() event procedure is coded in the Control Screen module.

The Demo Project.

Let’s try an example using the Employees table from the Northwind.mdb sample database. Begin by importing the Employees table into your current database.

If you're unsure about the location of the sample Northwind.mdb file, refer to the instructions provided on the page Saving Data on Forms Not in Table for guidance on locating it.

After importing the Employees table, create a new query using the SQL string provided below.
To do this, open a new query in Design View—when prompted, do not select any tables or queries from the list.

Instead, switch to the SQL View and paste the SQL string into the window.
Save the query with the name Birthday_Reminder.

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

Open the Birthday_Reminder query in Datasheet View to check if any records are returned based on the specified criteria.
If no records appear, open the Employees table and modify a few records by changing the Birth Date field so the month falls within the current month or within the next 30 days.

At the end of the query’s SQL string, you'll notice a condition like Date() + 30 used for testing.
You can adjust this value in Design View to suit your needs—for example, +7 or +15—depending on how many days in advance you want to be notified of upcoming birthdays or events.

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

Click to Enlarge

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
DoCmd.Restore

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

Form_Load_Exit:
Exit Sub

Form_Load_Err:
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
        REMPOPUP
        'Me.Timerinterval = 0
    Case 260
        REMPOPUP
    Case 261
      T = 21
End Select

Form_Timer_Exit:
Exit Sub

Form_Timer_Err:
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

REMPOPUP_Exit:
Exit Function

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

The Trial Run

When the main form containing the code is opened, the Form_Load() event procedure checks whether the Birthday_Reminder query returns any records.

If records are found, the Form_Timer() event is activated, running at an interval of 250 milliseconds ( quarter of a second), incrementing a globally declared variable T.

Once the value of T reaches 20 (after 5 seconds), the REMPOPUP() procedure is executed. This opens the Birthday_Reminder report in Access Snapshot Format. At the same time, Windows Media Player plays the notify.wav sound file to alert the user.

Click to Enlarge

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.

ReminderPopup.zip

• MS-Access & Graph Charts
• MS-Access & Graph Charts-2
• Highlighting Reports
• Reports Page Border

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 the Object option 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 deselect 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. Creating and Formatting a 3-D Pie Chart.

   1. Double-click on the chart control in your report or form to open the Chart Formatting Toolbar.

   2. From the Chart Type Toolbar, select 3-D Pie Chart.
      Alternatively:

      • Right-click on a blank area inside the chart (not on the pie itself).

      • Choose Chart Type from the shortcut menu.

      • Select 3-D Pie Chart and click OK.

   3. Open the Pie_Table:

      • Click on the top-left corner of the datasheet (grid) to select all data.

      • Select Edit > Copy from the menu.

   4. Go back to your chart:

      • Click the top-left corner of the chart's datasheet grid.

      • Select Edit > Paste to paste the data.

   5. Remove extra sample data:

      • If any extra rows or columns remain after pasting, select them and use Edit > Cut to delete.

   6. Resize the Pie:

      • Click on the shaded area surrounding the pie to select it.

      • Drag the bottom-right sizing handle outward to enlarge the pie slightly.

   7. Format the Plot Area:

      • Right-click on the shaded area around the pie.

      • Select Format Plot Area from the shortcut menu.

      • Set Area Options to None.

      • Set Border Options to None, then click OK.

   8. Set Chart Title and Data Labels:

      • Right-click on an empty area of the chart.

      • Select Chart Options.

      • On the Titles tab, enter "Total Revenue" in the Chart Title box.

      • Switch to the Data Labels tab, check the Percentage option under "Label Contains".

      • Click OK to apply your changes.

Click to Enlarge

A chart with more than one set of Bars.

Table1

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 using the field structure and data provided above, and save it as Table1. Then, follow the same steps outlined in the earlier post, MS-Access and Graph Charts, starting from Step 4, to create the chart shown below. In Step 5, set the Row Source property to Table1.

The completed Bar Chart, created using the sample data above, illustrates the quarterly performance of individual areas—Revenue, Expenses, and Income—and is shown below.

Click to Enlarge

Customizing Chart Y-axis Scale Values

The Y-axis scale of the chart, along with the major unit intervals (e.g., 0, 5000, 10000), is automatically calculated and displayed by MS Access. However, when the chart data contains smaller values, it may be necessary to adjust these intervals for better visibility. In such cases, you can manually customize the Y-axis scale to use smaller unit intervals as needed.

To adjust the Y-axis scale manually, double-click on the chart to enter edit mode. Then, right-click on the Y-axis (the vertical line displaying the scale values) and select Format Axis from the shortcut menu. In the dialog box that appears, go to the Scale tab and modify the values as needed. For example, you can change the Minimum, Maximum, or Major Unit settings to better suit your chart data.

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

Click to Enlarge

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

Once you manually change the Y-axis scale settings, they will remain fixed, even if the actual chart values exceed the defined maximum. In such cases, you must update the maximum value manually to ensure all data points are displayed correctly. Alternatively, you can enable automatic scaling by checking all relevant options in the Scale tab, allowing MS Access to recalculate and adjust the scale values dynamically as the data changes.

Formatting Data Labels.

You can adjust the alignment of the chart’s data labels to improve readability. Right-click on any label and choose Format Data Labels from the shortcut menu. Go to the Alignment tab and select one of the diamond-shaped icons under Orientation to change the label direction. Feel free to experiment with the other alignment options to find the best fit for your chart layout.

You can display the actual data table used to generate the chart alongside the chart itself. To do this, double-click on the chart to enter edit mode. Then, right-click on an empty area outside the plot area and select Chart Options. Navigate to the Data Table tab and check the Show Data Table option.

Click to Enlarge

Secondary Y-axis Usage.

Sometimes, we need to display smaller values alongside much larger ones on the same chart. For example, if the income values for all four quarters are below 3000, their bars or lines (in a line chart) may appear too small, making it difficult to compare them effectively.

In such scenarios, using a Secondary Y-axis allows you to scale smaller values independently, enhancing the visibility of bars or lines representing those values. Including data labels further improves clarity. The sample image below illustrates this, with Income values (colored bars) plotted on the secondary Y-axis.

Click to Enlarge

Adjusting Bar Width.

To reduce the thickness of the blue bars and make them as narrow as the other bars, you need to increase the gap between them. Double-click the chart to enter edit mode, then right-click one of the blue bars and select Format Data Series. On the Options tab, set the Gap Width value to 340, and click OK to apply the changes to the chart.

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

Click to Enlarge

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Objects 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