Alternating line colour in a report

Wouldn't it be nice to make your report a bit more readable, perhaps by alternating the background colour of each row? If nothing else, this can save your users having to get a ruler out to read across the lines of your report. But how to do it? Well, there are a couple of things to cover first. We're going to be modifying the Detail section of the report, since I'm assuming that's where you've got your “row” fields. Take a look at the properties of the Detail section and, chances are, you'll find the Back Color property is set to 16777215 - this is white! Consider a colour as being made up of red, green and blue - each of these parameters takes a value between 0 and 255 in the world of Access colour, and the overall colour value is determined like this:

Red value
+ (256 * Green value)
+ (256 * 256 * Blue value)

Red, green and blue all have values of 255 for the colour white - doing the maths reveals the equivalent colour property value to be 255 + (256 * 255) + (256 * 256 * 255) = 16777215 - I know, how intuitive...

So what value do you use for your alternating colour? Well, the easiest way to work this out is to use the Back Color property's builder and pick a colour from a dialog box, then make a note of the number. You'll be pleased to know that pale grey (the alternate colour of choice for all discerning developers) equates to 12632256 - I'll be using this in the example below.

The other thing to point out is that for this to work effectively you must ensure that all visible controls in your Detail section have their Back Style property set to Transparent, otherwise you'll end up with unwanted white blocks all over your nicely shaded background. Anyway, here's the code to do the alternating - it uses the Xor operator within the Detail section's On Format event to do bitwise comparison between the two row colours, and sets the background accordingly.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Const lngColour = 4144959 'i.e. 16777215 (white) - 126322566 (pale grey)
    Detail.BackColor = Detail.BackColor Xor lngColour
End Sub

Sorting a list-box by clicking its column headings

So you've got a list-box on a form with a number of columns. Wouldn't it be nice if you could click on the column headings to dynamically sort the list-box's contents by the relevant column? Well there's probably a sharp way to do this but I haven't found it yet. Instead, let me show you the somewhat clunky method I use; it lets you sort in ascending order by left-clicking a column heading and descending order by right-clicking the heading or, if you prefer, to toggle sort order with repeated left-clicks.

How does it work? Well, it's based around the list-box's MouseDown event and uses the position of the mouse pointer (its X and Y co-ordinates) over the list-box to decide whether it's over a column heading or not. See, I told you it was clunky! The important thing to remember here is that these co-ordinates are measured in twips - to help you with your conversions, there are 1440 twips in an inch or 567 twips in a centimetre. Also, the co-ordinate 0,0 is the top-left corner of the list-box.

In the example shown, I've got a list-box called lisSummary with a Row Source of "SELECT Surname, Forename, Department FROM tblStaff;" and Column Heads set to Yes. For simplicity, each column is set to be 3cm (that's 1701 twips, lest we forget) wide.

Private Sub lisSummary_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Y <= 225 Then 'This is the right height for column-headings in 8pt MS Sans Serif
    Dim strOrd As String, strSQL As String
    If Button = 1 Then strOrd = " Asc;" Else strOrd = " Desc;" 'Left-click sorts ASC, right-click sorts DESC
    strSQL = "SELECT Surname, Forename, Department FROM tblStaff ORDER BY "
    Select Case X 'Compile the OrderBy clause
    Case 0 To 1700
        strSQL = strSQL & "Surname " & strOrd
    Case 1701 To 3401
        strSQL = strSQL & "Forename " & strOrd
    Case Else
        strSQL = strSQL & "Department " & strOrd
    End Select
    lisSummary.RowSource = strSQL 'Apply the new recordsource
    lisSummary.Requery
End If
End Sub

You could modify this code to toggle the sort order on a column when you repeatedly left-click on it, if you'd prefer. Here's how:

