Tony's Microsoft Access Blog

The Detention Wizard

The Detention Wizard - In this story I blame the school for clearly not choosing a competent software developer.  This app has so many problems it's frigging unbelievable.  

And regretfully it's an Access based app so it perpetuates the stereotype of Access being a useless product.  Although it's not at all clear that the front end is in Access.  The first few comments think it was a VB app.

How many flaws?  I feel rather ill just starting to itemizing them.  "retina-burningly bright colors applied as a gradient background.", "blink", "unlabeled buttons", "support only eight food items"

One amusing comment - "I've always referred to these kinds of apps as "garage apps"".   Other comments were very denigrating to VB or Access.  <sigh>

Relinking tables

I was asked the following question

"I want a simple approach to relinking all linked tables--yes, it would be nice to relink to more than one back-end--in an application IF they are out of date. Best place to put the code, maybe in the splash screen code-behind-form? Best method? I dunno. "

As I had the answer(s) in recent newsgroup replies I decided to answer this question and I'll do some work and put this on a web page.  Real Soon Now TM Jerry Pournelle

Never mind.  I created the web page Relinking Tables

Silly, silly HP printer driver software

Ok, so this is a bit of a rant.

On my slightly older laptop I turned it on so a family member could use it to print some airplane tickets.   Well, I didn't have my latest printer installed on it so I copied the 300 or so Mb printer driver from my main laptop and started to install it.  Now the software is from HP and the printer is the 2605dn.

First problem is the driver software wants to extract itself into a temp folder under My Documents and Settings, etc but using 8.3 file naming convention.  I'm looking at this prompt and thinking to myself, "I'm an experienced person so I know what is going on.  But what about your parents or siblings?  They don't have a clue what this means so they're just going to click OK so why bother even displaying the folder prompt?"  That was a waste of a prompt

And HP, delete the temporary files when done installing the driver, ok?  Duhhh!!!

Now the good news is the printer driver did find the network attached laser printer so that was quite convenient.

But at the end the install software gave me a screen on which the only available option was to reboot. ARRGGGHHHH.  Let me make up my own mind when I'm going to restart.   I'm a big boy.  I know what's happening.

Of course this all took way too long so I just let the afore mentioned family member use my main laptop to print the airline tickets.   Ah, well, at least that laptop will have the anti-virus and OS and office patches up to date as well as the printer driver.

Access made the Daily WTF

Error'd: It Happens fourth item down.  The Subform wizard.  And yes, these kinds of mistakes through Access do bug me.   However I'm so used to them I just keep on going.

It is clear to me that the testers used short object names.   And I, for one, don't.  Especially when one of my applications , the welding shop ERP system, had 160 tables, 1200 queries, 450 forms and 350 reports.  And one macro.

Stopping SQL Injection in its Tracks

Stopping SQL Injection in its Tracks  Highly recommended if you are running ASP or IIS.

Setfocus and subforms - 2165 "You can't hide a control that has the focus."

