Polls

In case these past polls are of interest to somebody, somewhere, here are four survey's worth of results...

Standards

Why should you bother with standards? Maybe a couple of illsutrations are in order: consider a finance database that has a table called "Account", a query called "Accounts", a form called called "Account" and a report called "Accounts". Pretty confusing already, isn't it? Now try referring to these items in code, and you'll see what a nightmare naming can be. Alternatively, think of a form you've created without changing any of the control names - in six months time are you going to be able to easily tell TextBox8 from TextBox9, or ComboBox13 from ComboBox31?

Wouldn't it be a whole lot easier to adopt a naming convention that enables you to know what something is just from its name? Well, there are numerous naming conventions out there, the most popular of which, from an Access perspective at least, is the Leszynski/Reddick VBA naming conventions, a copy of which can be found in Word format here. You'll find the code samples on this site loosely conform to these standards. Like many developers, I use the subset of these standards that works for me, and I expect you'll do the same.

Anyway, regardless of whether you buy into the Leszynski/Reddick conventions or define your own, here are some general pointers on standards:

• even if you define your own, use some sort of standard and stick to it - you'll thank me for this later. It's more important to work to a standard - any standard - than to adhere slavishly to a particular standard.
• document your conventions so that others may follow your work. If you use an existing convention, reference it in your application's documentation.
• don't use spaces in object names - concatenate words instead, and use capitalisation to make things clear (e.g. use "CustomerDetails" instead of "Customer details"). apply your convention as you go along - backtracking over your database to rename all objects is no fun, and mistakes are easily made, even with third-party "search-and-replace" tools.

Adding an "ALL" option to a combo-box

We're all familiar with using a combo-box (also known as a drop-down list) to select data from a pre-defined or data-driven list of values. It's also quite common to use a combo-box to select a data value which can then be used in some context, for example selecting a value which is then used to filter a form or report. When you're using a combo-box in this way, it can often be good to add an “ALL” option at the top of the list, to show that you want everything... but how do you do this?

Fortunately it's a piece of cake, but there are different methods depending on what the row-source of the combo-box is. Broadly speaking, there are four generic types of combo-box row-source:

1. The Value List - the simplest row-source, with data values typed in and separated by semi-colons, so a row-source of North;South;East;West will give the drop-down list four simple values.
2. The Simple Bind - if a combo is on a form that's bound to a table, then the row-source of that combo can just be a table field name; as long as that field has a lookup set up at a table level, that lookup will cascade to the form and populate the combo-box drop-down list.
3. The Simple SELECT - a combo-box can have a simple SQL statement as the row-source, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID; would produce a drop-down list of Region names in alphabetical order.
4. The Complex SELECT - often a combo-box will use a SQL statement that returns more than one field, typically a numeric ID field and one or more narrative text fields. The ID field can then be given a column width of zero in the combo-box, allowing the user to see and highlight an intuitive and meaningful text value but to actually be selecting a numeric ID value. Such a row-source might look something like SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so we're happy with the four basic types of combo-box row-source as they might appear on a form control. Before we consider how to change each type to add an "ALL" option, let me first explain the general principle of what we're going to do. We're starting out with a combo-box from which we can select a value and then use that value in some way. For example, you might have a button on your form that opens a report but filters the data therein based on the value selected in your combo-box - in this case, the OnClick event of that button might include some VBA along the lines of DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion where Me.cboRegion represents the value of the combo-box. But to add an “ALL” option, we're going to have to change our thinking because what we're actually going to be adding is a “*” wildcard character option. So, in our subsequent use of the combo-box value, instead of using an Equals operator (=) we're going to have to think about using a LIKE operator. In other words, the previous example will have to change to something like DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE " & Me.cboRegion

That's easy enough, isn't it? The important thing to remember is this works because the SQL WHERE constraint of “LIKE '*'” returns everything that isn't Null.

Okay, enough theory - here are some examples of how to change each of the four combo-box row-source types to add that “ALL” option.

Row-source	Without “ALL”	With “ALL”
Value List	North;South;East;West	*;ALL;North;North;South;South;East;East;West;West
Simple Bind	The cascaded table field's lookup, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;	A UNION statement, e.g. SELECT "*" AS RegionID, "ALL" AS RegionName, 0 AS SortOrder FROM tblRegion UNION SELECT RegionID, RegionName, 1 FROM tblRegion ORDER BY SortOrder, RegionName;
Simple SELECT	A SELECT statement with one output, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID;
Complex SELECT	A SELECT statement with more than one output, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so you probably have a few questions now, such as:

Q. Why does my combo-box now show an asterisk or a number, instead of my nice intuitive narrative text?

A. Unless you tell it otherwise, the combo-box will still display the same number of columns, with the same widths, as before you made the changes outlined above. To restore the nice formatting, you need to do all the following:

• increase the combo-box's Column Count property - in the example above, the Column Count property would be 3 (2 for the Value List example), as the SQL statement returns three fields;
• add a column width of 0cm to the start of the Column Widths property (so the */ID field is hidden) - in the example above the Column Widths property would be 0cm;2.54cm;0cm (0cm;2.54cm for the Value List example) as you wouldn't want to see the third column either;
• ensure that the Bound Column property is set to 1.

Q. What's that “SortOrder” value all about?

A. You want your “ALL” to appear at the top of your drop-down list, right? But what happens when sorting alphabetically doesn't put “ALL” at the top? For example, you might have a list of countries, in which you'd find that Albania came above “ALL” in your combo-box. By adding a simple numeric flag called SortOrder, this can be avoided: SortOrder has a value of 0 for your “ALL” option and 1 for everything else. SortOrder can then be used as the first item in your row-source SQL statement's ORDER BY clause, to ensure that “ALL” comes first - subsequent items can then be added to the ORDER BY clause to sort the remaining data items however you like.

