Tony's Microsoft Access Blog : Tips

Is Nothing

Tony — Mon, 05 Oct 2009 11:09:04 GMT

I’m working on some largish updates to the Auto FE Updater and I wanted to ensure that the new routine which logs errors would properly handle the situation if there were errors in the error logging routine. A great deal of searching a dimly remembered discussion brought me to fellow Access MVP Dirk GoldGar’s posting on this topic. How to know when a Connection or Recordset is open in Programming

If Not rs Is Nothing Then
    rs.Close: Set rs = Nothing
End If
If Not gblLogdb Is Nothing Then _
    gblLogdb.Close
MsgBox "The following error occurred in procedure Log_NewSession of Module Log" & vbCrLf & vbCrLf & _
    Err.Description & vbCrLf & vbCrLf & Command()

Note that the standard error handling routine, including the sub/function name and module name is created by MZTools. An excellent free utility which I use all the time.

I was rather startled. This isn’t even documented in Access 97 help. Allen Browne has a page on this general topic - Nothing? Empty? Missing? Null?

InStrRev

Tony — Wed, 23 Sep 2009 21:32:04 GMT

I’ll be darned. I had no idea InStrRev existed in VBA code. It is new in Access 2000 so it’s been around for nine years.

I had to build this function myself in A2.0 or A97 to get the file name from a string which had the path and file. The code needed to find the right most occurrence of “\” back slash. I’ve been happily using my function ever since.

I actually learned something new about Access this year. <smile>

Queries and the 3061 "Too few parameters. Expected 1." message

Tony — Wed, 23 Sep 2009 04:32:14 GMT

Your query is working just fine referencing a form to select some records:

but when you run it in code you get the dreaded 3061 "Too few parameters. Expected 1." message.

One option is to evaluate the Forms![Form name]![Field Name] expression as per the following examples.

The below example is an execute query.

Set db = CurrentDb
Set qdf = db.QueryDefs("LEMBatchesRpt Append to temp table")
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i

qdf.Execute dbFailOnError

The below example is used in my standard routine which copies the contents of a recordset into an Excel spreadsheet. Due to the requirements of basing the openrecordset on a query to which I added a Where clause I created a temporary query and then did the parameter evaluation

strSQL = "SELECT * FROM [" & strQuery & "]"
If Len(strWhere) > 0 Then _
strSQL = strSQL & " WHERE " & strWhere & ";"
Set qdf = db.CreateQueryDef("", strSQL)
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i
Set rs = qdf.OpenRecordset(dbOpenSnapshot)

….

' Insert recordset into cells starting at left hand side and second row
' Using a copyfromRecordset is much, much faster than stuffing in the cells one at a time.
.range("A2").CopyFromRecordset rs

This is used in a generic routine behind the very flexible reporting form in the Granite Fleet Manager. When the user clicks on the Export Report to Excel command button I look at the record source of the report to fetch the query name. I then build the where clause and execute the above code.

An approach to sending text messages from within an Access application

Tony — Thu, 06 Aug 2009 20:55:34 GMT

Steve Schapel, fellow Access MVP, answered a question about sending SMSs with a page on his website. An approach to sending text messages from within an Access application I can see this capability being quite useful in some consumer facing applications requiring last minute notifications. One of Steve’s applications deals with school sporting events so last minute SMSs are very handy.

Now it is possible to send emails to the cell phone gateway email accounts. An example might be 78013456789@text.yourcellco.com. However this requires asking the person what cell phone company they use as the domain to the right of the @ sign is cell phone company specific. Also you run a risk of the cell phone deciding that your emails are spams. I would assume this is less of a risk if you use a SMS service which you pay for.

As an aside the SMS outfit that Steve has used with good success is in South Africa. While Steve’s clients in are New Zealand. I find it quite interesting that SMS service is reliable between continents.

Steps to think about when doing a data MDB back up, compact or zip.

Tony — Thu, 18 Jun 2009 22:16:27 GMT

