Tony's Microsoft Access Blog : Granite Fleet Manager

Building Scalable Databases: Perspectives on the War on Soft Deletes

Tony — Tue, 24 Nov 2009 06:01:54 GMT

This blog posting titled Building Scalable Databases: Perspectives on the War on Soft Deletes is interesting reading describing what to do with deleted data. The author comes to the correct conclusion, because he agrees with my viewpoint <smile>, that you model the business situation and mark records as inactive, canceled, etc. You seldom need or want to truly delete data.

I used to have an inactive flag on the equipment in the Granite Fleet Manager. That worked for quite a while until I had someone tell me that they have reserve equipment that is only used when the California wild fires are rampant. These fire engines are paid for by the state of California and stashed at various fire halls in strategic locations. They are all identical. Thus fire crews from outside the affected crews have already trained on that equipment and know how to use it. So now I have a status combo box which includes Reserve and Out of Service. The Out of Service is meant for the situation where a given unit will be unavailable for a period of time longer than a few days. For example maybe the boiler needs an inspection or the mechanic is waiting for parts to come from overseas.

P.S. I almost always add a general purpose comment memo field to strategic tables such as the equipment and the service order field. And you can search in those fields. Because no matter how many explicit fields you create the users can always think up a few more but don’t have any place to put the random data.

New version of the Granite Fleet Manager - V1.63

Tony — Wed, 15 Apr 2009 01:43:53 GMT

A new version of the Granite Fleet Manager is available for download. Nothing major. Mostly a lot of updates in the temporary locations forms to make it easier to move equipment from job site to job site or back and forth to your main yard.

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>

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.

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

Tony — Sun, 30 Nov 2008 22:08:17 GMT

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

Tony — Sun, 30 Nov 2008 21:13:46 GMT

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.

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

Email feedback

Tony — Sun, 23 Nov 2008 02:32:43 GMT

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

New version of the Granite Fleet Manager available for download

Tony — Wed, 19 Nov 2008 09:35:02 GMT

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>

Losing the [Event Procedure] on a command button

Tony — Tue, 11 Nov 2008 07:40:03 GMT

I just added a tab control to my standard File Locations form in the Granite Fleet Manager program. I added the tab control and cut and pasted all the controls from the form to the page on the tab control. I then discovered that I had lost all the [Event Procedure] entries in the command buttons. I've added them back in before I took this screen shot.

I've noticed this behavior occasionally before but had never realized what caused this. In this case it was quite easy to recall as I had made this change a few days ago and all three command buttons on the form were missing the [Event Procedure]

Background.

I've decided to install the Access Front End (FE) and the demo BE MDB and demo JPG files in the users Application Data\Granite Fleet Manager folder. I decided to install the live data BE MDB into the users My Documents\Granite File Manager folder. Thus the data MDB is easier to work with if the user wants to move the files onto a memory stick or anything else. The default settings in Windows Explorer make getting to the files in the Application Data folder rather difficult.

However my current database location form displays all the BE MDBs in the folder in a list box. I felt that while that was good for me in testing and good for network installs it was sub optimal for an end user who just wants to use the program on their own PC. (Sub optimal, yeah, that's a nice euphemism for sucks.)

For stand alone users they only care about the demo system and the data system. So on the Basic (Single User) page on the tab control I created a listbox which displays all the BE MDBs in the same folder as the FE MDE is installed and all the BE MDBs in the My Documents\Granite Fleet Manager.

And I cut and paste the already existing controls on the form to the tab Advanced (Networks) page on the tab control. Which caused this problem.

A refinement

I then thought about things a bit. Once the user sends me some money I email them a 265 byte license key file containing their encrypted business name. Now I can tell them just to drag and drop that file into their My Documents\Granite Fleet Manager folder. The application looks for a key file in that folder and asks the user if they want to relink to the data file in that folder. If they click Ok then they get relinked automatically. They don't even see the above form. Now if they ever do want to switch between the data and the demo BE MDBs they can get to the above folder.

Another refinement

A further refinement is to handle the situation where they email me a spreadsheet containing all their equipment. I will import the spreadsheet into the data MDB and email them back a complete data MDB. Typically with the name of their organization in the file name.

Below is the screen should the license key and two BE MDBs exist in the My Documents\Granite Fleet Manager folder.

That should be simple enough for any novice computer user. Maybe. I hope.

Note how I display the unit and service order count in the list box. I've taken to displaying the number of records in one or two key tables in the app on the main menu. This really, really saved my butt big time once about eight or nine years ago. I had to convert their backend to the new data format manually. (Now I use VBA code and the Compare'Em utility.) Well, I screwed up and converted a copy they had emailed me a few weeks before.

I also added a flag to a table (I always have a GlobalOptions table) in the BE MDB which states if the MDB is Demo, Data or Test. Test should only reside on my system.

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

The sordid life of software bugs, exposed!

Tony — Thu, 25 Sep 2008 22:00:09 GMT

The sordid life of software bugs, exposed! is a very interesting look into the software bug process within Microsoft. While this is about another MS product my impression is that this a very common approach within Microsoft. If you are going to report a bug to Microsoft it helps to have an exact repro scenario including an example file if appropriate and why you think it's important to a lot of people that Microsoft should fix it ASAP versus in the next Service Pack versus never.