Q. Hows does it work again?

A. Time to revisit the example. Let's say you have a combo-box called cboRegion on a form; previously it had a simple SELECT style row-source. You want to use this control to restrict the data included in a report - the report is opened by clicking a button on the same form. Previously, the button's OnClick event would have contained something like this:

DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion 

If you'd selected a RegionID of 1 from your combo-box, this would have parsed as

DoCmd.OpenReport "rptSalesman", , , "RegionID = 1" 

Now you have something like:

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '" & Me.cboRegion & "'" 

If you select “ALL” from your combo-box, this will now parse as

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '*'" 

And, as we learnt earlier, "LIKE '*'” returns everything, and so gives you your “ALL” option. Hooray!

Q. I still don't get it - can't I download an example database?

A. Yes you can (I'm good to you, aren't I?). There's no difference in the code at all but, due to popular demand, I've created Access 97, 2000 and 2002 versions of a database to illustrate the example we've talked about. They have two tables (tblRegion and tblSalesman), one form (misleadingly entitled frmSalesman) and one report (rptSalesman). Take a look at the properties of cboRegion and the OnClick event of cmdRpt in frmSalesman - you can surely work the rest out for yourself!

Free download samples: Access 2002 and above | Access 2000 | Access 97 - you'll need unzipping software for these downloads, such as WinZip or 7-Zip.

Creating a “save record?” prompt

By default, any changes made to a bound record in a form are automatically saved whenever you move away from that record - this could be by moving to another record, by creating a new record or by closing the form. This is generally fine, but what if an unplanned change has been made in error? Wouldn't it be nice to get a “record changed - save changes?” type prompt when you move away from a record?

Fortunately this is easily achieved through use of the form's BeforeUpdate event - this is triggered by saving changes to a bound record (that's why it called “before update”, after all), so it's triggered when you move away from a modified record. Of course, it's also triggered if you do an explicit Save operation, but that's okay, it then just gives you a chance to confirm that you want to save, which is generally a good thing too. Anyway, here's the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel + vbQuestion)
    Case vbYes 'Go-ahead and save
        'Don't have to do anything
    Case vbNo 'Undo all changes, close without saving
        Me.Undo
    Case Else 'Cancel change, continue editing
        Cancel = True
    End Select
End Sub

Changing the default printer

It's a common question - how do you control what printer is used in your application? Especially if you don't want to use the default printer?

Access 2000 introduced the Application object which, amongst many other things, has a Printer property that can be easily used to set and change the current default printer. Here's how you might use it:

Dim prt As Printer 'Define a printer variable
Set prt = Application.Printer 'Set the variable to the current default printer
Application.Printer = Application.Printers("\\YourPrintServer\YourPrinter") 'Change the default printer
'Do whatever printing activities you want here
Application.Printer = prt 'Don't forget to set back to the user's original default printer

Personally, I find this most useful for printing things to my PDF queue which, needless to say, isn't my default printer. Speaking of which, if you're looking for a free PDF print queue tool, let me save you looking as I've tried dozens. The best, in my view, are Cute PDF and Bullzip - not only are they properly free (no nags or adverts), they’re very easy to use and produce smaller PDFs than lots of comparable products.

Control Access options at start-up

From Access 2000 onwards, default behaviour is to have multiple windows open for multiple database components, i.e. if you open a database and then open a form and a report you'll have three Access windows open on your taskbar. Now I don't like this - it's messy and it spoils the illusion of your nicely packaged Access database looking like a standalone application. What you need is a way of enforcing the "one window" constraint.

Later versions of Access also have a function that enables object name changes to be tracked. This is great in a development environment but really should be turned off when your database is deployed for live use - you'll get huge performance gains by doing this. Of course, you can set these options before the database is deployed, but unless you have tightly secured your database what's to stop an inquisitive user turning it back on? What you need is a way of enforcing these options.

Fortunately, both these requirements can be met using VBA. You want this code to run at start-up, so I guess you could put it in the OnLoad event of a start-up form if you have one. Typically though, I prefer to write a function called fctnAutoExec and call this from an AutoExec macro using the RunCode action. This will then run at start-up by default. I often give fctnAutoExec a boolean parameter so that I can differentiate between it running at start-up or me running it by some other means and at some other time from within the database application. Anyway, fctnAutoExec might take the form:

Public fctnAutoExec(Optional booStartup As Boolean)
    'Ensure name change tracking options are turned off
    Application.SetOption "Log Name AutoCorrect Changes", False
    Application.SetOption "Perform Name AutoCorrect", False
    Application.SetOption "Track Name AutoCorrect Info", False
    'Ensure multiple windows in Taskbar are disabled
    Application.SetOption "ShowWindowsInTaskbar", False
    If booStarting Then
        'Do stuff that you want at actual start-up
        'i.e. things you might omit from ad-hoc runs.
        'E.g. write "logon" entry to user history table
    End If
End Function

So, to run the function from your AutoExec macro you'd just use the RunCode action with fctnAutoExec(True) as the parameter. To run the function on an ad-hoc basis from within your database, for example if you want to re-initialise settings but without doing any start-up only stuff, you'd just call the function as fctnAutoExec(False) or even just fctnAutoExec, since a Boolean data type defaults to False.

As a footnote, you should know that both the “track changes” and “show windows in taskbar” settings can be set manually before you deploy, using Tools, Options, General and Tools, Options, View respectively. This VBA does give you a nice way of ensuring your preferences are always applied though.

Faster recordset iterations

Looping through a DAO recordset is a pretty common requirement, isn't it? And in each loop, more often than not you'll want to do something to the value or values that you find in certain fields. Ever noticed how such an iterative process is not particularly fast? Especially if you have a big recordset...

