Tony's Microsoft Access Blog : VBA

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.

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.

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.

Humour: Mac users and VBA

Tony — Tue, 07 Apr 2009 04:51:36 GMT

"We've solved the problem with lack of VBA support on Macs, and it was very easy: we got rid of the Macs. Typically our mac users were pretty expensive to support, being on average less skilled and lacking in knowledge of basics like what a file is, how to search the web, etc, and additionally were routinely late to work and were relative to the rest of our staff unprofessional in demeanor. So in addition to getting rid of the Macs, we got rid of the Mac users.

Trust me, this methodology can clean up lots of your companies processes and deadwood employees. Give it a try!"

http://blogs.msdn.com/excel/archive/2008/01/16/clarification-on-vba-support.aspx#7149910

From a blog about the future of VBA in Excel.

"Object or class does not support the set of events." error message

Tony — Mon, 06 Apr 2009 01:54:08 GMT

I start getting the following message when opening a form after doing some minor changes to it.

"The expression On Open you entered as the event property setting produced the following error:
Object or class does not support the set of events."

Commenting out the code in the OnOpen event, compiling, uncommenting and compiling didn't help. Neither did decompiling which startled me.

However saving the form with a different name fixed the problem. Bizarre.

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.

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.

My muddiness when it comes to OpenArgs

Tony — Wed, 26 Nov 2008 21:37:03 GMT

I was getting an syntax error message when I clicked on a New button on the Equipment Quick Finder form. The equipment form in the Granite Fleet Manager executes a reasonable amount of code on startup.

I had added that New button to the Quick Finder form when I created it and all was well. Trouble is I added some code in the Equipment form that required the OpenArgs start with "New" when the record did not exist. And now the code was causing me troubles. While I fixed the immediate problem I realized I had a larger issue.

When testing I realized that if I'm updating a piece of equipment then the OpenArgs will legitimately be empty.

So I added the following code in the forms Open Event.

If IsNull(Me.OpenArgs) And Len(Me.Filter) = 0 Then _
MsgBox "OpenArgs is null and Filter is empty."

I also did a Find through all the code looking for OpenForm "Equipment" just to ensure I didn't have any other problems hidden away somewhere else.

The lesson is that if you are depending that OpenArgs have values in it ensure that values are always present. Pop up a message for the programmer to give him (or her) a kick.

This is a follow up to the Watch out for Null OpenArgs blog posting. (And I sincerely hope my clients never chip in to purchase the chair mentioned at Microsoft: We Share Your Pain.)

I just love the Access Developers Handbook

Tony — Wed, 19 Nov 2008 10:41:00 GMT

I recently added the ability to view unit photo's on the main menu of the Granite Fleet Manager. However if the user doesn't have photos or doesn't want to view them on the main menu then I wanted the form to be centered. I figured that the Access Developers Handbook would have the code to centre a form when it was shrunk and I was correct.

It took much longer to comb through the relevant chapter finding the code I needed than to import, implement and test the code. What an efficient use of my time! While I don't use the books much lately every time I opened them up I saved the price of the books. And I used the books lots years ago.

My understanding is that the Sybex figures there would be an insufficient market for an Access 2007 version. <sigh> However I suspect almost all the code will work quite nicely in Access 2007. I also think the authors have generally moved on to other technologies.

All I had to was import a class module from the appropriate MDB on their CD, add four lines of code and the resized form nicely centered.

At the top of the VBA code

Private mfi As FormInfo

In the load event:

Set mfi = New FormInfo
Set mfi.Form = Me

and in my subroutine which decides if I'm going to display the photos on the menu or not:

mfi.Center

Below are the two menu forms.

The code behind the form to shrink the form is fairly simple:

Me.InsideWidth = 5.6 * 1440

The 5.6 being inches as I'm not a metric person and 1440 being the number of twips in an inch.

Now you will notice that the backup (floppy diskette image) and the stop buttons were also moved. I simply manually moved them left to suit and recorded the left position of the controls. Then I put them back on the right hand side of the form and inserted the following code.

Me.cmdBackup.Left = 4.6667 * 1440
Me.cmdQuit.Left = 5.1458 * 1440

The program randomly selects the photo from the recordset of photos that are present in the system when the main menu is opened. You can them scroll back and forth among the photos. This feature is not that useful from a technical or user viewpoint. However I figure it will look neat when the folks are looking at the demo.

(The use of the word neat may help figure out approximately my age. If you pay attention to those kinds of things that is.)

Edited 2009-05-19 to change a URL from a spammy domain to a working domain.

Watch out for Null OpenArgs

Tony — Tue, 18 Nov 2008 08:08:12 GMT

Well, I gave the problem away with the subject line. I had the following code which I thought was working quite nicely.

If Left(Me.OpenArgs, 3) <> "New" Then

But then I suddenly noticed when viewing a screen that a few fields were empty that should've have some data in them. Some poking about and I now realize the following should've been in my code.