I have a button on my Granite Fleet Manager app which allows the user to do a backup of the backend data MDB. I also have two other buttons which zip and email the backend data MDB to either themselves for backup purposes or to me for support purposes.

However any open forms or reports will lock the back end MDB file so a rename and compact won't happen.

As I do not know which forms and reports the user might have left open when they click on any of the buttons I run through some code that closes all open forms and reports, then run the code doing the rename, compact back to the same file name and, if requested do the zip and email.

' close all forms and reports
For Each frm In Forms
DoCmd.Close acForm, frm.Name
Next frm
DoEvents
For Each rpt In Reports
DoCmd.Close acReport, rpt.Name
Next rpt
DoEvents

DoEvents seems to be necessary to give things time to close. But maybe not..

Also note that I do keep a status form open during the above operation. I also do not have any list boxes or combo boxes with the row source set to a query or table referencing the data MDB on the form. And I don't have any open recordset or database variables for performance purposes.

You can double check that this code works by stopping the code just after the above and seeing if the ldb file on the data backend mdb no longer exists

Of course if there are other users in the back end then the rename won't work of course. Also see my Backup, do you trust the users or sysadmins? tips page for more info.

Redimming an array 1,000,000 times takes 4.63 seconds

Tony — Fri, 22 May 2009 02:31:30 GMT

The following question was asked at StackedOverflow.How do you redimension an array in VBA?. One response was some detailed code showing how to redim an array efficiently by doubling the number of elements. This took 0.41 seconds for a million records while redimming 1,000,000 took 4.63 records.

I don't think I've ever had over 100 elements in an array. Maybe 1,000 once or twice. And now I'm not at all concerned about efficiency while redimming.

Note that I only occasionally visit that website to answer questions. My biggest complaint is that you never know what comments or postings are new and haven't been answered. Very much unlike using a NNTP newsgroup reader rich client. But it's interesting to poke about to see what's new has been answered.

It's also interesting to read what bull**it has been spouted by folks who view Access with a lot of disdain. David W. Fenton is doing a fine job of answering such comments.

Access Demos, tutorials and basics

Tony — Sun, 26 Apr 2009 21:35:19 GMT

Thanks to a posting by Bill Mosca Access (2007) Demo Videos can be found at Access Demos

Also see Access Basics by Crystal fellow Access MVP. She also has some Video Tutorials

Some code to tame the Office Ribbon for Access (2007)

Tony — Sun, 26 Apr 2009 21:18:50 GMT

Albert Kallal, fellow Access MVP just announced the following:

"The office ribbon is an amazing new interface.

The problem with the ribbon is that takes a lot of code to change the ribbon at runtime.

Solution: With my ribbon class you can enable, hide, change label text, or pictures of a button with great ease and very little code.

This ribbon class allows you to use the simple "old style" syntax to enable, hide, or change the text of a button. eg:

meRib("Button1").enabled = true
meRib("Button1").label = "this is the new text for button1 on the ribbon"
meRib("Button1").picture = "HappyFace.png"

Some code to tame the Office Ribbon for Access (2007)

There may be some updates suggested by fellow MVPs so check back to that page on a regular basis.

Overflow error 6

Tony — Sat, 25 Apr 2009 21:32:43 GMT

A newsgroup poster had the following very standard lines of code

strSQL = "SELECT Mytable.Field1, Mytable.Field2, Mytable.Field3 FROM Mytable"
SET rs = Currentdb.OpenRecordset(strSQL,dbOpenDynaset)

The error was Overflow error 6.

He then replied a day later indicating that one of the variables in his code had been defined as an integer when it should've been a long. Now this is rather obscure although, once you think about it, quite understandable.

As a matter of course I always use Long fields for integers even if there will only ever be a few hundred records in the table. So I would like to think I would never have seen this message. But you never know.

A bizarre report problem

Tony — Tue, 21 Apr 2009 05:36:16 GMT