I do something similar but vastly simplified in my shrink wrap program the Granite Fleet Manager. With a few minor exceptions. Every time I come across a bug or problem that I can't fix and test in five minutes it usually gets entered into an Access database. Now if the bug or problem in a feature that I'm working on and I'm going to get to it in a day or two then I'll enter it in my active OneNote page I use as a notepad for that particular feature. Then I cross it off once I've done using the strikethrough font capability. (I've added the strikethrough button to my OneNote Toolbar.)

I frequently find that as I go through my list of outstanding bugs that I've already done several but had forgotten they were in my list.

Microsoft Access Report Printing Criteria Selection Form page updated

Tony — Thu, 11 Sep 2008 04:47:32 GMT

I've completely updated the page at Microsoft Access Report Printing Criteria Selection Form with the latest code that I currently use. That page was created in about 1996 or so and was among the first five or ten pages I ever created. So it was long overdue. <smile>

I also put a link in to the screen shot that's in my Fleet Management System application that I'm working on. Hey, that's a shameless plug to see if your employer or someone you know might be interested in that system. And I pay a commission.

Don't forget the small touches - Invoice number and Set date range to last month

Tony — Sun, 13 Jul 2008 21:23:01 GMT

I'm just finishing up adding customer invoicing functionality to my Fleet Management app. One bit of functionality a client requested was the ability to know if a customer service order had been invoiced separately by the branch office accounting department. The idea as that the mechanics would only give the customer a service order itemizing the work done, parts used, etc, etc while the accounting department would give the customer the official invoice. And most importantly the accounting department would ensure they got paid.

At first a simple check box was requested but then I realized putting an invoice number would be better. The accounting department would enter the invoice number. Thus not only the accounting department know that the service order had been put on a invoice they would be able to track the paper work better.

I also decided that a purchase order number field would sometimes also be useful.

The accounting department needs to review all the service orders to ensure they all have invoice numbers. And of course they are going to really want to do this for last month and this month. So rather than making them futz around with the date selection boxes and clicking on arrow buttons I decided to give them two command buttons.

The code behind the buttons is:

Me.FromServiceDate = DateSerial(Year(Date), Month(Date) - 1, 1)
Me.ToServiceDate = DateSerial(Year(Date), Month(Date), 1) - 1

and

Me.FromServiceDate = DateSerial(Year(Date), Month(Date), 1)
Me.ToServiceDate = Date

I might rename the command buttons as the names are kind of awkward. Nevertheless I'll publish the screen shot and four lines of code now.

I'll probably never hear from anyone about how useful these two little buttons are. But folks are going to use those somewhat and are, hopefully, be thinking "That's nice."

By the way the various selection criteria are enabled or disabled depending on whether the report has those fields. In this particular report the "Job Site zzzzz" list box isn't used.

Also note that this app allows the users to choose the names of the temporary and permanent location. In this testing database they are "Job Site zzzzz" and "Branch Office".

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

Tony — Fri, 27 Jun 2008 22:15:00 GMT

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.

Background colour of forms

Tony — Fri, 02 May 2008 05:39:00 GMT

In a comment to my Multi Row Tab control posting Diana asked:

"Just curious, is your form really that blue color? Is it in production? How do your users feel about that color? Would you design a web page that color?

I'm always curious why Access developers pick colors for the background of their forms like pink or bright blue or whatever. I guess I always think of my forms like web pages and design accordingly."

This is the only time I've ever changed the background colour of a form and was due to a client's comment. Users were getting confused between the Equipment form and the Service form in my Granite Fleet Manager as they had a striking similarity. I also realize the colour blind folks, generally males, will have problems with this. Not sure what can be done in that situation. (Maybe colour blind males can read better than non colour blind males. <smile>)

I might do the same thing for a web page in a similar circumstance.

I haven't had any comments, negative or positive on these forms.

Multi Row Tab control

Tony — Mon, 25 Feb 2008 10:24:00 GMT

In my Granite Fleet Manager I'm soon going to be adding enough tabs to the following form that I'll run out of space. I didn't like the looks of the default behavior.

So I asked in the private MVP Access newsgroup how you could get multi line tabs? Ken Snell was the first to reply that there is a Multi Row property on the tab control. So the following is how it will look.

Duh!!!!!

Write a list and check it twice

Tony — Mon, 24 Dec 2007 20:25:00 GMT

A very interesting post on relating drastically reducing hospital infections and smooth code check ins. Write a list and check it twice.

Now how does this apply to us Access folks? I'm not quite sure. I do testing as I add new features to Access software but that's about it. I don't have any automated testing. As much as anything because, unlike operating system software or application software such as Access or Excel we don't have much inter related code. Generally our code means one form updates one table. (And each subform updates another table.) It's the content of the tables that are inter related.

Yes, we do have modules of code but again they're executed from forms and generally they update one or two tables.

Now one thing that I occasionally have is an unnormalized grand total. For example in my Granite Fleet Manager system there is a total service order cost which is the sum of the parts, labour and external invoices. That only makes sense as otherwise on some reports I'd be doing a lot of extra reads of the three child tables to fetch that total. This is the same as your bank account balance.

I do have a routine which the user can run which compares the total present on the service order record against the child tables. If there is a difference I offer to fix it up. I also log the message to an error log table so I can review it myself. And after working with forms that update those values I will run that routine myself to ensure I haven't missed anything.

Once I add in the capability of automatically downloading updates to my software then I'll be testing that each time I put a new version on my web site.

My Auto FE Updater needs automated testing. Which I will be adding to it as I add some long overdue enhancements to it. But that's not the typical Access app.

So what can us Access developers do for a check list before releasing software to the users?