Making a search form much easier to use

SearchFormThis organization, among many other things, provides counseling.  The receptionist books the appointments and over the years they’ve accumulated a lot of names.  The counseling manager stated they were cleaning up the data and inactivating individuals that hadn’t been seen in over a year.   So I decided to help their staff a little.  Actually a lot.

Below is the the revised search form.   I added the circled areas.  

Last assigned date

This shows the last appointment date the individual had with the organization.   Notice how vertical scroll bar button is a long ways down on this list box.  This shows that many Active & Intake individuals have never made an appointment.  And see how some active individuals last appointment was in 2005.  Definitely overdue for some data cleanup.

Now I always have trouble with this kind of query as I don’t do it very often.  So I always have to go looking it up.  And each time I do I say to myself “Tony, record this as you’ll need it in six months again.”  Well here it is.



The key things here are you must create a Totals query that you will join to another table or query that is based on the client appointment table.  (I did not design this system so don’t blame me for the table names.   And yes, folks much more talented in SQL can probably figure out how to create a sub query but I’m not one on those.)  The primary key, in this case, the ClientNo is set to Group By.   As we want the last assigned date the Max of the AssignedDate is chosen along with sorting the field in descending sequence

I decided to rename the AssignedDate field myself as otherwise the name in the below query would’ve been MaxofAssignedDate.


This is the query for the listbox above.  Note the right join to the above query.   The right join ensures that folks without ever having made an appointment will appear on the listbox.





Sort By

The Sort By allows the users to view the folks which the oldest appointment first so it’ll now be quite easy to inactivate folks.    In the frames On Click event I have the following code.

Call FilterAndSortClientListComboBox

I prefer to call a subroutine because the record source of the list box is now updated in three places.  The client name search field, the client status combo box and the Sort By filter.  This way the three search and filter fields all call one subroutine.   We’ll get to that subroutine shortly.

Client Status

The client status combo box is based on the client status table but with some extras as you can see.   The extras were done using a Union query.

SELECT tblClientStatus.ClientStatusNo, tblClientStatus.ClientStatus FROM tblClientStatus
UNION SELECT -1 as Expr1, ‘ (All)’ as Expr2 From tblClientStatus
UNION SELECT -2 as Expr1, ‘ (None)’ as Expr2 From tblClientStatus 
UNION SELECT -3 as Expr1, ‘ (Active & Intake)’ as Expr2 From tblClientStatus
ORDER BY tblClientStatus.ClientStatus;

Of course a SELECT is ridiculous in this circumstance but you have to follow SQL rules.  Do ensure these dummy values come from a table with very few records.  I put a space before the bracket in Expr2 so these sort first and it’s quite obvious to the user that these are special values.

The FilterAndSortClientListComboBox subroutine is also called in the combobox’s AfterUpdate event.

The FilterAndSortClientListComboBox  subroutine

Sub FilterAndSortClientListComboBox()

Dim strSQL As String

    Select Case Me.cboClientStatusFilter
    Case -3 ‘ 3 & 1, Active and Intake – This is the default
        strSQL = "SELECT * FROM qryClientListCS " & _
             "WHERE ClientStatusNo=1 OR ClientStatusNo=3"
    Case -2 ‘ None
        strSQL = "SELECT * FROM qryClientListCS " & _
            "WHERE ClientStatusNo Is Null"
    Case -1 ‘ All
        strSQL = "SELECT * FROM qryClientListCS"
    Case Else
        strSQL = "SELECT * FROM qryClientListCS " & _
            "WHERE ClientStatusNo=" & Me.cboClientStatusFilter
    End Select

    If Me.frmSortBy = 2 Then _
        strSQL = strSQL & " ORDER BY [Last Assigned Date]"

    Me.lstClient.RowSource = strSQL

End Sub


These changes didn’t take too long.  But they should make a significant difference in the ease of use of this system.   One of my rules is to always spend some time with the key users of any system.  Not just the managers but the users.   I frequently break that rule these days especially when doing remote programming.  But I’ve designed enough systems that I arrogantly feel I can put some ease of use features in with just a few sentences from the users describing the pain points.

(Well, it took longer than expected as the original developers of this system were not Access gurus and it was tough figuring out what they were all trying to do. They were not taking advantage of Access’s bound forms in many ways. )

The Auto FE Updater is no longer free for use

Despite what I’ve stated on my website for the past decade I’ve decided that going forward the Auto FE Updater is no longer free.

Why?  Please visit the Licensing FAQ page.

Pricing – Please visit the Licensing page

I also added a Editions summary and feature comparison page


For the next two weeks if you purchase a Standard Edition you will get a license for the Enterprise Edition.

In addition if you purchased the Enterprise Edition in the past or were given an Enterprise Edition because you had purchased a voluntary license you will continue to get annual subscription updates for half the previous licensing fee of the Enterprise Edition or $87.50.

Having reference problems?

Left and string not working?   Other bizarre problems?   Do you have any references besides the basic three or four?  Are you sure you need them?  Write down the path and name of the extra ones (or put the following code in a module and execute the code), delete from the references list and Compile and Save All.   Keep any necessary references and ensure they are distributed to the target system.  

The following code uses late binding and gives you the full reference name such as you see in the references window.  (Early binding would’ve required the Microsoft Visual Basic for Applications Extensibility reference.  So lets make things simpler for the users.)