Here's an example of a traditional iterative loop - each time through, the code assigns the name of a supplier to a string variable (in other words, this is about the simplest example I could think of):

Dim strSupplier As String
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
    strSupplier = rst("SupplierName")
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Pretty standard stuff, I'm sure you'll agree, and maybe even how you'd write the code yourself if asked. Bill Gates would undoubtedly approve. But it's slow! If your supplier table is complex and holds several thousand records this is going to be very tedious! And all we're doing is assigning a value to a string variable. What if we want to do something more complex? And maybe to/with more than one field value? We need a faster method... fortunately, help is at hand in the shape of the Field object. Compare the example above to the code that follows, which has exactly the same effect but can be 20+ times faster:

Dim strSupplier As String
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSupplier", dbOpenDynaset)
Set fld = rst("SupplierName")
rst.MoveFirst
Do Until rst.EOF
    strSupplier = fld
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Neat, isn't it? Whether by accident or design, once the Field object variable has been defined as equating to a certain field in a Recordset object, it automatically updates itself for each record as you iterate through that recordset. And anything that can give you such a hike in speed (for simple DAO recordset operations - differences for ADO recordsets are less apparent) in an Access environment has got to be worth exploring, surely?

Running parametised queries from code

We've all been there sometimes - you've got a parametised query which works fine, but you want to call it and supply the parameters from code. The most likely reason for doing this is that you want to run the query in a batch job or just without requiring user input.

Aside from the obvious comment (if you know the values you want to use, why use your query at all? Why not just embed some SQL in your code?), if you really want to use your query and keep things simple, here's how you do it from code. In this example, you've got a query called qryFilterStaffByTitle which takes a parameter to restrict staff records to those of certain job titles. In the query design grid, the prompt used for the parameter is [Please enter the required job title here]. Here's the equivalent code for running the query and supplying the value of "Manager" to the job title parameter:

Dim dbs As Database, rst As Recordset, qdfParam As QueryDef
Set dbs = DBEngine(0)(0)
Set qdfParam = dbs.QueryDefs("qryFilterStaffByTitle")
qdfParam![Please enter the required job title here] = "Manager"
Set rst = qdfParam.OpenRecordset()

Creating a scrolling marquee field

Creating a marquee field (you know: a text box or label that scrolls, common on some websites) in an Access Data Page is easy, because there's a control for it in your toolbox. Not so when you're working with forms though. I was surprised to find a number of people searching this site for help on this subject, so here's an easy way of adding a scrolling marquee to your forms.

Let's do a simple example first - a Label control will act as a marquee for us. Here's what you do:

1. Create a form and place a Label control on it - name it lblMarquee and fill it with a nice long text string (for demonstration purposes, size the label so that you can't see all the text on screen when you view the form).
2. Set the form's TimerInterval property to 200 - this property is measured in milliseconds, so a value of 200 equates to one fifth of a second.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label by simply taking off the first character of the string and appending it to the end, like so:

Private Sub Form_Timer()
    lblMarquee.Caption=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1)
End Sub

4. Check out your form - you have a scrolling marquee field!

Okay, but that's a pretty basic example. It would be more useful if the marquee stopped scrolling when the mouse pointer hovered over it. This is a tiny bit more complicated... but still very easy. In fact, I bet I can show you how to do it in eight easy steps...

1. Start the same way as in the simple example: create a form and place a Label control on it called lblMarquee, then fill the label with a long text string.
2. This time though, leave the form's TimerInterval property set to 0.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label - this uses exactly the same code fragment as the simple example, above.
4. Whilst you're in the VBA code editor window, declare a constant called intTimer with a value of 200 - place this declaration in the General Declarations section at the top of your code. This is so that if you subsequently want to change the speed of the scroll you only have to change the timer interval value in one place.
5. Create an event procedure for the Form's OnOpen event that sets the form's TimerInterval property to intTimer (to start the marquee scrolling).
6. Create an event procedure for the lblMarquee's OnMouseMove event that sets the form's TimerInterval property to 0 (to stop the marquee when the mouse hovers over it).
7. Create event procedures for the OnMouseMove events of the Form and its Detail section that set the form's TimerInterval property back to intTimer (these restart the marquee when the mouse moves away from the label).
8. Check out your form - you now have a scrolling marquee that pauses when the mouse hovers over it!

Here's the VBA for this form in full:

Private Const intTimer As Integer=200 'Use a constant for the timer interval

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval=intTimer 'Sets initial scrolling speed
End Sub

Private Sub Form_Timer()
    lblMarquee=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1) 'Does the scrolling
End Sub

Private Sub lblMarquee_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=0 'A zero value stops the Timer event, and hence the scrolling
End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Clearly this isn't very sophisticated but you get the idea of how this can be made to work. Try experimenting with values other than 200 to see what value gives the smoothest scrolling (you might find it varies depending on font face and size). Also, I believe this code could be adapted to make other control types scroll too (a text box being the obvious alternative).

Sending an e-mail from Access

For a long time now, there has been a very easy way to send e-mails from within Access - just use the DoCmd.SendObject command without specifying an object to send! This worked (note the past tense) fine if you just wanted to send basic e-mails using your default, MAPI-compliant mail software.

However, with the introduction of Access 2000, this solution became very buggy - not only would it not always work, sometimes it would fail without any indication which could be a bit of a problem if you were sending mails without editing them first. For full details of the spurious nature of this bug, check out this Microsoft KnowledgeBase article. And bless 'em, our friends at Redmond even went so far as to post a (pretty rudimentary) alternative piece of code, which completists can read here.

Game over - you don't need to read any further, right? Wrong! As I've said, the alternative code supplied by Microsoft is fairly basic but, to be fair, it did form my starting point for the following piece of code. Basically, my e-mail function, posted here for your delectation, uses Outlook to not only send an e-mail but to do so specifying all the following parameters:

• The sender - handy if you have more than one Service set up in Outlook
• The recipient(s) - addressee(s), CC and BCC
• The subject
• The message text
• Attachment path
• Voting options
• Urgency
• Edit before sending - true or false

The sender, voting, urgency and attachment path are all features not unsupported by the old SendObject method of e-mailing, so in many ways the Access 2000 bug has been beneficial.

To use this code, you must first set a reference to the Outlook Object Library (in Access 2000 you can do this from the Tools, References menu option in the Visual Basic Editor) - the file you're looking to reference is an OLB file, e.g. msoutl9.olb for Outlook 2000.

Here's an example of how to call this function. In this example, an e-mail will be sent to Jo Smith and Estelle Jones, CC'ing Bob Downes, with a short subject and title, and an attachment. The message will be sent with high priority, and Yes/No/Maybe voting buttons. Finally, we'll edit the message before it's sent.

fctnOutlook , "Jo Smith;Estelle Jones", "Bob Downes", , "My subject here", "My message here", "c:\temp\att.txt", "Yes;No;Maybe", 2, True

Note that multiple names in the same parameter are separated by semi-colons, as are the options in the voting buttons string.

Finally, you'll see that when you use this code you get prompted to confirm that it's okay for your program to send an e-mail using Outlook. This is a virus-protection method introduced by Microsoft (read their white paper on this for more info) to prevent script-kiddies writing malicious code to send self-propagating mails to everyone in your address book. Using straight Outlook this is largely unavoidable, so you'll have to get used to clicking the “Yes” button (unless you want to consider Outlook Redemption as a freeware solution to this inconvenience, although that means getting away from straight Outlook). At least you get an option to allow your program access to Outlook for up to 10 minutes, which can be handy if you're about to generate a whole load of e-mails. Anyway, here's the code for you to cut and paste.

Function fctnOutlook(Optional FromAddr, Optional Addr, Optional CC, Optional BCC, _
Optional Subject, Optional MessageText, Optional AttachmentPath, Optional Vote As String = vbNullString, _
Optional Urgency As Byte = 1, Optional EditMessage As Boolean = True)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
    If Not IsMissing(FromAddr) Then
        .SentOnBehalfOfName = FromAddr
    End If

    If Not IsMissing(Addr) Then
        Set objOutlookRecip = .Recipients.Add(Addr)
        objOutlookRecip.Type = olTo
    End If

    If Not IsMissing(CC) Then
        Set objOutlookRecip = .Recipients.Add(CC)
        objOutlookRecip.Type = olCC
    End If

    If Not IsMissing(BCC) Then
        Set objOutlookRecip = .Recipients.Add(BCC)
        objOutlookRecip.Type = olBCC
    End If

    If Not IsMissing(Subject) Then
        .Subject = Subject
    End If

    If Not IsMissing(MessageText) Then
        .Body = MessageText
    End If

    If Not IsMissing(AttachmentPath) Then
        'Check file exists before attaching!
        If Len(Dir(AttachmentPath)) > 0 Then
            Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        Else
            MsgBox "Attachment not found.", vbExclamation
        End If
    End If

    If IsNull(Vote) = False Then
        .VotingOptions = Vote
    End If

    Select Case Urgency
    Case 2
        .Importance = olImportanceHigh
    Case 0
        .Importance = olImportanceLow
    Case Else
        .Importance = olImportanceNormal
    End Select

    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

    If EditMessage Then
        .Display
    Else
        .Save
        .Send
    End If
End With

Set objOutlook = Nothing

End Function

Querying users and security groups

Unbelievably, it is possible to part with your hard-earned cash for commercial tools that, amongst other things, present a summary of user/group membership in an easy-to-interpret way. People can get away with it because these tools are much easier to use and interpret in this respect than Access's integrated view of the same information, which constrains you to looking at either all the members of one group or all the group memberships of one user. Even I, the most ardent of Access-philes, have to admit that sucks.

So what's the solution? Part with your hard-earned cash? Never! Instead, let's delve deeper into your system.mdw file. For the uninitiated, this is the workgroup information file that controls the security information for the database in question. There is a default workgroup information file that is used for all databases unless you specify otherwise via the /wrkgrp command line parameter. But I digress...