Private Sub lisSummary_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Button = 1 Then 'Check the left mouse button has been clicked, not the right
    If Y <= 225 Then 'This is the right height for column-headings in 8pt MS Sans Serif
        Dim strOrd As String, strSQL As String
        If Right$(lisSummary.RowSource, 5) = " Asc;" Then
            strOrd = " Desc;" 'Sort DESC if currently sorted ASC
        Else
            strOrd = " Asc;" 'Sort ASC if currently sorted DESC or for the first sort
        End If
        strSQL = "SELECT Surname, Forename, Department FROM tblStaff ORDER BY "
        Select Case X 'Compile the OrderBy clause
        Case 0 To 1700
            strSQL = strSQL & "Surname " & strOrd
        Case 1701 To 3401
            strSQL = strSQL & "Forename " & strOrd
        Case Else
            strSQL = strSQL & "Department " & strOrd
        End Select
        lisSummary.RowSource = strSQL 'Apply the new recordsource
        lisSummary.Requery
    End If
End If
End Sub

Returning the top n values

Ever wanted to return a specified number of the highest values in a field? Well, as with so many things in Access there are two ways of doing this. At least they both boil down to the same thing.

The first way is the easiest if you don't like to muddy your hands with SQL too much. Open the query in design view and bring up the Properties window. Set the TopValues parameter - this can take an integer value (5, 10, 25, etc) or a percentage (10%, 20%, and so on) - in turn, these values constrain the data returned by the query. Then sort the field you want to return the top values of in descending order. Easy, eh? But I can almost hear you asking, what about a “bottom values” property? Well there's no such thing... instead, simply sort the field you want to return the bottom values of in ascending order and then set the TopValues value as described above.

The other way of doing this is to get down and dirty with some SQL, and essentially do manually what the TopValues property/sort order combo does for you automatically. Here's an example - suppose you want the names of the top 10 students from the class of 1992. Here's the SQL:

SELECT TOP 10
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade DESC;

Here's another example - this time we're going to pull off the names of the bottom 10% of students from the same year. Here's the SQL - note the use of the PERCENT keyword and the change in sort order:

SELECT TOP 10 PERCENT
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade ASC;