Sub ViewMoreReferenceDetails()

Dim refIDE As Object

    For Each refIDE In Access.Application.VBE.ActiveVBProject.References
        If refIDE.IsBroken = True Then
            Debug.Print "Broken, GUID – " & refIDE.Guid
            Debug.Print refIDE.Description & " – " & refIDE.Name & " – " & _
                refIDE.Major & "." & refIDE.Minor & vbCrLf & _
                "       Location – " & refIDE.FullPath
        End If
    Next refIDE

End Sub

Note that, to my knowledge, you can’t get any information about a broken reference other than the GUID.   As this information for missing reference is included somewhere inside the Access database file I don’t have any access to the information that MS displays for a broken reference.   And it’s not really needed for this kind of trouble shooting.  It’s enough to know there is a broken reference.

To execute this code copy into a VBA module or create a new VBA modle.  Copy and paste this code into the module.   Ensure your cursor is within the subroutine and press F5. Then Ctrl+G will take you into the Debug/Immediate window to view the results.  You should now see a list such as

Visual Basic For Applications  – VBA – 4.0
       Location – C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL
Microsoft Access 11.0 Object Library  – Access – 9.0
       Location – C:\Program Files\Microsoft Office 2003\OFFICE11\msacc.olb
Microsoft ActiveX Data Objects 2.8 Library  – ADODB – 2.8
       Location – C:\Program Files\Common Files\System\ado\msado15.dll
Microsoft DAO 3.6 Object Library  – DAO – 5.0
       Location – C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll

For a very detailed page on reference problems see

Updated 2010-10-22.  Thanks to fellow Access MVPs Tom van Stiphout for pointing this out and to John Spencer for pointing out an easier way of running the code.

“the company’s IT manager stored the backup discs in his office.”

“Chapman’s had just come off a great summer season, on Sept. 4, 2009, when fire sparked by a welder’s torch levelled its offices and 85,000-square-foot production facility. Eleven production lines and all the company’s records were gone. According to Ashley, the company’s IT manager stored the backup discs in his office. “

Baptism by fire: Chapman’s triumphs

I hope he’s flipping burgers.

Queries On Steroids … Part IV

I was reminded of this article in a discussion in a newsgroup/forum.   As I’m always interested in the hard core details of anything computer related but especially anything to do with Access or Jet I thought I’d share this with you.

Queries On Steroids … Part IV talks about Jet’s ShowPlan and ISAMStats

I will also add an anecdote at a clients.   They had one table with 800K transaction records. Performance wasn’t the best in the main inquiry screen which pulled in just the last two weeks transactions.  The query had joins to about eight or ten other tables to display data.  One of these tables was a job table with about 500 records as well as a Boolean active field.   I had a vague memory of a newsgroup posing about ten years ago where someone mentioned  indexing a Boolean field made a big difference so I tried that.   And the form open dropped from 30 seconds to five seconds.

(The client has since upsized the database to SQL Server so now the form open is just about instantaneous.)

Using the Auto FE Updater to distribute an Excel Add-In

I received an interesting email showing some out of the box thinking when it comes to using the Auto FE Updater.  Clif wanted to occasionally distribute an Excel Add-In to another person in the office.   So he decided to copy the Excel Add-In on the server and use the Auto FE Updater to distribute the updates.  As he only makes sporadic updates and there’s only one person using the Excel Add-In he chose to have the utility not create any shortcuts on the users system.  Instead Clif is going to email the user using the Create User Email option whenever there is an update.

I then suggested that he consider using the Start Method = Display message showing the number of files copied.   Which worked perfectly.   After all the other guy doesn’t need to start the Excel file or such. 

Now he is going to have to set the Excel Trusted Locations for the Excel Add-In but for only one person it’s not a big deal to do manually once.

Clif, thanks for the email and the out of box thinking.

Added:  Clif posted his experiences at  How to deploy my new addin

The red splotch on my avatar cheek

The following comment was made in the Auto FE Updater group on LinkedIn.

“I’ve spent years studying Tony’s iconic face on various fora, and wondering about that big strawberry on his cheek … now, LinkedIn’s larger avatar allows me to discern the Canadian flag.”

I had to chuckle because I can see how it would be very hard to figure that out.  See this link as an example.   So here’s the full sized image.  


I found a website somewhere, somewhen that you could build faces.   So I did and chose to add the Canadian flag because I’m as patriotic as any other Canadian.  (Which is very low key compared to Americans but I digress.)

Note that my photo is not on the Internet anywhere including in Facebook.   There are four individuals on the Internet who really, really don’t like me because I post corrections to their comments.  I’ve had very ugly postings made which were utterly false.    The local police have been informed who the suspects should be considered at the top of the list if my death is considered suspicious.

Auto FE Updater – Seven day trial license for the Enterprise Edition


I’ve been thinking that it’s one thing to see static screen shots at the website.  But it’s quite another to see your own data in the various Enterprise Edition forms.   So email me at the contact page and request a trial license.  You don’t have to give me your company name as I’ll just put your name in the license name field.

I will NOT give your name or email address to anyone else.  I might send out an announcement email a few times a year but probably not.  I figure between the Auto FE Updater reminding you to check for updates and that you can subscribe to this blog entry ought to be sufficient.