So Jim phones me with a very strange report problem as per the below portion of the report. It's impossible to have the PM010-03B as a duplicate as that is a no duplicates allowed index in the table. Besides where is the data in the field to the left and the 0.0% amount on the right hand side which is represented by the circles?

The query on which the report is based doesn't show that duplicate value.

Note that the X in 03AX in the first column and the XXXX as well as PM010-03A being twice was part of Jim's testing trying to figure out the problem.

Here is the report in design view. Simple, right?

While there is some code behind the form it had nothing to do with these fields. Commenting out the code made no difference.

The fields did not have the Hide Duplicates property set to Yes.

So we added some labels to various sections on the report. Only one record and one set of labels were printing and yet we had the duplicate line. Stranger and stranger.

Now we added a constant to the actActivityDescription control so it looked like "=[actActivityDescription]&" testing". Still only printed one line but this time the "testing" was only on the second line. Getting even weirder.

Suddenly the light bulb went on.

Somehow the user entered the PM010-03B MOD in the activity description on the form, hit Ctrl+Enter and then re-entered PM010-03B MOD on the second line. But all the user could see was one line so you can't blame them.

Truly bizarre. I doubt I'll ever see this one again.

Updating backend MDB tables, fields, relationships and indexes

Tony — Fri, 10 Apr 2009 22:00:01 GMT

Short posting

I like using the Compare'Em utility which creates the necessary VBA code to create/update tables, fields, relationships and indexes. It works by comparing your last version of the BE MDB with the latest development version of the BE MDB. While I don't need the Pro version nevertheless I paid the $10 for it. It does have it's quirks about which I've emailed the author.

Long posting

I have a one record table in the front end and back end MDB with the FE and BE version number. Whenever I open the FE MDB I run code which compares the version numbers in the current FE MDB and BE MDB. If different the code then executes subroutines based on the version on the BE. As each version of the is updated I update the version field to the successfully updated version. I also run the code if the user switches from one BE MDB to another.

I also execute queries in the midst of the above created VBA code to clean up data as required. For example I recently created a service techs table in the Granite Fleet Manager. In the past the service techs fields was just a string field on the service order table.

' Append records to ServiceRecords table
strSQL = "INSERT INTO ServiceTech ( stServiceTech ) " & _
    " IN '" & strDatabasePathandName & "' " & _
    "SELECT srServicePerson FROM ServiceRecords " & _
    " IN '" & strDatabasePathandName & "' " & _
    "GROUP BY srServicePerson " & _
    "HAVING (srServicePerson Is Not Null);"
CurrentDb.Execute strSQL, dbFailOnError

Note the IN statement which allows you to update tables in MDBs which aren't linked. Of course I had to then update the service order with the autonumber ID in the above just created service techs table and then delete the field from the service order table.

As you well imagine there are many variations of John Smith in the various service orders. John, John S, John S., and so forth. So I gave the users a form to clean up the data. (The High Rate record was for testing a high rate of pay and invoicing to ensure there was lots of room on various forms and reports.)

Mechanics aren't paid for their typing or spelling. <smile>

Which control has the focus?

Tony — Fri, 10 Apr 2009 21:19:47 GMT

Sometimes when you're tabbing around a form the focus disappears. Dirk Goldgar, fellow Access MVP, has a very useful and simple solution to determining which control has the focus in a newsgroup posting.

"If you want to know where the focus is at some point when it seems to have
disappeared, press Ctrl+G to bring up the Immediate Window, and enter the
statement

?Screen.ActiveControl.Name

After you press enter, the name of the active control should be displayed on
the next line. "

What is the best way to understand the tables,queries, relationships,etc?

Tony — Tue, 17 Mar 2009 20:28:48 GMT

KrispyData asked the following question in the newsgroups

What is the best way to understand the tables,queries, relationships,etc. I
am new to Access and taking over an entire database.

Philip Herlihy had an excellent reply well worth reading. Jeff Boyce, fellow Access MVP, also had some good comments. I'm sure the discussion will continue for a few days so keep checking back to the thread.