Too easy, right? But there are a few important things to note here though. If you do not define a sort order (the ORDER BY clause if you're using raw SQL), the query will return an arbitrary set of n records which is not what you want at all! Secondly, the TOP predicate does not choose between equal values. In the first SQL statement above, if the twenty-fifth and twenty-sixth highest students have the same final grade the query will return 26 records. And finally, the value that follows TOP must be an unsigned integer data type.

Deploying user-specific desktop shortcuts

So you've got a networked database and you need an easy way of deploying customised desktop shortcuts for it to your users. Here's a cunning method.

The basis for this method is the use of Visual Basic scripting in the form of a VBS file - saves you having to build a setup.exe file or anything strenuous like that. Now VBS files have had something of a bad press, mainly because script-kiddies have used them to write viruses. Don't let that put you off.

Anyway, create a file in the text-editor of your choice and name it something intelligent like install_production.vbs - note the extension. Now the script shown below does the following:

1. Prompts the user to confirm their database username - since I use the user's Windows username for their database username, this is shown as the default response. If you're not using user-level security you can skip this step.
2. Creates and names the shortcut, checking the Registry to look-up the location of the user's version of Access (you can hard-code the Access executable's location if you'd prefer)
3. Sets the "run in" directory
4. Sets the shortcut icon
5. Sets the shortcut's window style

In the example that follows:

• MyApp is the name of the database application
• \\srvr\share\fldr\myapp.mdb is the location of the database file
• \\srvr\share\fldr\system.mdw is the location of the workgroup information file
• \\srvr\share\fldr\myapp.ico is the location of the application icon file

Okay, here's the script:

Set WshNetwork = WScript.CreateObject("WScript.Network")
Dim strI
strI = InputBox("Enter your MyApp database username here. If a default has been suggested, please " & _
    "check it before you proceed, amending as required." & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
    "NOTE: leaving this blank cancels the installation!", "Shortcut personalisation", WshNetwork.Username)
If Len(strI)>0 Then
    Dim WSHShell
    Set WSHShell = WScript.CreateObject("WScript.Shell")
    Dim MyShortcut, DesktopPath, AccessPath
    DesktopPath = WSHShell.SpecialFolders("Desktop")
    Set MyShortcut = WSHShell.CreateShortcut(DesktopPath & "\MyApp - production.lnk")
    AccessPath = WSHShell.RegRead("HKCR\.mdb\")
    AccessPath = WSHShell.RegRead("HKCR\" & AccessPath & "\shell\new\command\")
    AccessPath = Replace(Left(AccessPath, InStr(1, AccessPath,"/", 1) -1), """", "")
    MyShortcut.TargetPath = AccessPath
    MyShortcut.Arguments = "\\srvr\share\fldr\myapp.mdb /wrkgrp \\srvr\share\fldr\system.mdw /user " & strI
    MyShortcut.WorkingDirectory = "C:\Program Files\Microsoft Office\Office\"
    MyShortcut.WindowStyle = 3 '1=normal, 2=minimised, 3=maximised
    MyShortcut.IconLocation = "\\srvr\share\fldr\myapp.ico"
    MyShortcut.Save
    Msgbox "Installation completed successfully.", 64, "MyApp"
Else
    Msgbox "Installation was cancelled.", 48, "MyApp"
End If

Note that your users may get prompted to confirm that they want to run a VBS file, depending on their PC/environment security settings. Like I said before, this is a security measure to help prevent the feeble-minded exploiting the power of Visual Basic scripting to be malicious.

Counting the number of weekdays between two dates

If you want to calculate the number of weekdays between two dates in Excel, you simply use the Analysis Toolpak add-in function NetworkDays(). Unfortunately you don't have this luxury in Access if you're using version 97 or below, and even in 2000 you need an external reference. There are a number of solutions to this problem knocking around on the Internet - the most logically elegant (Mr Spock would be proud) can be found on the Access Web here. With due respect though, I'm more interested in speed than elegance so for a long time I used an alternative method that involved literally iterating through every day between the two dates and incrementing a counter if the day was a weekday. This was fine all while my dates were close together, but slowed right down as soon as you compared two dates years apart. Finally I resorted to puzzling out all possible weekday combinations and established some rules, which enabled me to write my own version which knocks both the alternatives mentioned above into a cocked hat for speed, and doesn't slow down for dates that are a long time apart. The function takes three parameters, a start date, a finish date and an optional Boolean so that you can specify if you want to include the last day in your count (this defaults to false if you omit it).

Please note: unlike the Excel function, this code does not allow for public holidays.

Function fctnNetworkDays(datFrom As Date, datTo As Date, Optional booIncEnd As Boolean = False) As Long
    Dim lngDiff As Long, booFWD As Boolean, booTWD As Boolean, bytF As Byte, bytT As Byte
    If booIncEnd Then datTo = datTo + 1
    lngDiff = DateDiff("d", datFrom, datTo, 0)
    bytF = WeekDay(datFrom, 0)
    bytT = WeekDay(datTo, 0)
    booFWD = (bytF < 6)
    booTWD = (bytT < 6)
    If booFWD And booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + lngDiff Mod 7
        If bytF > bytT Then fctnNetworkDays = fctnNetworkDays - 2
    ElseIf Not booFWD And booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) + bytF - 8
    ElseIf booFWD And Not booTWD Then
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) - bytT + 6
    Else
        fctnNetworkDays = (Int(lngDiff \ 7) * 5) + (lngDiff Mod 7) + (bytF <> bytT)
    End If
End Function

Converting macro-based menus

In the good old days (or bad, depending on your viewpoint) before Access 97 if you wanted to create a custom menu, shortcut menu, or toolbar in Access you had to create some macros. Indeed, in this author's opinion this was the only reason to play with macros other than AutoExec and AutoKeys. But times changes, and these days menus and toolbars are created as separate constructs (Tools, Customize, Toolbars). So if you're upgrading from an old version you have a lot of rewriting to do, right?

Wrong! Well, maybe in Access 97 you have, but from Access 2000 onwards the work has been done for you. All you have to do is this. In the database window, click Macros and highlight the name of the macro you want to create a menu or toolbar from. You only need to select the top-level macro; there's no need to select the macro group for each menu that appears on the menu bar. Then, on the Tools menu, select Macro, and then click either Create Menu from Macro, Create Toolbar from Macro or Create Shortcut Menu from Macro, depending on what you want to do.

The important things to remember though is that you can't then go ahead and delete the original macro after creating the new menu/toolbar - this is because it still depends on the original macro to run (unless the original macro only contained AddMenu or RunCommand actions). If you want to do away with the macro altogether, you need to convert the macro actions to VBA code, then modify the menu/toolbar you've just created to use the new VBA functions rather than the old macro actions.

Hiding the page header on the first page of a report

Chances are you won't want the report header and the page header on the first page as the two sections may have very similar content (captions, run by/when info, and so on). Here's the easiest way I've found of hiding the page header section latter on page one; it makes use of the page header section's OnFormat event, thus:

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Me.PageHeaderSection.Visible = Not (Me.Page = 1)
End Sub

This code sets the page header section's visibility to false if the page number is one, otherwise true. Bingo.

Deleting a record tidily

When you delete a record from a form, by default the next available record is displayed when the deletion is complete. No problems there, you might think, but what if the record you're deleting is the last in the recordset? A blank record is displayed. Some might argue that this is just an untidy annoyance that can be lived with, but I'd rather not. Also, if you're using Me.NewRecord in the form's Current event to do something in the event of a new record, you'll find that "something" happening, which isn't very convenient! The following example shows the module for a form that moves you back to the previous record if you delete the last record in the form's recordset:

Option Compare Database
Option Explicit
Dim booDel As Boolean

Private Sub Form_Current()
    If booDel Then
        booDel=not booDel
        DoCmd.GoToRecord , , acPrevious
    End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
    booDel=(Me.CurrentRecord=Me.RecordsetClone.RecordCount)
End Sub

Querying system objects

How much do you know about MSysObjects? Well, it's a hidden system table that includes information about the contents of your database. As such, you can use it to see if objects exist, when they were created, and so on. These are the key fields in the MSysObjects table:

• Name - the text name of the database object
• DateCreate - the date and time the object was created
• DateUpdate - the date and time the design of the object was last modified
• Connect and Database - used for storing connection information for linked tables
• Type - numeric value indicating the type of object, as follows:
• Form: -32768
• Report: -32764
• Macro: -32766
• Module: -32761
• Local table: 1
• Local database: 2
• Collection (e.g. forms, reports, modules, etc): 3
• Query: 5
• Linked table: 6
• Relationship: 8

So, for example, to return the names of all tables created since 1st January 2001, the SQL would be something like:

SELECT Name
FROM MSysObjects
WHERE Type=1
AND DateCreate>=#01/01/2001#;

Note that a Type value of 5 also returns all SQL queries embedded as the recordsource in forms and reports, as well as standalone queries.

Two ways of hiding a table in the database window

As with all good things in life, there are two ways to do this. The first is to right-click on the table in the database window and select Properties. Check the 'Hidden' Attributes check-box and, provided the ability to view Hidden and System objects hasn't been enabled (Tools, Options) the table will be hidden.

The second method makes more sense, to me at least. What sort of tables do you want to hide? Tables that store code lists perhaps, or other 'pseudo-system' tables? They're the only ones I ever hide anyway, but I digress. Try prefixing your table name with 'USys' (as in User System table, presumably), then gaze in wonderment as your table disappears from the database window, again provided the ability to view Hidden and System objects hasn't been enabled.