Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MS-Access Object Documenter

We normally prepare Reports on processed or raw data and take print outs to share the information with others.

But, there are times that we need details of the database Objects: Tables, Queries, and Scripts (Macros) or VB Modules and other objects. We can take detail 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.

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 Table.
  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 the options selected and open it in Print Preview.

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

Share:

Useful Report Functions

Common 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 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

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

Function to print formatted System Date in 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 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.

You can further enhance the use of all the common Functions that you have, by organizing them into a separate Database and link this as a Library File into 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 file's list. Browse to 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 a .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 on 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 the earlier Post on Sharing an Older Version Database under the topic: Ms-Access Security.

Share:

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-Office 2000 or later) and opens it 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 with others.

The reminder can be of any future event that falls between current date and within next 7 days or so that we need advance 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.

We will try an example with the Employees Table from the Northwind.mdb database. Import the Employees Table from 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 Employees table create a Query with the SQL String given below. Open a new Query in 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,
 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 Query manually in normal view and check whether there is any record present in the Query output based on the criteria setting that we have specified for testing. If there are no output records then open the Employees Table and change few record's Month in the BirthDate field to current month or the month that falls within 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 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
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

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 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 accompaniment to the event.

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

Me.timerinterval = 0

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

Share:

MS-Access and Graph Charts2

Continued. . .

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 design view. Select Object . . . from Insert Menu, select Microsoft Graph Chart and Click OK. A Chart Object with default values is inserted on 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 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 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, click and drag the right bottom corner sizing control to make the Pie little larger. Right-Click on the shaded area around the Pie and select Format Plot Area, select None from the Area options 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

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

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

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 on 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 to the scale values) and select 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 check mark on all settings to calculate the scale values automatically by MS-Access.

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 symbol 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 check mark on Show data Table option.

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 3000 mark and the Bars or Lines (in Line Graph) of those set 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:

To reduce the thickness of the Blue Bars to make it 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 Gap-Width Value to 340 in Options Tab and 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:

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