Any tips on working with a database that you did not create?

Note that KrispyData's original posting did not somehow make it to Google Groups.

Hiding 70 controls based on a value

Tony — Tue, 03 Mar 2009 03:34:43 GMT

At the blog entry Getting a list of control names on a form Nate posted the following comment:

Thanks for the great tip! It doesn't seem to include labels and command buttons, but it is still a great help. I have a form where I am hiding 70 controls based on a value, so this saved me some real time.

Thanks for the kind words.

But a better method might be to use the control's tag property. See Locking fields on a form in Microsoft Access for more details. One nice thing about the tag property as that you can use Shift + click to select multiple controls to set the property.

Using record selector on a form to select multiple records

Tony — Mon, 23 Feb 2009 06:32:26 GMT

A fellow Access MVP asked if there was a way of using the record selector to select multiple records and perform an action on them. Tom Wickerath, also a fellow Access MVP, responded with How to enumerate selected form records in Access 2003 and in Access 2002. I'd never seen the forms SelTop or SelHeight properties before so was quite intrigued. So I had to find a form in the Granite Fleet Manager where I could implement this functionality.

The code was fairly simple to implement. This gives us some more UI options which allow us to make Access continuous forms operate a bit like Excel worksheets in some simple respects.

Should the user be intrigued by the bulk move button and they didn't select any records they will get the following message.

Other UI Enhancements

Now this form allows the user to return the equipment to the main shop so I designed the UI with two radio buttons labeled Move and Return. You will note the new location field is disabled and some command button captions were changed.

It's possible for equipment to be issued to multiple temporary locations inadvertently. I created an additional All or Multiple Locations set of radio buttons along with the red diamond symbol. This allows the users to very quickly locate all such anomalous data and correct it immediately.

The red diamond is the letter u in the WingDings font. I chose the diamond as it has the most "colour" of any WingDings font entries and is a neutral symbol.

Now I'm not very happy with how the All and Multiple Locations set of radio buttons takes up so much room vertically. And it doesn't fit in well with the rest of the UI on that form or with other forms. But that was the best I could think of at the time.

Why Do So Many Programmers Use One Character Index Variables?

Tony — Mon, 29 Dec 2008 05:56:58 GMT

Why Do So Many Programmers Use One Character Index Variables has a very good point for those of us of a certain age accompanied by greying hair, bifocals and ... And yes I did learn Fortran as a teenager at the local college.

Although it was actually Watfiv, a student version of Fortran. How did Watfiv get it's name? Well, the first student version of the Fortran compiler was created by Waterloo University and thus called Watfor. Clearly the next version must be five so Watfiv it was named.

I must admit that just a week or four ago I used i as an index variable myself in a loop of some sort.

A very interesting solution for Code 39 barcode

Tony — Sat, 27 Dec 2008 05:53:07 GMT

Jim at a client found the following a very interesting solution for 3 of 9 bar code which was done as VBA code by James Mercantile. Barcode Generator for Code 3 of 9, Code 39, and Mil-spec Logmars. The VBA code reads the size of the control and creates white and dark lines accordingly. The advantage is that unlike with some free fonts you can have the control as tall as desired so it's much easier for the folks on the floor to hit the bar code especially from a distance.

While searching for the original source of the above VBA Code and it's author I also came across Code 39 barcode encoding and Code 39 Barcode FAQ and Tutorial. More techie stuff for the technical explanation of how the 3 of 9 bar code works.

The Using bar codes within a Microsoft Access application has been updated.

To James Mercantile thanks. A very interesting solution.

The case of the form which took 30 seconds to open

Tony — Tue, 23 Dec 2008 22:36:16 GMT

The client had two transaction file forms which took 30 seconds to open while a similar form took 3 or 4 seconds to open. I went through many of the items on my Microsoft Access Performance FAQ page and none made a significant difference.