I was quite puzzled.   I wanted to setup some fields on my Granite Fleet Manager so that the user could toggle off or on the item cost or price.   For example they might want to do up a quote for someone and just show the price.  (Hmm, I'll have to add another toggle button to hide the markup.)

To make some fields non visible I had to move the Setfocus elsewhere as otherwise I got error 2165 "You can't hide a control that has the focus.".  As these were subforms the quick and dirty approach was to create a control on the main form, set the focus there and then figure out a better solution.

Well that didn't work.   Upon much mucking with code figuring things out I was starting to understand what was happening.  Close examination of the two fields circled on the form below you can see how they are both slightly highlighted indicating they both have the focus.  First time I can recall coming across this interesting concept

Clearly I need to change the focus to a control on the subform.  But there are three subforms on that form that could have the cost or the price I want to make non visible.   So how do I do that?

Now there might be more elegant solutions but I decided to save a flag value of the subform that I'm making the fields non visible.  Then depending on which subform I had the error on I would move the focus to a field just to the left of the fields being hidden.

' Prices
sbf = 1
Me![Service Detail - Techs sbf].Form![lblCustomerPrice].Visible = Not Me.tglViewPrice
Me![Service Detail - Techs sbf].Form![lblCustomerRate].Visible = Not Me.tglViewPrice

....

tagError:

    Select Case Err.Number
    Case 2165 ' You can't hide a control that has the focus.
        Select Case sbf
        Case 1
            Me![Service Detail - Techs sbf].Form!srtHours.SetFocus
        Case 2
            Me![Service Detail - External Invoices sbf].Form!sriDescription.SetFocus
        Case 3
            Me![Service Detail - Parts sbf].Form!ptxQuantity.SetFocus
        Case Else
            MsgBox "Tony made a mistake."
        End Select
        Resume
    Case Else
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ViewCostAndPrice of VBA Document Form_Service Detail"
    End Select

Note that i always place the error text on the error handling line.  And sometimes throw in something slightly nonsensical.

I might've used the value of the tab control index and figured out the name then but I felt that was a bit more obscure.  

Compile error: Sub or Function not defined

As a follow up to my previous blog entry Compile error: Method or data member not found here's the identical VBA IDE problem but due to a function name not found.

In the above screen shot CalcPrice is the undefined function yet the VBA showed the previous variable.

Note that I frequently use the If ... Then _ if there is only one line of code to be executed.  Looks nicer, in my opinion, and shortens code. 

DailyIdea.tv: Why a Geek Will Steal Your Girlfriend this Year

DailyIdea.tv: Why a Geek Will Steal Your Girlfriend this Year

Add two items to your computer tool kit

Add two items to your computer tool kit   Indeed there are some low priority files I want to get off my old laptop hard drive.  It's been sitting in the freezer for a few months now so it should be cold soaked. <smile>  Good idea about the blue gel packs.

Updated - Microsoft Access Email FAQ - DOS Mailers

Ensure the user can update the data

"One judge, however, accidentally punched in a mark of 8.7 instead of 9.7. While the mistake was immediately admitted, officials refused to change Fréchette's official score and the champion swimmer was relegated to second place. " Canada's Sports Hall of Fame - Sylvie Fréchette

As Sylvie Fréchette was a Canadian the video of the judge on the TV news back in 1992 frantically running around trying to get someone to change her score stuck in my mind all these years.

A newsgroup poster asked about how to handle bar code scanning in a serial number in a factory and then somehow indicate "Pass" or "Fail".  I gave the poster some suggestions but added "always make sure the user can go back and change things.  Mistakes happen and you have to allow them to change things. "

Now clearly you can't always do this.   In many financial systems you want to have an audit trail of any such changes.   Or the users simply aren't allowed to change things but must key in adjusting entries.  Or maybe you give the user to the end of the shift before locking fields or require supervisors to make any changes.  Or once a transaction has been invoiced it can't be updated. 

Compile error: Method or data member not found

I'd forgotten about this bug in Access 2003 VBA.  I was getting the message Compile error: Method or data member not found but the me. control was just fine.  In fact I could see the control in the line above.  I spent about 10 minutes trying things getting more and more frustrated. 

A dim memory of getting something weird like this before surfaced and so I tried putting a quote in the code to separate out the parts of the line of code that weren't, apparently, causing a problem.  And that fixed the problem.

Oh yeah, now I remember that bug.   If you have several me controls on a line, or maybe two field from a recordset or such VBA can get confused if one control or field doesn't exist and indicate an error with the other control or field before it on the same line.

In the below the red circled control ptxCostz is the problem while the white on black control ptxMarkupPct  is innocent of any wrong doing.    And see how ptxMarkupPct is used three lines up and at the beginning of the line?

$499 1.5 m RJ45 network cable

The AK-DL1 is a "digital audio reproduction" cable.  Note the word digital.  There are either 1s or 0s going down the cable.  No analog signal of varying intensities or "nuances".

Another link - The Joy of Tech - Why premium cables cost so much.

Now of course this isn't to say that digital cables are perfect.  A friend's office was being renovated and added to and so he decided to have them check all the cabling.  Especially in his office as he was having random network issues with the connection to the server dropping.  Well, it turns out that they got a high school kid into to setup the Ethernet cabling because head office was exceedingly cheap and they had to hide some expenses.   Turns out the kid ran my friends network cable right over four fluorescent light ballasts.  Cable was moved sideways and he's never had a problem since.

Update:  It got slash dotted with a direct link to Denon's page.  <guffaw>  They're going to get slammed.  And mocked.

Stephen Lebans has updated the A2000ReportToPDF utility

A2000ReportToPDF is an Access 2000 database containing a function to convert Reports and Snapshot files to PDF documents. No PDF Printer driver is required.

New Version of DynapPDF.DLL dated March 11/2008. New Version of StrStorage.DLL dated May 12/2008. Fixed Paper Size Bugs. Added new functionality to allow user to Merge two separate PDF documents into a single PDF document. Exposed Security and Permission properties. Exposed flags for UNICODE conversion of the Text records contained within the Metafile. Change search order for DynaPDF.DLL and StrStorage.DLL - now searches the folder your MDB resides in before searching System32 folder.

I've implemented this utility for several clients.  It works very, very well.  And it's free!  Stephan purchased the developers dll himself and has made it available for us to enjoy.

Deploy! Deploy! Deploy!

"It was the calm before the storm. Brokers were sitting at their desks in silence, watching the clock." ...  Deploy! Deploy! Deploy! (Very nice attention grabber the initial paragraphs.)

Why you should always do some testing especially of core functionality.  I know I've screwed up in the past in a similar fashion.  Fortunately never such core functionality and never with such severe financial and other consequences.

News Flash: Spaces are legal characters in both filenames and passwords!

News Flash: Spaces are legal characters in both filenames and passwords!

My comments to that posting:

I didn't know spaces were valid in a Windows password until I happened to be watching a Microsoft video a year or three ago.   Oh I knew all about file names having spaces but not Windows passwords.

I'm a database developer specializing in MS Access for the last ten or fourteen years.  I've been using Windows at least that long.  

Many, many users don't know that either.  I've asked around. I'd suggest adding some text to the Windows login screen.

Data belongs in your tables -- not in your code

Duane Hookum, fellow Access MVP, posted a link to a very useful blog posting - Data belongs in your tables -- not in your code.  

One of my #1 mottos is "When in doubt add more tables."  A corollary is "and add more fields."

Debunking third-world myths with the best stats you've ever seen

TED Talks: Hans Rosling: Debunking third-world myths with the best stats you've ever seen.   Fascinating content as well as superb animations of numbers.   At about the 16 minute mark note his display of boring columns of numbers.  Contrast that with his fabulous animated graphs and balloons representing population numbers.

How can we as developers present such numbers to interested folks such as decision makers?   I'm the last person to make any suggestions here.  Other than to use code to move data to Excel for further massaging and presentation up the org chart. <smile>

Edited: I had forgotten that it was fellow Access MVP Allen Browne who brought this presentation to my attention. Thanks Allen.

Adding Programs to Access 2007 Deployment Packages

Adding Programs to Access 2007 Deployment Packages is a video using PDF & XPS Addin Setup as the example.  This demo gets right down into the setup.ini file.

Mike Stowe did a fine job of smoothly saying PDF & XPS Addin Setup quite a number of times.  I'm sure I would've stumbled a few times on that one.