As far as Microsoft Access is concerned compacting the Database is an essential function to keep the Database size to the minimum. When you work with the Database MS-Access creates temporary work-objects within the Database resulting the file size to expand. We can display some of these temporary objects with VBA Code. Copy and paste the following Code into the Global Module of a Database that was not compacted recently. Press Ctrl+G to open the Debug Window (Immediate Window), click somewhere within the code and press F5 to execute it.

Public Sub tmpObjects()
Dim ctr As Container, doc As Document
Dim cdb As Database, item

Set cdb = CurrentDb
Set ctr = db.Containers("tables")
For Each item In ctr.Documents
   If Left(item.Name, 1) = "~ " Then
      Debug.Print item.Name
   End If
Next
Set db = Nothing
Set ctr = Nothing
End Sub

You will find a listing of temporary work-files, similar to the sample lines given below, in the Debug Window:

~sq_cBRVISIT~sq_cBR
~sq_cBRVISIT~sq_cBRV_Detail
~sq_cBRVISIT~sq_cEMPCOD
~sq_cBRVISIT~sq_cYRMTH

After compacting the Database if you run this code again then these type of objects will not appear at all.


If the Database is a Single User one then there isn’t much to worry about this issue, because all you have to do is to enable the Compacting on Close feature in the Options settings. Select the Menu Tools - -> Options - - > General Tab and put a check mark in the Compact on Close option. Every time MS-Access will compact the Database when you close it.


But, if the Database is Shared on Network then enabling this feature can lead to problems. You should have Exclusive Access of the Database to run compacting. When you turn on the Compact on Close feature it will attempt to do that but will fail if others are using it. We don’t know what will be the impact of these repeated attempts and failures, from different sharing points, on the Database. Enabling the Exclusive Access Option (Tools - -> Security - -> User and Group Permissions - -> Permissions Tab) will not allow concurrent sharing.


Why MS-Access insists on Exclusive Access of the Database for compacting because it deletes the original Database in this process and re-creates it. Check the following steps taken by the compacting Procedure:

1. Select Tools - -> Database Utilities - - > Compact and Repair Database. Closes the Current Database.


2. Creates a temporary Database with the name db1.mdb in the current folder.


3. Transfers all the Objects (Tables, Forms, Reports etc.), except the work-objects, into db1.mdb.


4. Deletes the original Database.


5. Copies db1.mdb file with the original name.


6. Deletes the temporary Database db1.mdb


7. Re-opens the newly created Database.

If a database is not active when you select the option in Step-1 you are asked to select the Source Database from disk and will prompt for a Target Name to save the compacted copy in your preferred location. It will not re-create it with the original name. It is up to you to decide what to do with the old and new files.


To do compacting on Server, User-level Disk Quota Allocation is also a matter of concern. The User should have double the size of the Database or more free disk space available under his allocated Disk Quota.


In these circumstances a separate Compacting Utility Program is useful when you have several Databases shared on Network. We are going to create such a Database with VBA Programs that can compact one or more Databases easily. The Program follows the same route map described in Step-1 to 7 above but with some change in the way the procedure is Run.


The Compacting Utility, that we are going to create, have the following advantages:

• Uses Local Disk Space for Work-File, which will speed up the process, and extra space on server is not required for this purpose.


• Can select more than one Database for compacting.


• Takes a safe Backup on Local Drive besides compacting.


• No failures due to non-availability of enough Disk Space under the User’s Disk Quota.

We will create a small Database with a Table to hold a list of Database Path Names, a Form and two VBA Programs on the Form Module.

1. Create a new Database with the name CompUtil.mdb.


2. Create a Table with the following structure.




Field Name Type  Size
ID  AutoNumber 
dbPath  Text  75




3. Save the Table with the name FilesList and key-in the full path names of your Databases running on Server and close the table. Do not use UNC (‘\\ServerName\FolderName\DatabaseNname’) type addressing method.





4. Open a new Form and create a List Box using the FilesList Table. See the design given below. Draw two Label Controls below the List Box and two Command Buttons below that, side by side.





5. Re-size the Controls and position them, to look almost like the design given above. The finished design in Normal View is given below. The Labels below the List Box are kept hidden and will appear only when we run the Program.




Change the Property Values of the Form and Controls so that you can make them look exactly like the design given above.


6. Click on the List Box and display the property sheet (View - - > Properties).


7. Change the List Box's Property Values as given below:



Name  :  dbList

Row Source Type :  Table/Query

Row Source  :  SELECT [FilesList].[ID], [FilesList].[dbPath] FROM [FilesList]

Column Count :  2

Column Heads :  No

Column Widths :  0.2396";1.4271"

Bound Column :  2

Enabled  :  Yes

Locked  :  No

Multiselect :  Simple

Tab Index :  0

Left  :  0.3021"

Top  :  0.7083"

Width  :  3.2083"

Height  :  1.7708"

Back Color :  16777215

Special Effect :  Sunken



8. Re-size the child Label Control, attached to the List Box, to the same size and position it above the List Box. Change the Caption to Database List.
   
   
9. Click on the first Label Control below the List Box, display the Property Sheet and change the following Properties:
   
   

   
   
   Name  :  lblMsg
   
   Visible  :  No
   
   Left  :  0.3021"
   
   Top  :  2.5"
   
   Width  :  3.2083"
   
   Height  :  0.5"
   
   Back Color :  128
   
   Special Effect :  Sunken
   
   
   

   
   
10. Display the Property Sheet of the second Label Control and change the following Properties:



Name  :  lblstat

Visible  :  No

Left  :  0.3021"

Top  :  3.0417"

Width  :  3.2083"

Height  :  0.1667"

Back Style :  Transparent

Back Color :  16777215

Special Effect :  Flat

Border Style :  Transparent



11. Change the following properties of the left-side Command Button:



Name  :  cmdRun

Caption  :  Repair/Compact

Tab Index :  1

Left  :  0.3021"

Top  :  3.25"

Width  :  1.4271"

Height  :  0.2292"



12. Change the following properties of the right-side Command Button:



Name  :  cmdClose

Caption  :  Quit

Tab Index :  1

Left  :  2.0833"

Top  :  3.25"

Width  :  1.4271"

Height  :  0.2292"



13. Change the Properties of the Form. Click on the left top corner of the Form where the left-side and Top design guides (Scales) meet. When you click there a blue square will appear indicating that the Form is selected. Display the Property Sheet and click on the All Tab, if that is not the current one, and change the following Properties:



Caption   :  External Repair/Compact Utility

Default View  :  Single Form

Views Allowed  :  Form

Allow Edits  :  Yes

Allow Deletions  :  No

Allow Additions  :  No

Data Entry  :  No

Scroll Bars  :  Neither

Record Selectors :  No

Navigation Buttons :  No

Dividing Lines  :  No

Auto Resize  :  Yes

Auto Center  :  Yes

Pop up   :  Yes

Modal   :  Yes

Border Style  :  Dialog

Control Box  :  Yes

Min Max Buttons  :  None

Close Button  :  Yes

Width   :  3.9063"



14. Click on the Detail Section of the Form, change the Height Property:



Height  : 3.7917"



15. Create a Header Label at the top with the Caption Compacting Utility and change the Font Size to 18 Points or to a size to your liking.


NB: If you would like to create a Heading with 3D style characters, like the sample shown above, visit the Page Create 3D Heading on Forms and follow the procedure explained there. This you can do it later also.



16. Select the Rectangle Tool from the Toolbox and draw a Rectangle around the Controls as shown on the Design.


17. Display the VBA Module of the Form (View - - > Code), Copy and Paste the following Code into it and save the Form with the name Compacting.



Private Sub cmdClose_Click()
If MsgBox("Shut Down...?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"cmdQuit_Click()") = vbYes Then
       DoCmd.Quit
End If
End Sub

Private Sub cmdRun_Click()
Dim lst As ListBox, lstcount As Integer
Dim j As Integer, xselcount As Integer
Dim dbname As String, t As Double, fs, f
Dim ldbName As String, strtmp As String

'create a temporary folder C:\tmp, if not present
On Error Resume Next
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("c:\tmp")
    If Err = 76 Or Err > 0 Then
             Err.Clear
       fs.createfolder ("c:\tmp")
    End If

On Error GoTo cmdRun_Click_Err

Me.Refresh
Set lst = Me.dbList
lstcount = lst.ListCount - 1

xselcount = 0
For j = 0 To lstcount
If lst.Selected(j) Then
    xselcount = xselcount + 1
End If
Next

If xselcount = 0 Then
   MsgBox "No Database(s)Selected."
   Exit Sub
End If

If MsgBox("Ensure that Selected Databases are not in Use. " _
& vbCrLf & "Proceed...?", vbYesNo + vbDefaultButton2 + vbQuestion, _
"cmdRun_Click()") = vbNo Then
   Exit Sub
End If

For j = 0 To lstcount
    If lst.Selected(j) Then
       dbname = lst.Column(1, j)
       dbname = Trim(dbname)
       ldbName = Left(dbname, Len(dbname) - 3)
       ldbName = ldbName & "ldb" 'for checking the presense of lock file.
       If Len(Dir(ldbName)) > 0 Then 'database is active
          MsgBox "Database : " & dbname & vbCrLf _
                  & "is active. Skipping to the Next in list."
          GoTo nextstep
       End If
    
       If MsgBox("Repair/Compact: " & dbname & vbCrLf & "Proceed...?", _
                vbQuestion + vbDefaultButton2 + vbYesNo, "cmdRun_Click()") = vbYes Then
            Me.lblMsg.Visible = True
            Me.lblstat.Caption = "Working, Please wait..."
            Me.lblstat.Visible = True
                       DoEvents
    
            dbCompact dbname 'run compacting
            
            Me.lblstat.Caption = ""
                       DoEvents
   
nextstep:
            t = Timer
            Do While Timer <= t + 5 'delay loop
                'do nothing
                       Loop
        End If
    End If
Next

   Me.lblMsg.Visible = False
   Me.lblstat.Visible = False

strtmp = "c:\tmp\db1.mdb"
'Delete the temporary file
If Len(Dir(strtmp)) > 0 Then
  Kill strtmp
End If

Set fs = Nothing
Set f = Nothing
Set lst = Nothing

cmdRun_Click_Exit:
Exit Sub

cmdRun_Click_Err:
MsgBox Err.Description, , "cmdRun_Click()"
Resume cmdRun_Click_Exit
End Sub





Private Function dbCompact(ByVal strdb As String)
Dim ErrLoop As Error, t As Long
Dim xdir As String, strbk As String
Const tmp As String = "c:\tmp\"

On Error GoTo Err_Compact

If Dir(tmp & "db1.mdb") <> "" Then
    Kill tmp & "db1.mdb"
End If

t = InStrRev(strdb, "\")
If t > 0 Then
   strbk = Mid(strdb, t+1)
End If
strbk = tmp & strbk

xdir = Dir(strbk)
If Len(xdir) > 0 Then
   Kill strbk
End If
'Make a Copy in c:\tmp folder as safe backup
Me.lblMsg.Caption = "Taking Backup of " & strdb & vbCrLf _
& "to " & tmp
DoEvents

   DBEngine.CompactDatabase strdb, strbk
   
Me.lblMsg.Caption = "Transferring Objects from " & strdb & vbCrLf _
& "to " & tmp & "db1"
DoEvents

   DBEngine.CompactDatabase strdb, tmp & "db1.mdb"

' Delete uncompacted Database and Copy Compacted db1.mdb with
' the Original Name

lblMsg.Caption = "Creating " & strdb & " from " & tmp & "db1.mdb"
DoEvents

    If Dir(strdb) <> "" Then
        Kill strdb
    End If

    DBEngine.CompactDatabase tmp & "db1.mdb", strdb

lblMsg.Caption = strdb & " Compacted Successfully." & vbCrLf _
& "Database backup copy saved at Location: " & tmp
DoEvents

Err_Compact_Exit:
Exit Function

Err_Compact:
  For Each ErrLoop In DBEngine.Errors
    MsgBox "Compacting Unsuccessful!" & vbCr & _
      "Error number: " & ErrLoop.Number & _
      vbCr & ErrLoop.Description
  Next ErrLoop
  Resume Err_Compact_Exit
End Function



You can set the Compacting Form to open at Startup. Select Startup from Tools Menu. Select the Form Compacting in the Display Form/Page Control. If you want to hide the Database Window then remove the check mark from Display Database Window Option.



18. Open the Compacting Form in Normal view. Select one or more Databases from the List Box for Compacting.


19. Click the Repair/Compact Command Button.

If you are running the Program for the first time it will check for the presence of a Folder c:\tmp. If it is not there then it will be created. This folder will be used as Work-Area for the Compacting Utility irrespective of from where you are running this Program (from Server or from Local Drive) and places the Backup Copies of the Compacted Databases.


The program runs a check on each selected database before running the Compacting Procedure to re-confirm that nobody is using it. If it does then it will display a message as such and will not Compact that Database.


The Label Controls that we have created and kept hidden below the List Box will be visible now. It will be updated with the program’s current activity information, at different stages of the Compacting Procedure.


Any suggestions for improvement of this Program are welcome.

---

Transparent Command Buttons
Colorful Command Buttons
Double Action Command Button
Sending Alerts to Workstations
Refresh Dependant Combo Box Contents

Labels: msaccess tips

If your Database is installed on a Network then a regular server backup is done on a Daily/Weekly/Monthly/Quarterly basis by the Network Team and kept in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deletion by mistake etc., you can always send a request to the Computer Department giving details of location, filename and safe backup date from which you would like to restore from. This may take a few hours to few days to get it done because the backup tapes or other mediums must be transported back from its storage location before they is able to complete your request.


If your Project have its own backup procedure and does it on a regular basis you don't have to go after others with requests and resultant delay in restoring the Database. This will also ensure that your Application’s down time is very minimal.
You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but when it comes to the safety of the Database File itself this will not work. When it is on a Network several Users may be accessing the shared folder besides the Application Users and a mistake can happen to anybody.
You cannot make a Database Read-Only under Network Security to protect it from inadvertent loss. If you do that then you cannot work with the Database.


As a precautionary measure we can take a quick Daily Backup of the Database File, from Server to Local Disk or vice versa with a DOS Batch File. The Backup should run immediately on opening the Database, by any one of the Users, for the first time of the Day.


We can do this with a simple VBA Routine to create a DOS Batch File in the database folder and run it from there to make a copy to local drive. We need a small table with a single record to keep track of the Backup event.

1. Create a Table with the following structure and save it with the name Bkup_Ctrl and add a single record with a date earlier than today in the bkupdate Field. Leave the other field blank.




Srl.   Field Name  Type   Size
a.     bkupdate          Date
b.     workstation  Text  20










bkupdate	workstation
01/05/2008




2. Copy and Paste the following Code into a Global Module of your Project and save the Module.




Public Function SysBackup()
'------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 01-Apr-2008
'URL   : http://www.msaccesstips.com
'All Rights Resersed by msaccesstips.com
'------------------------------------------------------
Dim dbPathName, j As Long, t As Date
Dim bkupdate, strBatchFlle As String, qot As String

On Error GoTo sysBackup_Err

qot = Chr$(34)
bkupdate = Nz(DLookup("bkupdate", "Bkup_ctrl"), 0)
' bkupdate+7 > date() for weekly backup
If bkupdate = Date Or bkupdate = 0 Then
    Exit Function
End If

dbPathName = CurrentDb.Name
j = InStrRev(dbPathName, "\ ")

If j > 0 Then
    strBatchFlle = Left(dbPathName, j)
    strBatchFile = strBatchFlle & "bakup.bat"
    Open strBatchFile For Output As #1
        Print #1, "@Echo off"
        Print #1, "Echo :------------------------- "
        Print #1, "Echo : " & dbPathName
        Print #1, "Echo :Daily Backup to C:\ "
        Print #1, "Echo :------------------------- "
        Print #1, "Echo : "
        Print #1, "Echo :Please wait... "
        Print #1, "Echo : "
        Print #1, "Copy " & qot & dbPathName & qot & " " & qot & "C:\ " & qot
'add lines here for Back-end database or for other Files
    Close #1

'Copy file
    Call Shell(strBatchFile, vbNormalFocus)
    t = Timer
    Do While Timer <= t + 10
        'wait for 10 seconds
       Loop

    DoCmd.SetWarnings False
    DoCmd.RunSQL "UPDATE Bkup_Ctrl SET Bkup_Ctrl.bkupdate = Date(), Bkup_Ctrl.workstation = Environ('COMPUTERNAME');"
    DoCmd.SetWarnings True
    
    'Kill strBatchFile
End If

sysBackup_Exit:
Exit Function

sysBackup_Err:
MsgBox Err.Description, , "sysBackup()"
Resume sysBackup_Exit
End Function




3. Add the following line of Code in the On_Load() Event Procedure of the Startup Screen or Main Switchboard or any other Form that opens immediately after loading the Database.

SysBackup

At the beginning of the SysBackup() routine, the Program reads the last backup date from the Bkup_Ctrl Table and checks whether it matches today's date, if it does then stops the program from proceeding further. By replacing the expression bkupdate = date() with the expression bkupdate+7 > date() you can schedule the Backup to run on weekly intervals on a particular Day of the Week.


The VBA Routine creates a DOS Batch File in the same folder of your Database and Runs it. The DOS Copy command is used for copying the Database File to the User's local drive. Even though a VBA FileCopy() Function is available, this will not work from within to make a copy of the same Database.


You may modify the line to change the Target Location C:\ to a different one, if needed.


A delay loop is built into the routine to slow down the program for about 10 seconds, to give enough time for the DOS Command to complete copying the Database. Normally, the VBA Code execution will not wait for the DOS Command to complete before executing the next statement. This will also prevent the User from start working with the Database before the copy operation is complete. You may increase or decrease this value depending on the size of the Database, or after trial runs of the procedure to determine approximate time it takes to copy.


The control table’s bkupdate Field is updated with current date immediately after completion of the Copy operation and this will prevent further running of this procedure in subsequent Sessions on the same day. If your Application has a Back-End Database then install this table in there and link it to the Front-End. If your Application is on a Network and shared by several Users then by referring the workstation field you can easily find out which machine have the latest Backup Copy.


The 'Kill strBatchFile statement (if enabled) will delete the DOS Batch File after the backup operation. The delay loop protects the DOS Batch File from this statement for about 10 seconds. Enable this line if you don’t want the batch file to remain in the database folder.


You can create a DOS Batch file manually, with a Text Editor like Windows Notepad, install it in the Database Folder and run it from Code or Macro. You may define the Source and Target Locations manually for the Copy command.


The advantage of the above Code is portability and convenience. You can copy the Code and the bkup_ctrl Table into your other Projects and run it without much change or worrying about the Source or Target Location addresses of the Database.

---

Crosstab Union Queries for Charts
Union Query
Percentage on Total Query
Selecting Office Assistant
Office Assistant with Check Box Menu

Labels: msaccess tips

Function to Calculate Number of Days


The User-Defined Function DaysM() given below can be used in calculations that involves the number of days of a particular month. Copy and Paste the following Code into a Global Module and save it in your Project.

Public Function DaysM(ByVal varDate) As Integer
Dim intYear As Integer, intmonth As Integer
On Error GoTo DaysM_Err
  
If Nz(varDate) = 0 Then
    DaysM = 0
    Exit Function
End If

intYear = Year(varDate)
intmonth = Month(varDate)

DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)

DaysM_Exit:
Exit Function
DaysM_Err:

MsgBox Err.Description, , "DaysM()"
DaysM = 0
Resume DaysM_Exit
End Function

Syntax: X = DaysM(varDate)


Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X.


The Parameter value can be a valid Date, a Date in Text format like "15-02-2008" or in its corresponding numeric value 39493.


If you would like to re-write the Function differently by adding few extra lines of code then you may replace the expression DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1) with the following lines of Code:

DaysM = Choose(intmonth, 31, 28 + IIf((intYear Mod 4) = 0, 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

If intmonth=2 then
   Select Case (intYear Mod 400)
       Case 100, 200, 300
            DaysM = DaysM - 1
   End Select
End if

This Function can be used in VBA Routines, Queries or Text Controls in Forms or Reports where number of days of a particular month is involved in calculations.


Example: An Employee resumed duty after her vacation on 15-02-2008. To calculate the balance number of days, for her salary payment, one of the three Expressions given below can be used.

Dt = #02/15/2008#

BalDays = 1 + DateDiff("d", Dt, DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) 

or

BalDays =   1 + Day(DateSerial(Year(Dt), Month(Dt) + 1, 1) - 1) - Day(Dt) 

or

BalDays = 1 + DaysM(Dt) - Day(Dt)

The first two calculations are performed with Built-in Functions. With DaysM() Function, which uses the second expression for the main calculation, we could arrive at the same result with lesser number of characters in the last expression.


Finding Number of Days in a Month is not a big issue. We have learned simple rules to keep track of this, like 30 days in months 4, 6, 9, 11 (April, June, September & November) and 28 days for February. When it comes to February only we need calculations, like Year/4=0, to find whether to add or not to add one more day to 28 days in February.


But, this needs correction when we enter into Centuries. During the Year 2000 we took 29 Days in February for calculations. But this has not happened in the Year 1700, 1800 or 1900 and not going to happen in calculations that includes the Year 2100, 2200 & 2300 either.


We take approximately 365.25 days for a year, based on Earth’s rotation time around the sun, and every 4th year is considered as leap year with 366 days, adding 1 more day to February. But, it is estimated that the Earth’s rotation time around the sun is about 365 days 5 hours 48 minutes and 45.5 seconds (i.e. 365.2422 days) only. We take about 0.0078 days more every Year into our calculations and this value will add up to 3.12 extra days in about 400 years of time. To adjust this excess 3 days, all Century Years not evenly divisible by 400 are made common years, even though they are evenly divisible by 4. The remaining 0.12 value becomes 1.2 in about 4000 Years time and year 4000 is not leap year, even though it is evenly divisible by 400.


Who knows before that some other discovery will take us into re-working the whole thing again?


References: Microsoft Encarta Encyclopedia


History of Calendar

The Roman Calendar

The original Roman calendar, introduced about the 7th century BC, had 10 months with 304 days in a year that began with March. Two more months, January and February, were added later in the 7th century BC, but because the months were only 29 or 30 days long, an extra month had to be intercalated approximately every second year. The days of the month were designated by the awkward method of counting backwards from three dates: the calends, or first of the month; the ides, or middle of the month, falling on the 13th of some months and the 15th of others; and the nones, or 9th day before the ides. The Roman calendar became hopelessly confused when officials to whom the addition of days and months was entrusted abused their authority to prolong their terms of office or to hasten or delay elections.


In 45 BC Julius Caesar, on the advice of the Greek astronomer Sosigenes (flourished 1st century BC), decided to use a purely solar calendar. This calendar, known as the Julian calendar, fixed the normal year at 365 days, and the leap year, every fourth year, at 366 days. Leap year is so named because the extra day causes any date after February in a leap year to "leap" over one day in the week and to occur two days later in the week than it did in the previous year, rather than one day later, as in a normal year. The Julian calendar also established the order of the months and the days of the week as they exist in present-day calendars.


In 44 BC Julius Caesar changed the name of the month Quintilis to Julius (July), after himself. The month Sextilis was renamed Augustus (August) in honour of Caesar Augustus, who succeeded Julius Caesar. Some authorities maintain that Augustus established the lengths of the months we use today.


The Gregorian Calendar


The Julian year was 11 min and 14 sec longer than the solar year. This discrepancy accumulated until by 1582 the vernal equinox (see Ecliptic) occurred 10 days early and Church holidays did not occur in the appropriate seasons. To make the vernal equinox occur on or about March 21, as it had in AD 325, the year of the First Council of Nicaea, Pope Gregory XIII issued a decree dropping 10 days from the calendar. To prevent further displacement he instituted a calendar, known as the Gregorian calendar, that provided that century years divisible evenly by 400 should be leap years and that all other century years should be common years. Thus, 1600 was a leap year, but 1700 and 1800 were common years.


The Gregorian calendar, or New Style calendar, was slowly adopted throughout Europe. It is used today throughout most of the Western world and in parts of Asia. When the Gregorian calendar was adopted in Great Britain in 1752, a correction of 11 days was necessary; the day after September 2, 1752, became September 14. Britain also adopted January 1 as the day when a new year begins. The Soviet Union adopted the Gregorian calendar in 1918, and Greece adopted it in 1923 for civil purposes, but many countries affiliated with the Greek Church retain the Julian, or Old Style, calendar for the celebration of Church feasts.


The Gregorian calendar is also called the Christian calendar because it uses the birth of Jesus Christ as a starting date. Dates of the Christian era (see Chronology) are often designated AD (Latin anno domini, "in the year of our Lord") and BC (before Christ). Although the birth of Christ was originally given as December 25, 1 BC, modern scholars now place it about 4 BC.


Because the Gregorian calendar still entails months of unequal length, so that dates and days of the week vary through time, numerous proposals have been made for a more practical, reformed calendar. Such proposals include a fixed calendar of 13 equal months and a universal calendar of four identical quarterly periods.


Source:Microsoft Encarta Encyclopedia

---

Union Query
Percentage on Total Query
Selecting Office Assistant
Office Assistant with Check Box Menu
Who Changed the Data

Labels: msaccess tips

Who changed the data? This question comes up when something found not in order with the information kept in the database and ended up with serious issues reaching out to too many. I didn’t give out much to make it clearer to you and wondering what this is all about. Let us look into a scenario to bring the story into brighter light.


There is a database on Credit Customers of the Company, which includes high profile Credit Parties from Ministries or Ministers themselves. All Credit Parties are grouped into different Categories based on their status or other credibility criteria and assigned with different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the parties as a regular follow up measure, to remind them to make payments or to check and reconfirm the statement is in order.


Since, high profile parties are kept under different Category Codes, their Statements are printed separately for internal record purposes only, but never forward it to the Parties, as per strict instructions from the top.


Several individuals in Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Code by mistake and the Account landed in the common category and the monthly statement goes out without knowledge of it, again by mistake. As far as the common category is concerned it is a routine work to transport it to the destination. The remaining story you can add here, if needed.


The investigating committee pointed their fingers on each one of the supposed to be responsible for the change, but nobody came forward hanging their head low. Finally EDP Department has been called up and the poor Application developer has to find the answers quickly or his head is going to roll, after all, he can change any data in his Application. But he was already prepared for such eventualities and had ready answers with him to save his skin. He caught the culprit red handed having left his finger print on the record crystal clear, whether the change was intentional or not, is a different issue altogether.


If you are from Accounts Department, I know you have so many questions to ask and you may even argue such a thing can never happen in your department. I already mentioned it is a story, but you cannot rule out the possibility altogether.


Data Entry and Editing are two major functions that involve maintaining information up-to-date. Field level validation checks are performed to maintain reasonable accuracy of the information fed into the System. Present day inventions like Scanners, HHTs (Hand Held Terminals) and other devices are also used. In these devices also the User IDs, Date and Time etc. can be recorded as part of the information fed into computers.


When several Users are involved in using and updating information in a MS-Access Database it should be installed in a Network, implementing strict MS-Access Security under Workgroups. Users must be organized into different Workgroups based on their activity with the database and allow them to Log-in with their own User ID and password limiting the activity within their own privileges.


We were discussing about the finger printing (I coined this phrase here all by myself) of the data editing event on the record. When a new record is entered into a table or during the editing session we can record the date, time and User ID into that record, which is very useful to sort or find the information entered or edited on a particular day or within a time-period. Inadvertent change to the data can happen and the user may not remember on which record she has made the wrong change, even if she is aware that something went wrong. With the aide of approximate time and date the user herself can find the record involved and correct them, if she is given the facility to do that.


While designing the Table add the following three fields at the end of the field list to record the Data Entry Date-Time, User ID and record Edited Date-time:

Field Name       Type        Size
DEDate              Date/Time
EditedBy            Text         20
EditDate            Date/Time

Click on the DEDate Field. Enter the expression =Now() in the Default Value property of the field at the bottom. The EditDate and EditedBy fields we will update every time a new record is added or when changes made to it on the Form.


While designing the Data Entry/Edit Forms place the above fields also on the Form at a convenient location. Display the Property Sheet of the fields and set the Locked property of the above fields to Yes so that the user will not change the values manually. Set the Tab Stop property to No so that during normal data entry or editing time the cursor will not move into these fields.


Even if you set the Locked property to Yes the user can click on this field and sort the data and find the records she has edited recently and correct the mistakes, if she knows about it, but don’t know how to find it.


If you don’t want the user to use this field any way but let her see the contents then set the Enabled Property of the field to No and reset the Locked Property to No. If you plan to keep these fields hidden from the user then set the Visible property of the fields to No.


Now, with setting up a simple Before Update Event Procedure we can start recording these values on the table. While the Form is still in design view, display the VB Module of the Form (View - > Code), copy and paste the following code into the module and save the Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me!EditedBy = CurrentUser
     Me!EditDate = Now()
End Sub

The DEDate field will record the current date automatically when a new record is created and the above procedure will record the Current User ID and Current Date and Time as the final step of the edit action of each record.


Even this method is not 100% fault free. For example, if someone else edited that record changing some other field, after the error has happened, her name will be recorded overwriting the actual person made the mistake. Then we will be catching an innocent person for somebody else wrong doing.


Then we have to consider keeping a history of changes made to the records and each edited record must be copied to a history file with Date, Time and User ID.

---

Dynamic Report
MS-Access & Mailmerge-3
MS-Access & Mailmerge-2
MS-Access & Mail-Merge
MS-Access Object Documenter

Labels: msaccess tips

Keyboard Shortcuts are very useful, if practiced and memorized, for frequently used actions without shifting back and forth between mouse and keyboard. Microsoft Access has an extensive list of Keyboard Shortcuts to make our work much easier. You can enter the search term Keyboard Shortcuts on the Help Menu to get a category-wise list. I have selected some of them and given at the end of this Post for reference.


If you look carefully on the MS-Access Menu Options you can find one letter in each Menu Item is underlined indicating that it is a Keyboard Shortcut Code.

For example: the letter E in Edit menu is underlined indicating that you can open the Edit Menu using ALT+E Keyboard Shortcut. Most of the Options within the Edit Menu also have keyboard shortcut Codes at the right side of each one.


When we design Forms we can define our own custom Keyboard Shorts (Access Keys) on the Form Controls in a similar way with the help of & symbol. If you insert & symbol at the left side of a character in the Caption of the Command Button (or in the Caption of a Label) you can use ALT+character to make the Command Button act as if it is being clicked and runs the action programmed into that Button.

For example: If you have a Command Button on a Form with the Caption Export you can write the Caption as &Export so that ALT+E Keyboard Shortcut can be used to run the Export action. When we define keyboard shortcuts this way we should see that it is not ending up with the same built-in Shortcuts. Here, we already have a Built-in Keyboard Short &Edit, which uses ALT+E. We can insert the & symbol to the left side of any letter within the word Export (like Ex&port for ALT+P) to avoid conflict with the built-in codes.

There are other methods to define Keyboard Shortcuts. You can put all your Custom Keyboard Shortcuts in a Macro for specific actions, like Print Previewing/Printing Report, for opening a Particular Form in Design View or Normal View, Run a Macro to Process Report Data etc. You must name the Macro as Autokeys. An image of the Example Macro is given below.




The first line Action OpenReport runs when the CTRL+O keys are used, CTRL+K runs another Macro.


If you assign an action to a key combination that is already being used by MS-Access (for example, CTRL+C is the key combination for Copy), the action you assign this key combination replaces the Access key assignment. A List of Key combination that you can use in your Autokeys Macro is given below.

^A or ^4	CTRL+A or CTRL+4
{F1}	F1
^{F1}	CTRL+F1
+{F1}	SHIFT+F1
{INSERT}	INS
^{INSERT}	CTRL+INS
+{INSERT}	SHIFT+INS
{DELETE} or {DEL}	DEL
^{DELETE} or ^{DEL}	CTRL+DEL
+{DELETE} or +{DEL}	SHIFT+DEL

If you want a Macro to run automatically when you open a database, name the Macro as Autoexec.


A list of useful general purpose Keyboard Shortcuts are given below for your reference.

Srl.	Key Combination	Action
SAVING AND PRINTING
1.	CTRL+S or SHIFT+F12 or ALT+SHIFT+F2	To Save a Database Object
2.	F12 or ALT+F2	To open the Save As dialog box
3.	CTRL+P	To Print the Current or Selected Object
FINDING REPLACING
4.	CTRL+F	To open the Find tab in the Find and Replace dialog box (Datasheet view and Form view only
5.	CTRL+H	To open the Replace tab in the Find and Replace dialog box (Datasheet view and Form view only)
6.	SHIFT+F4	To find the next occurrence of the text specified in the Find and Replace dialog box when the dialog box is closed (Datasheet view and Form view only)
WORKING IN DESIGN VIEW
7.	F2	To switch between Edit mode (with insertion point displayed) and Navigation mode
8.	F4	To switch to the property sheet (Design view in forms and reports in databases and Access projects)
9.	F5	To switch to Form view from form Design view
10.	F6	To switch between the upper and lower portions of a window (Design view of tables, macros, and queries and the Advanced Filter/Sort window only)
11.	SHIFT+F7	TTo switch from the Visual Basic Editor to form or report Design view
12.	ALT+V+P	To open property sheet for a selected object
Editing Controls in Form and Report Design View
13.	CTRL+C	To copy the selected control to the Clipboard
14.	CTRL+X	To cut the selected control and copy it to the Clipboard
15.	CTRL+V	To paste the contents of the Clipboard in the upper-left corner of the selected section
16.	CTRL+RIGHT ARROW	To move the selected control to the right
17.	CTRL+LEFT ARROW	To move the selected control to the left
18.	CTRL+DOWN ARROW	To move the selected control down
19.	SHIFT+DOWN ARROW	To increase the height of the selected control
20.	SHIFT+RIGHT ARROW	To increase the width of the selected control
21.	SHIFT+UP ARROW	To reduce the height of the selected control
22.	SHIFT+LEFT ARROW	To reduce the width of the selected control
Window Operations
23.	F11	To bring the Database window to the front
24.	CTRL+F6	To cycle between open windows
25.	ENTER	To restore the selected minimized window when all windows are minimized
26.	CTRL+F8	To turn on Resize mode for the active window when it’s not maximized; press the arrow keys to resize the window
27.	ALT+SPACEBAR	To display the Control menu
28.	SHIFT+F10	To display the shortcut menu
29.	CTRL+W or CTRL+F4	To close the active window
30.	ALT+F11	To switch between the Visual Basic Editor and the previous active window
Miscellaneous
31.	F7	To check spelling
32.	SHIFT+F2	To open the Zoom box to conveniently enter expressions and other text in small input areas
33.	ALT+ENTER	To display a property sheet in Design view
34.	ALT+F4	To quit Microsoft Access, close a dialog box, or close a property sheet
35.	CTRL+F2	To invoke a Builder

---

MS-Access Object Documenter
Useful Report Functions
Reminder Pop Up
MS-Access & Graph Charts-2
MS-Access & Graph Charts

Labels: msaccess tips

When I started learning Microsoft Access in 1996 the first challenge that I have faced was how to find a particular record on the Data Editing Screen or Filter a group of records on some condition.


I was asked to develop a System for the Vehicles Division of our Company, for tracking pending Orders and Receipts of Heavy Equipments and Vehicles. If I am the one who is going to use the System, then some how I could manage to find the information that I want to work with and nobody will know how much time I spent for doing that. But it is going to be used by someone else and it is my job to make it as user friendly as possible. Even though I have good knowledge of BASIC Language at that time, knew nothing about Access Basic (MS-Access Ver.2 language, a primitive form of Visual Basic).


I struggled with the problem for a while and finally decided to have a look in the Northwind.mdb sample database for clues. There it was, on the Customer Phone List Form, the technique that I was struggling for so long. Within an Option Group Control, 27 Buttons with Labels A-Z, All to filter Records of Company Name starting with the respective letter on the Button or to remove the filter using the All labeled button. The Option Group is linked to a Macro (Customer Phone List) for testing, which button on the option group is clicked, and to filter the Company Names starting with that letter. There were no second thoughts on this and I straight away transplanted this method on my first MS-Access Project. It was developed without touching a single line of Access Basic Code, all automated procedures are run with macros and this is still in use.


We will look into the Finding or Filtering records using three different methods on the same Form. You can use any one of the three methods or all of them in your Project. We will use the Products Table from the Northwind.mdb sample Database.

1. Import the Products Table from the Northwind.mdb Database. Visit the Page Saving Data on forms not in Table to find the location reference of the Northwind.mdb database, if you are not sure where to find it.


2. Click on the Table and select Form from Insert menu and select Autoform: Columnar from the displayed list, click OK to create the Form and save it with the suggested name: Products.





3. Display the Form Header/Footer Sections, if not already visible. Select Form Header/Footer from View Menu.


4. Create a Label on the Header Section of the Form and type Products List as Caption. Click on the Label and change the character size to 20 or more, to your liking, and make it Bold using the Format Toolbar above.



NB: If you would like to create the same 3D Style Heading, visit the Page: Shadow 3D Heading Style and follow the procedure explained there.



5. Create a Command Button at the Footer Section of the Form. Display the Property Sheet of the Button (Alt+Enter or select Properties from View Menu). Change the Property Values as shown:



• Name = cmdExit


• Caption = Exit



6. Design a Text Box and four Command Buttons on the Form, as shown in the shaded area of the Form. Change the property values of the Text Box and Buttons as given below:



7. Click on the Text Box, Display the Property Sheet and change the property values:


• Name = xFind


• Back Color = 0




• Text Box child Label Caption = Find / Filter Product Name



8. Click on the first button, Display the Property Sheet and change the property Values.



• Name = FindPID


• Caption = << Product ID


• Fore Color = 128



9. Click on the second button, Display the Property Sheet and change the property Values.



• Name = FindFirstLetter


• Caption = << First Letter


• Fore Color = 128



10. Click on the third Button, Display the Property Sheet and change the property Values.



• Name = PatternMatch


• Caption = << Pattern Match


• Fore Color = 128



11. Click on the fourth Button, Display the Property Sheet and change the property Values.



• Name = cmdReset


• Caption = Reset Filter



12. Display the Visual Basic Module of the form, select Tools - > Macros - > Visual Basic Editor when the Products Form is still in Design View.



13. Copy and paste the following Code into the VB Module of the Form and save the Form:

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub cmdReset_Click()
'Remove Filter effect
'Clear Text Box
Me!xFind = Null
Me.FilterOn = False
End Sub


Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

If Val(m_find) = 0 Then
   MsgBox "Give Product ID Number..!"
   Exit Sub
End If

If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If
     rst.Close
End If

End Sub


Private Sub FindfirstLetter_Click()
'Filter Names matching First character
Dim xfirstletter

xfirstletter = Me![xFind]
If IsNull(xfirstletter) Then
  Me.FilterOn = False
  Exit Sub
End If
If Val(xfirstletter) > 0 Then
   Exit Sub
End If

xfirstletter = Left(xfirstletter, 1)
Me.FilterOn = False
Me.Filter = "Products.ProductName Like '" & xfirstletter & "*'"
Me.FilterOn = True

End Sub


Private Sub PatternMatch_Click()
'Filter Names matching the group of characters
'anywhere within the Name
Dim xpatternmatch

xpatternmatch = Me![xFind]
If IsNull(xpatternmatch) Then
  Me.FilterOn = False
  Exit Sub
End If

Me.FilterOn = False
Me.Filter = "Products.ProductName Like '*" & xpatternmatch & "*'"
Me.FilterOn = True
End Sub

1. Usage of << Product ID Button.



• Click the Button with a Number less than or equal to the Product Code range of values in the Text Box.


• If clicked with Text Value it will ask for Product ID Number.


• If clicked when the Text Box is empty, it is same as clicking Filter Reset Button.



2. Usage of << First Letter Button.



• Click the Button with any Alphabet A to Z or a to z in the Text Box.


• If more than one character is entered only the first character will be taken.


• If Numeric Value is entered the filter action will be ignored.


• If clicked when the Text Box is empty, it is same as clicking the Filter Reset Button.



3. Usage of << Pattern Match Button.



• Click the Button with group of characters that to match anywhere within the Product Name.


• If clicked when the text box is empty, it is same as clicking the Filter Reset Button.



4. Usage of Filter Reset Button.



• Resets the earlier applied Filter action.


• Empties the Text Box Control.

---

Useful Report Functions
Reminder Pop Up
MS-Access & Graph Charts-2
MS-Access & Graph Charts
Reports Page Border

Labels: msaccess tips

As we already knew we can install Access Database on a Local Area Network (LAN) and several Users can work on it at the same time. But it is difficult to know how many users are actually online with the Application at a given time.


Similarly, when there are several MS-Access Databases on a Network under a centrally controlled Security Workgroup it will be interesting to know which users are currently working on the MS-Access Applications on the Network.


You don’t need any complicated programs to find this out. When someone opens a Database, say NorthWind.mdb, MS-Access opens a Lock-File with the same name of the Database with file extension .ldb (Northwind.ldb) in the same folder of the Database. You can open this file with Notepad or any other Text Editor and view the contents.


This file contains the Workstation Ids and MS-Access User IDs of all the active Users of the Database. The lock file will be deleted by MS-Access when all the Users close the database. In other words if a Lock File is not active then nobody is currently using the database.

We can implement MS-Access Security of several Databases with a common Workgroup File in a Network holding Workgroup IDs, User IDs and User Passwords. In such situations there will be a common Workgroup File (a Database with .MDW extension) accessible to all the Users in a Network. When someone Log-in into the Workgroup to open a database, a lock-file with .ldb extension will open up with the same name of the Workgroup file. This file will contain the Workstation IDs and User IDs of all the Users currently active across different databases.


We will design a Form and write Programs to open the Lock-File of a Database or Workgroup File and display the List of Workstation IDs and User IDs currently active. In addition to that we will try to send messages through Network to the selected Users from this list.

1. Design a Form similar to the one shown below and change the Properties of the Controls as explained. Smaller controls are Text Boxes and the big Control under the label Users Online is a List Box.





2. Click on the Text Box below the label: Workgroup or Database File Path and display the property sheet. Change the value of the Name Property to FPath.


3. Display the Property Sheet of the List Box and change the value of the following properties as given below:




• Name = UsrList


• Row Source Type = Value List


• Column Count = 2


• Column Heads = No


• Column Width = 1.25”;1.25”


• Bound Column = 1


• Multi Select = simple




4. Display the property sheet of the Text Box above the Send Message Button and change the value of the Name Property to msgtxt.



5. Display the Property Sheet of the Button with the caption <<Update User List and change the value of the Name Property to cmdUpdate.



6. Display the Property Sheet of the Button with the Caption Send Message and change the value of the Name property to cmdNS.



7. Save the Form with the name; whoisonline.


NB: We are referencing all the above Property Values in Programs and it is important that you change the Values of respective controls as explained above.


Copy and Paste the following Code into the VB Module (Select Tools > Macro > Visual Basic Editor to display the VB Module) of the whoisonline Form, Save and Close the Form.


NB: PROGRAM BUG FIXED.




Private Sub cmdNS_Click()
'-------------------------------------------------------
'Author: a.p.r. pillai
'Date  : 01/12/2007
'-------------------------------------------------------
Dim ctl As ListBox, msg As String, usrs As String
Dim i As Integer, pcs() As String, xmsg As String
Dim ic As Integer, j As Integer, lc As Integer

On Error GoTo cmdNS_Click_Err

xmsg = Nz(Me.msgtxt, "")

If Len(xmsg) = 0 Then
   MsgBox "Message Control is Empty.", , _
"cmdNS_Click()"
   Exit Sub
End If

Set ctl = Me.UsrList
lc = ctl.listcount
ic = ctl.ItemsSelected.Count

If ic > 0 Then
ReDim pcs(1 To ic, 1 To 2)
i = 0
For j = 0 To lc - 1
If ctl.Selected(j) Then
    i = i + 1
    pcs(i, 1) = ctl.Column(0, j)
    pcs(i, 2) = ctl.Column(1, j)
End If
Next

usrs = ""
For j = 1 To i
msg = "NET SEND " & pcs(j, 1) & " User: " & pcs(j, 2) & " " & xmsg
  Call Shell(msg)
  If Len(usrs) = 0 Then
     usrs = j & ". WorkStationID: " _
     & pcs(j, 1) & "  UserID: " & pcs(j, 2)
   Else
     usrs = usrs & vbCr & j & ". WorkStationID: " _
     & pcs(j, 1) & "  UserID: " & pcs(j, 2)
  End If
Next
  MsgBox "Messages Sent to:" & vbCr & vbCr _
  & usrs & vbCr & vbCr & "successfully.", , _
"cmdNS_Click()"
Else
  MsgBox "Select one or more Users in list and try again.", , _
 "cmdNS_Click()"
End If

cmdNS_Click_Exit:
Exit Sub

cmdNS_Click_Err:
MsgBox Err.Description, , "cmdNS_Click()"
Resume cmdNS_Click_Exit
End Sub


Private Sub cmdUpdate_Click()
       Me.UsrList.SetFocus
    Me.cmdUpdate.Enabled = False
   WhoisOnline Me.FPath
    Me.cmdUpdate.Enabled = True
End Sub




8. Copy and Paste the following VB Code into a Global Module in your Project and save:




Public Function WhoisOnline(ByVal strPathName)
‘-------------------------------------------------------
‘Author : a.p.r. pillai
‘Date   : 01/12/2007
‘-------------------------------------------------------
Dim strldb() As String, j As Integer, strpath As String
Dim str As String, intlen As Integer, t As Date
Dim pos As Integer, xsize As Integer, l As Integer
Dim FRM As Form, ctl As ListBox, qt As String
Dim x As String

Const strlen As Integer = 62

On Error Resume Next

qt = Chr$(34)

strpath = Trim(Nz(strPathName, ""))

If Len(strpath) = 0 Then
  MsgBox "File Path is Empty.", , "WhoisOnline()"
  Exit Function
End If
  
str = Right(strpath, 4)
l = InStr(1, ".mdb.mdw.ldb", str)
Select Case l
    Case 0
       strpath = strpath & ".ldb"
    Case 1, 5
      strpath = Left(strpath, Len(strpath) - 4) & ".ldb"
    Case 9
       'it is .ldb no action
End Select

Set FRM = Forms("WhoisOnline")
Set ctl = FRM.Controls("UsrList")

Open "c:\x.bat" For Output As #1
Print #1, "@Echo off"
Print #1, "copy " & strpath & " c:\xx.txt"
Close #1

Call Shell("c:\x.bat", vbHide)

x = Dir("c:\xx.txt")
t = Timer
Do While Len(x) = 0 And Timer < (t + 2)
   x = Dir("c:\xx.txt")
Loop

t = Timer
Do While Timer < (t + 2)
   'do nothing
Loop


Open "c:\xx.txt" For Input As #1
If Err > 0 Then
  'Lock file copying was not successfull
  MsgBox "Database is idle.", , "WhoisOnline()"
  Exit Function
End If
Input #1, str
Close #1

intlen = Int(Len(str) / strlen) + 1
ReDim strldb(1 To intlen, 1 To 2) As String
xsize = strlen / 2

For j = 1 To intlen
  pos = IIf(j = 1, 1, (j - 1) * strlen + 1)
  strldb(j, 1) = Trim(Mid(str, pos, xsize))
  pos = pos + strlen / 2
  strldb(j, 2) = Trim(Mid(str, pos, xsize))
Next

str = ""
For j = 1 To intlen
  If Len(str) = 0 Then
    str = qt & Trim(strldb(j, 1)) & qt & ";" & qt _
    & Trim(strldb(j, 2)) & qt
   Else
    str = str & ";" & qt & Trim(strldb(j, 1)) & qt _
    & ";" & qt & Trim(strldb(j, 2)) & qt
  End If
Next
ctl.RowSource = str
ctl.Requery

Kill "c:\x.bat"
Kill "c:\xx.txt"


End Function



In the above program we are creating a DOS Batch File (c:\x.bat) and running it, to make a copy of the Lock-File c:\xx.txt as a work file.


9. Open the whoisonline Form in Normal View. Type the full path of an active database (Tip: give the Path and Name of the Database you are currently working on, if you are not on a Network). If you are on a Network and have a common MS-Access Workgroup File, which other Users are currently connected then give that File’s Path and Name. No need to give File Name extensions .mdb, .mdw or .ldb, but if you wish to include you may do so for clarity.



10. Click on the <<Update User List button. The List Box will be populated with the active User’s Workstation IDs and MS-Access User IDs.



11. Select one or two Workstation IDs in the List Box.


12. Type a Message in the Text Box Control above the Send Message Button for the selected Users.


13. Click on the Send Message Button. The Message Text will pop up on the selected Users’ Machines.

NB: If you have selected your own machine name then the Send Message action will not work.

---

Reminder Pop Up
MS-Access & Graph Charts-2
MS-Access & Graph Charts
Reports Page Border
Highlighting Reports

Labels: msaccess tips

This is the continuation of a series of different 3D-Heading-Styles introduced for designing Microsoft Access Form/Report Headings. This design is a variant of the 3D-Heading Style presented under the Title Create 3D Headings on Forms. Both the Styles has its own beauty and once they are created you may copy the same Controls and customize them with different Fore-color, Font & Font Styles (Bold, Italics etc.) and use it on Form or Report Headings.