The list of items Included "Subdatasheet Name property set to [Auto] should be [None]. " so I'm forced to conclude that while that tip may make a difference when using datasheet view it isn't helpful when using forms.

There are 740K records in the largest two tables of the app. The MDB is about 250 Mb in size.

To simulate their environment I placed the BE on a spare older laptop . I also opened the BE in another instance of Access as performance significantly decreases with the second user into the database. I was then able to get performance times to approximate the client reports.

Note that just opening the form wirelessly caused performance to decrease by a factor of four or five. Although Access frequently crashed. Note that I'm running the 54 Mbps wireless networking version so this number quite surprised me. I'm thinking that the encryption might be the bottle neck there. And is the bottleneck at the consumer grade WAP that I own or in the wireless networking device or the CPU doing the decryption?

In this case the problem was in the form load event somewhere as I determined by putting debug.print at beginning and end of form load. For example:

Debug.Print "BuildAndApplyFilter - " & strFilter & vbTab & Now

Note that you must put the debug.print at the very end of the routines you want to test as well. Otherwise you don't realize that the last line in the routine is the one that's causing the slowdown. <sigh> Ironically that line was commented a year or three ago "'Note: This sub also calls BuildAndApplyFilter - a small performance hit." Yeah, well, it's a small performance hit when the BE is local. A significant performance hit when on the network.

I then sprinkled debug.prints in the VBA code as appropriate to narrow things down.

Turns out the problem was due to a form filter based on an Inactive boolean field on a master Job table which had 792 records. Indexing that boolean field fixed this particular performance problem.

Now given that this forms recordset automatically opens only the last two weeks worth of transactions that field really isn't necessary as a filter. The users may not be that likely to close a job within two weeks of the last time transaction against it. And even if it is closed well, so what. Maybe it's a bit misleading to have some transactions entered yesterday, the job is closed today and now those transactions don't appear on the form. You could argue this case both ways. <smile>

Standard NotInList VBA code

Tony — Sun, 30 Nov 2008 23:18:19 GMT

I finally got around to creating a Using NotInList in a combo box page at my website. I keep forgetting to make up same sample code and re use it as I don't care for the MS sample code in the Access help. A bit too verbose and it doesn't show how to deal with adding records to tables and then opening up a form. So here's the code I just setup as a sample. Critique away.

Using pictures on a form and report

Tony — Sun, 30 Nov 2008 22:23:36 GMT

The following question was asked in the comp.databases.ms-access newsgroup.

I'd like to add a picture in a report's detail section where the path to the picture is from a table.

Incredibly easy. The same code works on a form as well. Using the Toolbox drop an image control on the reports detail section. Note that you will have to follow the dialog and actually insert an image. But then go to the Picture property on the Format tab in the property sheet and delete the actual file. You will probably want to set the Size Mode from clip to zoom. And rename the control to something meaningful.

Then in the report detail section in the On Print event you want to add the following lines of code

    If <logic if file available to view> Then
        Me.PhotoImage.Picture = PhotoFilePath
    Else
        Me.PhotoImage.Picture = ""
    End If

PhotoFilePath being whatever logic you want to use to figure out the file that you want printed.

You don't want to do this logic in the Format event as Microsoft has told us, via Stephen Lebans, that using that event can lead to memory leak problems. Or you can use BMP images. Which is rather impractical.

The reason for extensive bloating in Access when JPG or other graphic files are permanently embedded in forms or reports is that Access converts those file formats to BMP format. This made sense back in Access 2.0 days is viewing a JPG file was very slow as it requires lots of CPU cycles. 80386 computers running Windows 3.1 were rather slow at that. BMP format meant that you basically read the large file directly into the graphics card memory. In Access 2007 the Access product group have updated that logic so the BMP file format isn't created any more.

Shameless plug: I have a number of reports in the Granite Fleet Manager which print a "primary" and a number of "secondary" photo's for each unit. See Granite Fleet manager Main Menu a screen shot and Service Order Details (with Photo) for a sample report.