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