Take a look in the system.mdw file - I find my default version at C:\Program Files\Common Files\System\system.mdw (I'm using Access 2000, by the way). Equally, you can open up any copy of a system.mdw file that you might be using for other databases. Either way, you'll find a couple of tables in there, MSysAccounts and MSysGroups. If you can't see these: go to Tools, Options and check System Objects on the View tab. You'll also find some queries, notably MSysGroupMembers and MSysUserMemberships which are on the way to what we want but still constrain you to the one user/group at a time view mentioned earlier.

So where were we? Well, as the name suggests, the MSysAccounts and MSysGroups tables store info on the users and security groups in your workgroup information file, so we can write a neat crosstab query to provide a grid of which users belong to which groups, like so:

TRANSFORM Count(MSysGroups.GroupSID) AS CountOfGroupSID
SELECT MSysAccounts.Name
FROM (MSysAccounts INNER JOIN MSysGroups ON MSysAccounts.SID=MSysGroups.UserSID)
INNER JOIN MSysAccounts AS MSysAccounts_1 ON MSysGroups.GroupSID=MSysAccounts_1.SID
WHERE (((MSysAccounts.FGroup)=0) AND ((MSysAccounts_1.FGroup)<>0))
GROUP BY MSysAccounts.Name
PIVOT MSysAccounts_1.Name;

Go on, give it a try! It's pretty tidy, isn't it? One thing to note - you don't have to do this in the system.mdw - if you'd rather integrate this crosstab in your database application, simply create links to the two tables in the relevant system.mdw file (File, Get External Data, Link Tables...) and then you can create the query above in your database file.

Linking to Outlook folders

Considering how thoroughly it's explained in the Help file, it's surprising how often the question “how can I link to Outlook folders?” crops up. This is popular as, for example, it allows the user to query the contents of their Inbox, use data from their Contacts list, report on their Calendar, and so on. Fortunately for us, this is very easy to do - if you can link to a table, you can link to virtually any Outlook folder on your friendly neighbourhood Exchange Server. We'll go through how in just a second but first a note for all users of Access 97 (are there any left?) - you can do this too but only after you've installed a free add-on from Microsoft, available here.

Now that's out of the way, here's how you do the linking.

1. In Access click File, Get External Data, Link Tables.
2. In the resultant “Link” dialogue box, change the Files of type parameter to Exchange or Outlook (either will do fine). This launches the Link Exchange/Outlook Wizard.
3. Use the tree structure shown by the Wizard to highlight the Outlook folder you want to link to and click Next.
4. Specify a name for the linked table.
5. Untick the “store my MAPI profile” check-box (unless you are likely to be the only user of the linked table).
6. Click Finish.

And that's it, you then have a table in your database that links back to your Outlook folder, and can be queried just like any other table. Note that this doesn't work for Outlook Express.

Calculating the median of a field

Access comes loaded with mathematical functions for all kinds of things, and it's easy to determine the sum, average, maximum or minimum of a field in a table, using either a query or some VBA. But what about some more complicated functions? What if you want to, for example, know the median value of a field in a table? For those of you whose maths is a bit rusty, the median is the value below which 50% of the data falls - the physical midpoint in a range, if you will. Access does provide a MEDIAN() worksheet function but this only works on a series of numbers rather than table data. It's still useful for illustrating exactly what a median is though - check out these examples:

MEDIAN(1, 2, 3, 4, 5) equals 3

MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4

So anyway, you want to work out the median of a table field. The following function takes two parameters, the name of the table and the name of the field, both as strings. Note that if you have spaces in either name (naughty you) you'll need to enclose them in square brackets before you pass them to the function. Anyway, the function just returns the median value.

Function fctnMedian(strTable As String, strField As String) As Variant
    Dim dbs As DAO.Database, rst As DAO.Recordset 'You must add a reference to the DAO Object Library too
    Dim booEven as Boolean
    Set dbs = DBEngine(0)(0)
    Set rst = dbs.OpenRecordset("SELECT * FROM " & strTable & " ORDER BY " & strField)
    If rst.EOF = False Then
        rst.MoveLast
        booEven = (rst.RecordCount Mod 2 = 0) 'Is there an even number of records in the recordset?
        rst.PercentPosition = 50 'Rounds down if there is an even number of records...
        fctnMedian = rst.Fields(strField)
        If booEven Then
            rst.MoveNext
            fctnMedian = (fctnMedian + rst.Fields(strField)) / 2 'Takes average of this & the next value up
        End If
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Converting macros to VBA

There will, inevitably, come a time when you get sick of macros - they're slow, unwieldy and clutter up your database. Who needs them (with the exception of AutoExec and AutoKeys, of course)? Most annoying of all though, macros just don't give you the flexibility you might want - what if you want to add custom error-handling, or perform some action beyond the dazzling array that the RunCommand macro action gives you?

It's time, then, to convert your crappy old macros to shiny VBA. Fortunately, this conversion couldn't be easier. Simply highlight the macro in question in the database window, then click Tools, Macro, Convert Macros to Visual Basic. You'll be prompted on-screen to confirm whether you want to add comments and error-handling. I'd recommend always saying yes to these questions; after all, you can always delete the comments later if they're not appropriate, and the default error-handling saves you a bit of typing when you come to write your own.

There are a couple of things to note though. Each macro you convert will be put in its own module - if you want to simplify things later on, you'll have to manually cut and paste the created VBA code into the module of you choice, then delete the surplus module. These created modules have names of the form “Converted Macro - Your macro name here”.

It's also worth remembering that the VBA this conversion process creates might not always be as slick as you might like. Consider the simplest of examples: a simple macro called mcrOpenMain which has one action, OpenForm, to open frmMain. Converting this in the manner described above produces the following VBA:

'------------------------------------------------------------
' mcrOpenMain
'
'------------------------------------------------------------
Function mcrOpenMain()
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain", acNormal, "", "", , acNormal

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Error$
    Resume mcrOpenMain_Exit

End Function

Now this isn't too horrendous, and certainly isn't as bad as the VBA that some of the form design wizards (notably the command button one) write. But it does add all the optional command parameters in and does ugly error handling - I like to see the error number too, for a start. My advice then is this: once you're happy that the VBA does what it's supposed to do, delete/edit any surplus comment lines, strip out any unnecessary command parameters and improve the error-handling loop... something like this:

Function mcrOpenMain()
' Converted from mcrOpenMain on 15-Sep-2005 by A. Developer
' Called from menu mnuMain
On Error GoTo mcrOpenMain_Err

    DoCmd.OpenForm "frmMain"

mcrOpenMain_Exit:
    Exit Function

mcrOpenMain_Err:
    MsgBox Err.Number & ": " & Err.Description, vbExclamation
    Resume mcrOpenMain_Exit

End Function

Much nicer, wouldn't you say? Depending on how fussy you are, you might also want to consider renaming the created function so that it doesn't start with “mcr” - but if you do, don't forget to check and change any other references to your nice new function, otherwise they won't run.

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.

An easy backup routine

It's always wise to make regular copies of all your database files. Whilst there are numerous tools that will do this for you, some of these are expensive, whilst others are overly complex.

If you have a fairly simple backup requirement, i.e. you just want to archive all the files in the database's directory on a regular basis, you might want to consider using a file zipping program, with a command-line interface. This lets you easily zip up all the relevant files with one command, whilst the command itself can be scheduled to run using Windows Task Scheduler or even the Handy Access Launcher.

Using this principal, I like to use WinZip to do my backups for me - the command-line interface is a freely downloadable add-on. Using the WinZip command-line interface takes the following form:

[WinZip command-line zipping executable] [Destination ZIP file] [Files to be zipped] [-switches]

For example:

"c:\program files\winzip\wzzip.exe" "g:\mdb\backup\cd.zip" "f:\mdb\cd\*.*" -ex -P -r -u

There are numerous available switches, described in full in the command-line help file that comes with the add-on. The switches shown in the above example (the only ones I feel it is necessary to use) have the following effects:

• -ex - sets compression to maximum;
• -P - causes all folder information specified in the command-line to be stored;
• -r - recurse into folders (include subfolders). This option requires the -p or -P option;
• -u - only add files that are new or have changed since the last zip file creation.

A series of these command-line statements can then be written in a single batch file. In this way, I backup seven production databases, and all associated files (e.g. workgroup information files, help files, icon files, splash-screen bitmaps, etc) in a single batch job which is scheduled to run twice a day. In other words, I get regular incremental backups without having to do anything!

I should probably point out a few of the drawbacks of this approach. If a file is in use when the backup runs, WinZip warns you that the zipped version may become corrupted. I haven't had any such problems but if WinZip feels such a warning is appropriate, then it must be possible. Secondly, this method only provides you with incremental backup, i.e. each backup overwrites the previous one. If this is inappropriate for your needs, consider something else. If you just want quick and easy regular backups of your production databases though, this may be for you.

Finally, if you've got a good reason for not using WinZip, you might want to look into 7-Zip which is entirely free (WinZip is shareware), has a superior compression ratio and a command-line interface. Indeed, if anyone does use 7-Zip to perform a backup in the manner described on this page please use the comments below to share the details.

Checking security group membership

These few lines of code will tell the current user whether he/she belongs to a specified security group. I use it extensively with my AutoKeys macro to enable keystrokes to do different things for Admins users and regular users. Note that if you're planning to ask the same question many times in your application, it might be an idea to define a Boolean global variable and set it once with this function, then subsequently refer to the variable, as the function can be a little slow, especially if you have lots of security groups. Anyway, it takes one parameter - the security group name - and returns True if the current user is a member of that group, False otherwise.

Function fctnUserGroup(GroupName As String) As Integer
    Dim w As Workspace, u As User, i As Integer
    fctnUserGroup = False
    Set w = DBEngine.Workspaces(0)
    Set u = w.Users(CurrentUser())
    For i = 0 To u.Groups.Count - 1
        If u.Groups(i).Name = GroupName Then
            fctnUserGroup = True
            Exit Function
        End If
    Next i
End Function

Handling keystrokes with an AutoKeys macro

The AutoKeys macro provides an easy way to set up global shortcut keys within your database. The 'Macro Name' parameter is used to specify the keystroke, whilst 'Action' is used to establish what events happen on pressing the relevant key or keys.

The syntax used for specifying keystrokes in the 'Macro Name' parameter is a subset of that used for the SendKeys statement. Note that you cannot use % to indicate keystroke combinations involved the 'Alt' key. Anyway, here are some examples of permissible syntax:

• {F12} : the F12 function key
• ^S : Ctrl + S
• +P : Shift + P
• +^O : Ctrl + Shift + O
• +^{F4} : Ctrl + Shift + F4

In addition, you can make use of the macro's 'Condition' parameter to only trigger the action if a certain condition is true. A second line against a keystroke can then be used to specify an alternate action if the condition is not true. I tend to use this functionality extensively with a function to determine user group membership so that certain keystrokes are enabled for Admin users but no-one else.

The example AutoKeys macro shown below enables the keystroke combination of Ctrl + N to start the new database dialogue for members of the Admins user group (this is not a built-in function, but bespoke - read all about it here), but displays a warning message box to non-Admin users.

Changing the zoom on a report depending on the number of pages

When you preview a report in Access, by default you are zoomed in on it. If the report is only one page long, I quite like to have the whole page showing instead. There is a neat way of doing this with just one line of code in the reports OnPage event. I like to bundle this with code to maximise the report so that it is displayed to its best advantage, like this:

Private Sub Report_Page()
    If Me.Pages = 1 Then DoCmd.RunCommand acCmdFitToWindow
End Sub

Private Sub Report_Close()
    DoCmd.Restore 'Restores the database to its pre-maximised view
End Sub

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Maximize 'Maximises the report preview
End Sub

This does the job, and can be adapted for other purposes too - for example, try replacing acCmdFitToWindow with acCmdZoom150

Anyway, this method replaces a previous fudge that made use of the annoying SendKeys command, which could cause your NumLock to toggle on/off mysteriously if you also used other SendKeys commands in your code, as explained here.

Conditional formatting by control type

Ever wanted to apply formatting to all the controls of a form at the same time? Easy! All the examples shown are running from the form's Open event, but could just as easily be applied to the Current event if you want different formatting for different records. The first example simply disables all the controls by setting their Enabled properties to False:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next 'In case some controls without the relevant property are considered
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = False
Next ctl
End Sub

This is fine but has an ugly method of handling those controls that don't support the property you want to modify, i.e. it justs skips the error. Wouldn't it be nicer to just reformat relevant controls? The ControlType property lets you do this, as in the following example which disables all controls except Labels:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    'ControlType constants you can use
    'acLabel - Label
    'acRectangle - Rectangle
    'acLine - Line
    'acImage - Image
    'acCommandButton - Command button
    'acOptionButton - Option button
    'acCheckBox - Check box
    'acOptionGroup - Option group
    'acBoundObjectFrame - Bound object frame
    'acTextBox - Text box
    'acListBox - List box
    'acComboBox - Combo box
    'acSubform - SubForm / SubReport
    'acObjectFrame - Unbound object frame or chart
    'acPageBreak - Page break
    'acPage - Page
    'acCustomControl - ActiveX (custom) control
    'acToggleButton - Toggle button
    'acTabCtl - Tab
    ctl.Enabled = (ctl.ControlType <> acLabel)
Next ctl
End Sub

Which is all well and good except what happens if you want to format an eclectic selection of controls of different types? Well you could use the control's Name property to uniquely identify each one and scroll through them all but how laborious is that if you have lots of controls to do this for? Plus if you make changes to the form, adding or removing controls, you have to change your code too. I prefer to make use of each control's Tag property, as in the following example which disables all controls with a Tag property of "X":

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = (ctl.Tag <> "X")
Next ctl

Union queries

Union queries offer a handy way of merging the results of two independent tables or queries. Here's the SQL - in this example, data from a staff table is being combined with data from a supervisors table to show all employees for a given region:

SELECT StaffId as IdNumber, Forename, Surname
FROM tblStaff
WHERE Region='South'
UNION SELECT SupervisorId, Forename, Surname
FROM tblSupervisors
WHERE Region='South'
ORDER BY IdNumber;

There are a couple of important things to remember when using UNION - these are:

• by default, no duplicate records are returned when you use a UNION operation; however, you can get round this by including the ALL predicate straight after the UNION keyword. This also makes the query run faster;
• all queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type;
• use aliases only in the first SELECT statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement (cf. IdNumber in the example above);
• an ORDER BY clause can be used at the end of the last query argument to display the returned data in a specified order;
• a GROUP BY or HAVING clause can be used in each query argument to group the returned data.

Two ways of checking whether a table exists

There are two ways to check whether a table exists in your database. The first, and easiest, is to make use of the MSysObjects system table. We'll make use of two fields: Name (the name of the object, in this case our table) and Type (tables have a type of 1). This function will return True if the specified table exists, False otherwise.

Function TableExists(strTable As String) As Boolean
    TableExists = (DCount("*","MSysObjects","Name='" & strTable & "' AND Type=1") > 0)
End Function

This is fine, but the DCount() function can be a bit slow. A slightly more long-winded solution is to enumerate through all tables in the database, stopping if one is found that matches the specified table. This is also slow the first time you run it, but is faster thereafter. Again, this function will return True if the specified table exists, False otherwise.

Function TableExists(strTable As String) As Boolean
    Dim db As Database
    Dim i As Integer
    Set db = DBEngine.Workspaces(0).Databases(0)
    TableExists = False
    db.TableDefs.Refresh
    For i = 0 To db.TableDefs.Count - 1
        If strTable = db.TableDefs(i).Name Then 'Table exists
            TableExists = True
            Exit For
        End If
    Next i
    Set db = Nothing
End Function

Creating a splash screen

Okay, so you've created you database application in Access but you don't really want everyone to see that annoying Microsoft splash screen every time you start up, right? Fortunately the solution is unfeasibly simple.

Let's say the filename of your database is my1stdb.mdb - create the image you want to use for a splash screen and save it as a bitmap with the same filename (i.e. my1stdb.bmp) in the same directory as the database. Lo and behold, next time you start the database up (assuming you open it directly from Explorer or a shortcut, rather than opening Access first and then opening your database from within it) your bitmap will be displayed instead of the usual homage to Bill Gates. Neat, but be warned - the bigger and more colourful the bitmap (and hence its file size) the more this will slow your database's start-up.

Okay, but what if you want to get rid of the splash screen altogether? Well (and I haven't checked this bit for versions higher than 97) you can't - but you can do the next best thing. Create a bitmap of size 1 pixel by 1 pixel - reduce the number of colours to 2, to minimize the size (so it loads as fast as possible) and name it as described above. This then pops up on the user's screen, but as a monochrome single pixel, who'll notice? This also has the advantage of keeping the database's start-up speedy, as a monochrome single pixel bitmap is only 66 bytes.

Validating ISBN

If you have any databases that store information about books, you'll want some way of verifying ISBN entries. I'm not going to elaborate more about how ISBNs work, suffice to say they use modulus 11 maths. If you're really desperate to know, you can read all about it here. Anyway, this function takes one parameter, the ISBN-10 value, and returns True if it's a valid ISBN, False otherwise.

Function CheckISBN(x As Variant) As Boolean
    Dim bLen As Byte, iCnt As Integer, iVal As Integer
    bLen = Len(x)
    If bLen > 10 Or bLen = 0 Then GoTo Invalid_CheckISBN 'Number is too long or too short
    iVal = 0
    For iCnt = 10 To 1 Step -1
        If iCnt <= bLen Then
            iVal = iVal + (iCnt * Mid(x, ((-1 * iCnt) + bLen + 1), 1))
        End If
    Next iCnt
    CheckISBN = (iVal Mod 11 = 0)

Exit_CheckISBN:
    Exit Function

Invalid_CheckISBN:
    CheckISBN = False
    GoTo Exit_CheckISBN
End Function

When to use an AutoExec macro

An AutoExec macro is a smart way of running a series of actions every time your database is started. Unless you bypass the start-up (by holding down the shift key), this macro will always be run when the database loads. If you want to perform actions not supported in macros, simply write them as a function in a module and use RunCode from within the AutoExec macro to call them. Also if, for reasons best known to yourself, you don't want to call your start-up macro 'AutoExec', call it something else and then use the /x command line parameter to reference it.

There doesn't seem to be a great deal more to say about AutoExec macros, does there? Well, I will point out that there is a faster alternative if (and only if) you also load a start-up form. If it is suitable to do so, consider putting a VBA equivalent to the AutoExec macro in the OnLoad event of your start-up form. This will speed up your's database load time if the form already has a module (i.e. there is already some VBA on the form). This is because Access only has to load the form and its module into memory, rather than the AutoExec macro, the form and its macro. Okay, so it might only save half a second but, as I have mentioned elsewhere, performance is key to me; as Paul Daniels used to say, every second counts!

Closing a report automatically if no data exists

There's nothing worse than running a report and it returning a blank page, especially if you choose to print the report rather than preview it. There are two ways round this conundrum: the first is to perform a manual check on the eventual record source the report will use before you open, and then to only open the report is the record count is greater than zero. This can be done using DCount() or with a recordset (preferred, as it is faster). However, I don't like this approach as, assuming there is data, it means running two queries - one to check there is data and a second when you actually open the report. I prefer to make use of the report's OnNoData event instead, like so:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No data matches the specified criteria.", vbInformation
    Cancel=True
End Sub

This way, when the report opens if no data is found up pops a message box to that effect, and the report closes. What could be simpler?

Determining a form's state

There are occasions when you will want to know, for example, if a given form is open. This can be quite easily done using SysCmd, a technique I quite often use to automatically close form A when closing B. In the example that follows SysCmd is used to check whether the frmSale form is open and, if so, closes it.

Private Sub Form_Close()
    'There are four states you can check for:
    'acObjStateOpen - open
    'acObjStateNew - new
    'acObjStateDirty - changed but not saved
    '0 (zero) - closed or non-existent
    If SysCmd(acSysCmdGetObjectState, acForm, "frmSale") = acObjStateOpen Then DoCmd.Close acForm, "frmSale"
End Sub

The fastest way to delete a table programmatically

Ever needed to delete a table in code? Maybe you created a temporary table and now you need to get rid of it in the neatest way possible. The most obvious way is to use DoCmd.DeleteObject, thus:

DoCmd.DeleteObject acTable, "tblTemp" 'tblTemp is the name of the table to be deleted

This does the job, but Access 2000 and above requires you to have the database open exclusively to do this. Why not make use of the fact that Access has a database engine (no kidding!) and try this slightly faster method:

DBEngine(0)(0).Execute ("DROP TABLE tblTemp;") 'tblTemp is the name of the table to be deleted

In either case you can run into problems trying to delete a table that is the parent in a join to another table, as this could lead to a referential integrity violation. Presumably you'll want to delete the related table too, so rather than worry about having to delete joins first, just delete the child table before the parent. There are no problems using the second method shown above to do this.

The vagaries of dates in queries

Consider this situation - you've written a query that returns all records that fall within certain date parameters. When you run the query it returns the correct data, and everything is hunky-dory. Now let's say you're building the same query programmatically; first you have to remember that dates need to be contained within hash symbols in the same way that strings have to be in quotes, but that's not the real issue. If you're not from the USA, chances are you local date format will be something like dd/mm/yyyy, whereas it is mm/dd/yyyy in the States. Did you know that when you use VBA to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of your PC's regional settings.

Consider this example, in which a form contains a field Param for entering a date in, and a command button cmdOK which, when pressed, displays a count of staff who started work after the specified date. The code for the command button would be something like:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Me.Param & "#")
End Sub

Looks okay doesn't it? But, at best, this will return the wrong results and, at worst, could crash your code. The date has to be parsed in US format, like this:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "mm/dd/yyyy") & "#")
End Sub

In the unlikely event that this still gives problems, use the following to completely negate the possibility of any ambiguity:

Sub cmdOK_Click()
    MsgBox DCount("*","tblStaff","StartDate>#" & Format(Me.Param, "dd-mmm-yyyy") & "#")
End Sub

About Access Relic

Morning all. I used to develop database applications using Access. I got pretty good at it too, to the extent that at one point I was in discussions about writing a textbook on the subject. I also used to maintain a website full of handy hints, tips and pointers for Access developers of all skill levels. I also offered plenty of code samples, some add-ins and freeware, and sample databases. It was a popular site, once upon a time.

Jobs change, and I don't get to do much with Access any more, I'm sorry to say. And the host I used for the old website pulled their free hosting, so that's gone too. I've still got all the old content though, so my plan is to release it gradually on here and ensure that it's still "out there" and available to anyone who might find it useful. Some of the content might be a bit dated (I doubt many of you remember Access 1.1 and 2) but I'll put the whole lot up, eventually, and let you decide for yourself.

The old website used to be called Accessory, but in starting this new blog it's time to acknowledge what I am now ... an Access Relic.

The small print, about you

The purpose of Access Relic is to share knowledge with other developers. However, to preserve the integrity of this site's content, and to follow common developer courtesy, the following conditions for use of code on this site must be observed:

• You may use any code here in any application you develop, provided a reference to this site is included in source code and associated documentation.
• You may not copy, redistribute or republish any source code code from this site in any form, including redistributing the code on the web, in zip files, or on CD collections. If you want to use something you find here please link users to the corresponding page(s) of this site.
• In other words, anything that lets you capitalise on my generous nature is just not on.

The other small print, covering me

The author of this site expressly disclaims any warranty for the code contained herein. The code and any related documentation is provided "as is" without warranty of any kind, either express or implied, including, without limitation, the implied warranties or merchantability, fitness for a particular purpose, or non-infringement. The entire risk arising out of use or performance of the code remains with you. Furthermore, in no event shall the author of this site be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or any other pecuniary loss) arising out of the use of or inability to use this code, even if the author of this site has been advised of the possibility of such damages. Because some jurisdictions do not allow the exclusion or limitation of liability for consequential or incidental damages, the above limitation may not apply to you.