If IsNull(Me.OpenArgs) Or Left(Me.OpenArgs, 3) <> "New" Then

Quickly looking through the rest of the code in the database for OpenArgs shows that was the only problem. Never mind that it was on the most used form. <sigh> And I have no idea how long that code has been broken. <bigger sigh>

OTOH those fields will be removed on my next release anyhow as I'm going to a much more sophisticated method of tracking all the preventative maintenance work that should be done to a piece of equipment. Rather than only tracking oil changes as is currently done.

Adding a record count to a tab caption on a tab control part 2

Tony — Sat, 08 Nov 2008 03:09:40 GMT

In my previous blog entry on this topic I stated "I also didn't bother putting any logic in to update the count of notes as the user was entering them. Hmm, for the sake of neatness and tidiness though I should." So I decided I'd better do that. Then as I got to thinking about this I also need to update the count when I delete the record as well. (I recalled my friend Jim gently reminding me a year or two back on some similar logic elsewhere that I need to also consider record deletions.)

However this logic is in the Part Notes subform. So I used the trick in Calling a subroutine on another form blog entry to call the same routine on the parent form as I had in the previous blog entry. These are in the subforms AfterUpdate event and the Del(ete) command button that I added to that subform. I had to rename the parent form to remove the spaces from the form name as documented in this referenced blog entry.

Mind you I could've also put the logic into a VBA module and I wouldn't have needed to call the module from the form.

I also really gotta go through the entire app and ensure that, where appropriate, all sub forms have a delete button.

Adding a record count to a tab caption on a tab control

Tony — Wed, 05 Nov 2008 06:10:33 GMT

I decided that it would be useful for someone looking at a particular part in the Granite Fleet Manager to know if there were any notes associated with that part. The idea being that there seldom would be any such and the mechanic wouldn't have any reason to click on on that tab. So I should visually indicate that this part has some notes.

Why does a part need notes? Not if it's 0W30 motor oil But if it's an custom part for a machine built overseas and the only source of parts is one distributor at the other end of your country or your continent that took you hours to locate then having notes is a good thing.

The code is quite simple. The page on the tab control is named pagPartsNotes.

If Not IsNull(Me.pID) Then
    strSQL = "SELECT Count(PartsNotes.pnID) AS CountOfNotes " & _
        "FROM PartsNotes " & _
        "GROUP BY PartsNotes.pnPartsID " & _
        "HAVING pnPartsID=" & Me.pID & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.EOF Then
        NotesCount = 0
    Else
        rs.MoveFirst
        NotesCount = rs!CountOfNotes
        Me.pagPartsNotes.Caption = "Notes - " & NotesCount
    End If
    rs.Close: Set rs = Nothing
End If

The first line If Not IsNull(Me.pID) Then is present to see if the user is adding a new record.

Also note that if you add a new record from this form you'll also need to reset the Notes tab caption. I also didn't bother putting any logic in to update the count of notes as the user was entering them. Hmm, for the sake of neatness and tidiness though I should.

But then I thought about the mechanics using the system and figured that maybe this was just a bit too subtle for the folks. (Jim, an excellent person at a client who has made a large number of UI suggestions to me over the years, is good at questioning my assumptions and who reads this blog, is now grinning.) Now don't get me wrong. The folks using the system are great people but noticing subtle differences on computer screens just isn't their strength.

So I decided to give Stephen Lebans Tabs.zip a try. Trouble is his solution requires fixed tab widths which I'd prefer to not use as I'll be adding more tabs when I add inventory to the system.

Given that Google and Google Groups are useless for searching I asked around. Former Access MVP and current Microsoft tester Jeff Conrad told me about his resource page CSD - Tab Control Colors.

Hmm, those solutions are decent but a bit complex for right now. So I'm going to think about this. And I just might add a text control that contains a note along the lines of "There are x notes present." which is only visible if there are notes.

Referring to a control's label

Tony — Sat, 04 Oct 2008 20:58:06 GMT

rkc posted a snippet of code in a thread titled Access an attached label in VBA on how to refer to a control's label using some syntax I've never seen used in exactly that fashion.

With ctlCurrentControl
.Controls(0).BackColor = lngYellow
End With

which I then extended to

Me.<controlname>.controls(0).BackColor = lngYellow

In this thread the original posted wanted to change the background colour of the label when the user entered the control.

I could've used that logic to change the control's label caption in my Granite Fleet Manager app. I allow the user to select either miles or kilometers as the unit of measure. I also allow the user to change the units permanent or temporary location to suit their business. For one client the temporary location caption is Job Site while another is Farm. On those I renamed the label to be something like lblControlName and referred to it directly such as

me.lblControlName.Caption = SomeValue

J Street's Access Relinker available for download

Tony — Fri, 03 Oct 2008 03:04:57 GMT

See J Street's Access Relinker by fellow MVP Armen Stein.

Also see the Access Web's Relink Access tables from code and Allen Browne's Reconnect Attached tables on Start-up as well as my general approach on this topic for shrink wrap apps Relinking Tables.