Tony's Microsoft Access Blog

A Training Issue

A Training Issue   <chuckle>  Folks much younger than me won't necessarily get this one as they've never seen punched cards.

I recall a client nearly thirty years ago telling me how they'd lose data if the tapes snapped.  They'd splice the tapes back together but would be missing a block or two of data.

Standard NotInList VBA code

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

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.

I never knew that - view linked table file path and name

I was in the table view in Access 2003 and accidentally hovered over a linked table.  A tool tip (mouse over tip) displayed itself a second later telling me the backend linked MDB file path and name.

Cool!  I never knew that.

I should add that I go to great lengths to ensure a user doesn't have a problem with linked tables.   When the user opens the app I ensure that all tables have the same linked path and file name.   Just in case.  You never know when something really, really weird could happen when relinking tables.   And I'd hate to have to debug a customers problem where a few tables are pointing to a demo MDB and all the other tables are pointing to their live data table.

Other things I do on start are opening a recordset based on a particular table and check for a particular field in it.  If that fails I assume the BE isn't linked properly and start up the linking table logic.

Note that I install the Granite Fleet Manager into the users Application Data folder and  sub folder of Granite Fleet Mgr using Inno Setup.  I also install the Demo MDB and the demo graphics files into that same folder.  However the live empty Data.MDB gets installed into My Documents\Granite Fleet Mgr.

I then check to see if there is a license key file in the users My Documents\Granite Fleet Mgr folder.  If so I then look to see if there is only one data MDB file in that folder. I inform the user and ask if they want to relink to that table.  If there are two or more data MDBs then I inform them and pop up a relinking form.  This handles the situation where I've converted their data from a spreadsheet and emailed the user that MDB and the license key file.

Note how the number of Units and Service Orders are displayed in the list box.  I've found this to be of great value when working with clients and somehow multiple versions of the BE data MDB are floating about.

Thanks to Jim, a friend and client, who suggested adding the Selected Data File Folder field.  If folks are interested and understand what is all going on then that's all the better.   That field can be double clicked and Windows Explorer opens to that folder.  The code behind that double click event for the Selected Data File Folder is:

Call RunShellExecute(tt_StripFromLastRight(CurrentDb.Name, "\"))

tt_StripFromLastRight is a function which removes the "\" and everything to the right leaving just the path.

If nothing found as per the above paragraphs then the linking table logic first examines the current FE path for the Granite Fleet Mgr.INI file. If found I then relink to the BE MDB as specified in that INI file.  This handles the situation where the app has been previously installed on the system and the INI file is pointing to a network file location.

If that file isn't found then I assume this is the first time the user has ever run the app and link to the demo MDB.

I also display the path and file name of the BE MDB in the About screen.  As well as the current FE MDB/MDE file path and name.  

I also put in a double click event on those two fields which the user can then open up Windows Explorer.  Do you really want to tell a user how to use Windows Explorer to navigate to a path that Windows by default hides from the user?  No, I didn't think you would either.

Still reading?   All this when all I meant to type was the first few paragraphs. <sigh>

New version of the Granite Fleet Manager

Ten year annual maintenance costs report
Email feedback form
Added meaningful error 2114 screen
Minor UI improvements and bug fixes

http://granitefleet.com/

Commissions paid to sales agents.

How are your personal backups?

Including all your photos? And your Outlook and Outlook Express files which are, last I read, stored in a sub folder of your Application Data folder and not your My Documents folder?

"The GpCode family is a dangerous form of malware which can permanently destroy files by encrypting them.  The capability for AV products to de-crypt files vary and can't be relied on in all cases, especially when complex encryption techniques are used. "

New GpCode Ransomeware attack encrypts victims files

No, no, no.  Do not leave this until tomorrow.   Grab a coffee and start copying files to a DVD.

My muddiness when it comes to OpenArgs

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.)

Meteorite search update

Very interesting.  Meteorite search update  It was estimated to weigh 10 tonnes when it entered the atmosphere at 14 km/sec.  Possibly football sized chunks. 

I sure hope the local farmers aren't troubled too much by *rsehole souvenir hunters but I know they will be.  They might as well get any cattle, horses and livestock off the fields until the snow falls.  And even then there'll be folks out there leaving the fences open or just plain cutting the fences.  I'd like to see the scientists get the rocks first before the folks who want to sell chunks.

Microsoft Math - $20 - Wow

Microsoft Math 3.0   Wow.  3D graphing.  For 20 freaking dollars.

When I was a teenager (Yeah, yeah, some of you are clicking on the next blog entry button.) I was teaching myself WatFiv and a bit of PL/C and S/360 Assembler at the local college after school hours.  I was doing some graphing using some libraries of code which meant printing out the graphs using *+- and | on a 132 character dot matrix line printer.   I was using punched cards and did a very little bit of work on a 24x80 text screen.  No such thing as Windows or even DOS then. 

Although the Altair came out soon thereafter.  8 Kb of RAM I vaguely recall.  Or maybe it was 256 bytes of RAM.   (Ah I'm right.  According to Wiki It came with 256 bytes of RAM and you could order a 4 Kb RAM board for half the price of the initial computer.)

I was also doing some graphing with equations in high school where you had to put in the values of x and hand plot the values of y.   Took minutes for each equation.  I was thinking wouldn't it be so neat if we could plot the graphs on a computer screen, change a value in the equation and see instantly how the graph would change.   The equations would become so much more intuitive rather than being just images in the text book or smudges of pencil on graph paper.  Remember graph paper?

And now you get 3D graphing for $20.  Wow!

(Note the reference to Microsoft at the bottom of the Altair Wiki page.)

Have software patents been thrown out?

Slashdot article - Groklaw Says Microsoft Patent Portfolio Now Worthless

If so I think that's excellent.  Too many of the software patents we see mentioned in the software media are ridiculous and obvious.   Frequently the basics of the patent have already been done years before the application.

Of course who wouldn't be in favour of causing trouble for the scum sucking, bottom feeding lawyers who are suing folks based on such.

And as I'm reading the Groklaw article about tax strategy patents referenced in the above article I'm shaking my head in disgust.

The funniest comment on the fireball

Gary: "I found it, it was melted twisted metal in the shape of a wrench and you could just make out NASA engraved into it. The whole thing was encrusted with what looked like a burned up tool bag"

I read the above while reading some comments on various news web sites attempting to figure out approximately where the fireball/meteor might've hit the ground. 

My tentative guess would somewhere in the Macklin area of Saskatchewan.

Email feedback

I got to thinking about how to make it easier for folks to send me feedback good, bad or ugly.   And I realized it would be quite simple.  While my software does have SMTP email code this requires users to configure the system for their email account, SMTP server, etc, etc.  So I decided to use their current email client.

The code behind the buttons is ridiculously easy.

The first button:

DoCmd.SendObject acSendNoObject, , , TechSupportEmailAddress, , , _
    "Granite Fleet Mgr comments", , True

Where TechSupportEmailAddress is a global constant that has my email address in it.

The second button uses the the API code from API: Copy variables/control contents to memory and has the following single line.

Call ClipBoard_SetText(TechSupportEmailAddress)

I should've done this a long time ago.   And it's quite easy to drop into other systems so I'll be including it from now on whenever I build a custom system for someone.

I was walking when I saw the light from the fireball

5:30 PM and It was dark outside.   I saw the very bright light shining through the trees onto the ground and my first momentary reaction was a nuclear bomb had gone off.  Then I saw the shadows move quite a bit so I figured the *rseholes in the party house down the street had set off a flare but I didn't hear anything.  So I kept on going home.

The light wasn't as bright as sunlight but it was definitely three or five times brighter than moonlight.  It was also a very stark white or a welding arc light white.   

I didn't see the meteor myself as it would've been behind a 2 story house I was just talking by.  I only saw the light on the ground.   And there was no way I was going to look towards a nuclear bomb blast.  <smile>

Visit Mysterious bright light seen over Edmonton for more of the story. Visit Video: Global Edmonton reports for some not the greatest quality videos but still spectacular.

Interesting.  According to Wiki the official definition was fireball and not meteor or meteorite.  So I changed the title accordingly.

I just love the Access Developers Handbook

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.

New version of the Granite Fleet Manager available for download

I just uploaded the latest version of the Granite Fleet Manager to the website.

• Added customer invoicing including labour rates, markup percentages on parts , parts types and external invoices and sales taxes.   Added internal invoice number and customer purchase order number to service order so the administration staff can ensure that the service order was invoiced.  Extensively reworked the service order forms.  
• Selecting reports by individual customers now shows your price to them rather than your cost
• Added mechanics comments to service order. 
• Added logo to printed service order.  
• Added Service Order Quick Find so you can do extensive searching
• Updating the hourly rates for Service Techs will ask if you want to update the service orders as well to get a more accurate cost.
• Temporary locations will now appear on the overdue items screen and report
• Added an overdue items report
• Export reports to Excel
• Random equipment photos on main menu
• Many minor UI improvements

The customer invoicing was a very interesting feature is that touched many forms and reports through out the system.  I wanted the screens and reports to work equally well if you were working on your own fleet of equipment or on someone else's equipment.   One difference of course is that the customer name should appear where appropriate.  And of course you don't want your customer knowing your cost so they should only be seeing their price. 

The exports to Excel was quite interesting in many respects.  I'll probably create a page on my main website with all my Excel code.  But some things I'm doing is after the reports recordset gets exported to Excel is I delete all fields that are long integer and autonumber keys.  Or long integer and end in ID.   All primary and foreign keys I use are autonumber and end in ID.   One particular column, if it's present in that Excel spreadsheet, is bolded and highlighted in red.   Other columns were deleted if they were required for reporting sequencing purposes but not required in the Excel spreadsheet.

A friend who is a heavy duty mechanic and instructor sat down in front of the system for an hour.  It was an eye opener as I watched how he moved around the system.  I made lots of little enhancements due to his questions and comments as he went along. 

I'm going to sit another friend down in front of the system in a few weeks.  She seldom uses a computer and doesn't even have an email address.  <gasp>  She does text a lot to her daughter via cell phone though she has joined a part of the 21st century.

Finally the glitter feature I added was viewing unit photos on the main menu.  To be blunt this is a relatively useless feature as far as regular users are concerned.  However I'm hoping that folks who are looking at the demo will be slightly impressed.  Or as a friend put it "eye candy".  Who knows if this will help or hinder.  <smile>

Encryption and developers

A newsgroup poster recently stated:

I found a freeware dll, md5lib.dll, on the web and am trying to use it in Access 2003.

My reply (which has been added to for the purposes of this posting)

I would strongly urge using the CryptoAPI as specified by Microsoft.  Read the documentation thoroughly on MSDN.   Although MSDN can be difficult to plow through.

See Security Alert: Debian & Ubuntu Linux Weak Encryption Keys which in turn has links to a number of articles such as DSA-1571-1 openssl -- predictable random number generator

My point is that rolling your own solution or using someone else's solution without you thoroughly understanding encryption and the code can cause problems.  I trust Microsoft to do a good job with their code.  I would've generally trusted open source systems as there are lots of folks reviewing the code.  But that didn't work in this case.  I certainly would not at all trust do it yourself code or dlls found on the web.

Watch out for Null OpenArgs

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.

Updates for Microsoft Office Access 2007 Help

Click on Updates for Microsoft Office Access 2007 Help to download.  Many if not all the other Office 2007 products update for help is also available.  

Looks like most of these are also available via Microsoft Update.  Oddly enough not the Access Update.  <shrug>

An update on Rick Fisher's Find & Replace utility

I posted a blog entry on Rick Fisher's Find & Replace tool that stated I couldn't see any mention of Access 2003, in which his utility works very nicely, and Access 2007 support.  Turns out the beta copy of his tool does indeed have support for Access 2007.   I just couldn't see it but then I didn't go looking very hard either.  I've gently told him via email that he should update his website a bit.

Now what is interesting is that Microsoft has removed the ability to programmatically update macros in Access 2007.   This puzzles me especially given that the product group has significantly enhanced macros.   The Access team has gone to great lengths to ensure that macros can't do anything malicious such as deleting files so I don't quite see why.

Disclaimer.  I purchased a license for Rick Fisher's Find & Replace tool likely about a decade ago.  I have no financial interest in blogging about his tool

Installing Access 2003 runtime with SP3 and hotfix part 2

You will likely wish to install the Office 2003 security patches subsequent to SP3 into your runtime using the same techniques as for the hotfix as mentioned in the above document.  While in a test operating system in Virtual PC I ran Microsoft Update and found these extra fixes.  They only added about 20 Kb to the runtime file after SP3 was installed however, using Microsoft Update and the overhead per update it would've been about one Mb per fix.   Painful on dialup. Also I feel it's much more professional to have things neat and tidy.

However Microsoft Updates still wants to download the security patches.  Which I thought was quite interesting.   I'm still going to apply the security patches so there is no vulnerability until the next time the updates are downloaded and installed.  While the chances are small there could be a problem I'm not willing to risk it.  Also if the user is on dialup it could take a considerable length of time before three or five megabytes worth of updates trickle down to the PC.