A Story about User Interface Design

At work we recently moved to a new building which has been adapted several times, some spaces have had multiple uses and multiple security level changes. The present use of rooms is different from the original design. As a result walking down the corridor requires a combination of pressing buttons, waiting for automatically opening doors or simple manual operation.

I can see new people coming down the corridor and people regularly get caught out by at least one door waiting for a manual door to open automatically or alternatively trying to manually open the automatic doors one set of which have heavy hydraulics.

Light relief when I am having my lunch.

UI design

An example application for use learning Русский


I quite often put together various databases / applications to help me with my side projects. This is a good example. In an effort to help me remember my Russian verbs I put together a small database to assist in learning and remembering verbs.

One table consisting of

Memory Number
Russian Imperfecive Form
Russian Perfective Form
English Translation
Conjugation Type
Conjugation Class
Memory Aid
Page in Book

Works nicely in conjunction with the code that alters the keyboard mapping when going between cells (follow LINK for details). I am entering the list of verbs based on the popularity of use as listed here.


And I have field (Page in Book) listed so that I can relate back to my personal reference manual.

“The big silver book of Russian verbs 555 fully conjugated verbs in all Tenses” by Jack Franke – McGraw Hill – 2 nd edition

Here’s an image of the simple form that I put together only took half an hour. I use a left click AZ sorting right click ZA sorting on columns much like applications such as Outlook and ITunes the difference is my sorting tends to be a bit more intelligent in that I will generally sort on multiple columns. EG if I have a sort on a name field first name for example I will make the surname the secondary sort. I personally really dislike the double click as an event I think it is highly imprecise. The memory aid is some tricks I took from a Tony Buzan book. Future development will be ability to print out list and a further child table that will allow recording of related conjugations and yes the example screen shot shows that I still need to input some more information.




I eluded to it in my last post but when I have tabulated forms I like to have the labels activated and set to sort alphabetically ascending on left click and descending on a right click.

I use the mouse down event as trigger for this.

If Button = acLeftButton Then
    Me.OrderBy = "Q001Contacts.CompanyName, Q001Contacts.Surname, Q001Contacts.Firstname"
    Me.OrderByOn = True
    Me.OrderBy = "Q001Contacts.CompanyName DESC, Q001Contacts.Surname DESC, Q001Contacts.Firstname DESC"
    Me.OrderByOn = True
    End If

I use this pretty much on every single tabulated form that I have – This is not so different from Outlook or Itunes. What is different is that I tend to use a slightly more intelligent sort. In the above code you can probably see that for this particular column (its the company column) I first sort on company then I sort on the surname of the individual and then on the first name. I find it frustrating on things like Itunes or Outlook that you can be left searching around within a sort category sometimes as it is unclear what order within the sort that things are arranged by.

I use the left and right click rather than double because I used to find that double clicking would first sort the list one way and then immediately sort the other. A double click I believe sends mixed messages to the system and encourages lag. I still hate the idea of double clicking on things.

If I could change one thing about standard UI design it would be to get rid of the double click!!!

Needless to say I have banished double clicking from all my UIs.

Sometimes all it takes is a walk

The other day I had a good morning.  A colleague had asked me to help him out on something and we’d just spent an hour going through some options getting pretty much nowhere, finally I told him I was out of ideas and we were going to have to leave it for just now. He thanked me but truthfully I wasn’t very happy. Things were broken and in this case broken was inoperable. I left his desk and started walking back to my office which is located in another buildings ten minutes away. Out of nowhere and almost exactly half way between both buildings I realised what the problem was. I immediately phoned my colleague and he made the required adjustments. Hey presto everything worked.

I love getting things working
and I love when you suddenly realise how to get things working.

Rolling VBS Script

Want to backup files and don’t have a fancy Database Administrator or sysadmin on 40k that knows his way around SQL Server 08R2 and MS Access , windows server and fully complies with continuous backup?

This might prove useful.

With any of the standard bespoke internally created applications that I am responsible for I like to have three main copies or parts.

1.a Back End
2.a Development copy front end – (this is used to adjust and create runtimes )
3.a Front end run time

Of the above two I backup both the back-end and the development copy of the front end. I do not back up run times (No.3), personally if I have the others backed up I can re-create a run-time from whatever point I desire. If for some reason I can’t create a run-time from a development copy it is time to roll back on the development copy – this should exist because of this very post.

In terms of the two types of copies I consider both equally important. Back-ends are easy to create but very difficult to re-create increasing in value with time. Front ends have no value in the data but take an extra ordinary amount of time to figure out and likewise are very awkward to recreate increasing in value with time. Both are digital and can be copied multiple times often with little to no cost (size dependent for back ends). Many front ends are actually tiny but their value comes from the way in which things flow and the speed with which they execute.

So I backup both to date I’ve had some brushes with almost disaster but never actually lost an application. Yes there have been hiccups mainly due to my own error – getting confused with versioning and deleting the most up to date version rather than yesterdays version. Forgetting a password on an encrypted USB and having it wipe itself requiring recovery from backup – that kind of thing annoying but not really a problem. I now have a daily backup routine that at least means that I cannot really loose more than a days work. I use it all the time.

Option Explicit
Dim vardatefile
Dim varmonthfile
Dim BDayFilePath
Dim BMonthFilePath
Dim Varnow

Set FSO = CreateObject("Scripting.FileSystemObject")
Varnow = now
vardatefile = "YourDatabaseBackEndCopy-Weekday-" & day(varnow) & ".accdb"
varmonthfile = "YourDatabaseEndCopy-Month-" & Month(varnow) & ".accdb"
BDayFilePath = "C:\" & vardatefile
BMonthFilePath = "C:\" & varmonthfile

FSO.CopyFile "C:\DatabaseTarget.accdb", BDayFilePath, "True"
FSO.CopyFile "C:\DatabaseTarget.accdb", BMonthFilePath, "True"
Set FSO = nothing

msgbox "Backup Complete" ,0, "Backup Script"

Save in simple word editor and change to vbs suffix , double click to run.
Note I have made this Option Explicit which is good practice although it is entirely possible to remove Option explicit and dynamically set the variables.

In the beginning

UPDATE January 2021
Yes its still here and I still seem to be sporadically writing. Decided that I wanted to change the name to something more representative of what the site is about. So henceforth it is Cloudy DATA blog.

April 2014
This was my first post on 27 April 2014 as I write this I am about a month away from its 7th birthday.

Configuration vs Programming

I used to find configuration kind of frustrating – highly repetitive unintuitive and often changing as software versions change.

I now consider it a simple exercise in rote learning for which there is no solution except perseverance, patience and determination and I try to find out the names and numbers of the key administrators.

This change in perception means I no longer feel the kind of frustration I previously felt. I am also much more likely to refer to manuals than trying to guess my way through a menu system which was in retrospect the impatience of youth. As a result I am much more organised in documenting and keeping documentation and I think more productive as a result.

The amount of setups that are hindered by insufficient security privileges being available to the  individual doing setup must amount to millions of lost hours. Please for those giving configuration tasks to individuals build in large amounts of time for configuration. If you don’t you’ll probably just be burnt.

Programming by comparison seems gloriously imaginative and logical. Makes me think that a fundamental reason why users hate changes in Operating Systems is because of configuration. They have to re-learn quite a few sets of obscure unintuitive procedures no matter how nice the UI is, they last sorted out X years ago when they bought their previous device. As for the XP, Win 7, Win 8 debate – personally I like Win 8.1, got it on my surface. Maybe because the configuration of Win 8.1 seems like a complete doddle to the kind of obscure software packages I normally have to deal with.

VBA Function to allow alteration of Key Mapping for Multi-lingual support

Proviso : bear in mind that different Windows operating system versions have different support for languages – with Windows 7 you require ultimate version of the OS and then you have to ensure that the required language pack is installed – happy to say that Win 8 has language support as standard although you will need to specifically request certain regional input options within the settings. Windows 10 is similar, language support is free although it is likely you will have to specifically configure languages if there is more than one. My environment when I first solved this was Windows 7 ultimate.

So the problem – You are multi-lingual (or trying to be) and you regurlaly need to change mapping of your keyboard between alphabets (you can also touch type in both alphabets). You can do it manually everytime you need to change but it’s a pain, you have a database with fields some of which are in one language the others of which are in another language. You would like to alter keyboard mapping to specific languages on entering particular fields but how do you do it?

The following uses the Windows API to change the keyboard language globally. This can be done through VBA in MS Access no problem remembering the provisio that your OS must support your chosen language.

Firstly place the following in a module (note no end function required)

Public Declare Function ActivateKeyboardLayout Lib "user32.dll" (ByVal myLanguage As Long, Flag As Boolean) As Long 'define your desired keyboardlanguage

Then you can call the function from any form event.
Eg on field GotFocus and LostFocus

'1049 Russian keyboard language layout
'2057 English(United Kingdom)keyboard language layout
'1033 English (United States) keyboard language layout

Private Sub A_GotFocus()
Call ActivateKeyboardLayout(1049, 0)
End Sub

Private Sub A_LostFocus()
Call ActivateKeyboardLayout(2057, 0)
End Sub

You can find the LCID decimal codes here
Microsoft Windows Location Identifier Codes LCID

Wax on Wax off

One of the most useful and easy improvements I ever made to working was simply learning how to touch type. Its never been particularly necessary for programming as I find anytime I’m programming I write relatively little and the combination of tabs,  weird characters, automatic code generation, intellisense and strange syntax results in a fairly uniform speed, touch typing or otherwise. No were it tends to be useful is simply for smashing out quick communications with individuals. If things are going well things tend to change quickly. Yes I need that no hang on things have changed cancel that have you got this?  That kind of thing. This is were it helps to have a work colleague who is also a quick typist and a quick responder.

So I went out and bought what is called a mechanical keyboard from Das Keyboard (link to their site below) a name I might add was quickly noted as somewhat inaccurate. A colleague immediately pointing out that a mechanical keyboard really has no electronic parts whatsoever like the old key and lever ones you sometimes see in old black and white films.


I settled on a the S Ultimate model which has no labels on the keys. I figured hey I’ve started a bit of Russian if you change the key mapping to cyrillics any letters are useless anyway so lets go hard core. It arrived at my work and female colleague no 1 enjoyed watching me eagerly unpack and plug it in. Now I thought I was a touch typist. Turns out that wasn’t quite true. To register the keyboard I had to re-start  the OS. Fine except I immediately had to re-enter my password to get to desktop. Three times later I had to phone support to unblock my password. Yep you guessed it I wasn’t quite the Ninja touch type artist I had fully convinced myself I was.

I’m down with it now but it did require me upping my game. I am particularly careful using some of the USB sticks as those that are encrypted have a three strikes and your out policy  with NO recovery – that did slightly concern me.

So what is it like as a keyboard.

I was immediately about 4% faster I have raised my average speed from about 59 words per minute to something like 66 words per minute. On the rare occasion that I don’t make any typing mistakes I can get up to 75. Think of the discipline some must have had to use the old mechanical keyboards I’m sure that the requirement to use typex would have concentrated the mind on not making mistakes.

It makes me think that maybe Das Keyboard should do a special keyboard with the option for electrifying the delete key.

I am writing a letter now.

The Economics of Immortality

Its 2021 and I originall wrote this article back in 2014 – It sounded on a reread just plain wrong so.

I still maintain that programs should be in some way immortal and maybe wordpress is a case in point. I’ve updated and changed my blog and it runs today better than it ever did. Yes wordpress may change and there is a requirement that it should be updated but how long could it be maintained without maintenance.. Quite probably for a time close to forever.

VBA Function Collection for converting Eastings and Northings to Latitude and Longitude

Some years back we hired a young lad by the name of Iain Brodie on a temporary contract – The week before I had been at an ESRI conference which had extensively discussed Web Mapping and  a speaker had demonstrated showing points in Google Maps. It was clear to me that the Google Maps url would accept and zoom to coordinates if those coordinates passed to it were Longitude and Latitude. Where I work there are significant numbers of datasets that use old Ordnance Survey UK specific Eastings and Northings coordinate system. Ordnance Survey actually set out the mathematics of conversion to Lat and Long on this page even detailing coded functions albeit in Javascript.


I specifically wanted to dynamically convert using Visual Basic for applications (specifically from MS Access). When Iain arrived it was clear that he was useful with computers and so I tasked him with finding VBA code from the internet. Between us we managed to get it working and I still regularly use the function set today to give users of applications a map in Google Maps. It really is a very nice quick tool that gives users quick access to maps for – you bet zero cost. My favourite price. We originally had it working with Google Earth but I only use it with Google Maps now.

Function PHId(North1, N0, aFo, PHI0, n, bFo)
PHI1 = ((North1 - N0) / aFo) + PHI0
M = Marc(bFo, n, PHI0, PHI1)
PHI2 = ((North1 - N0 - M) / aFo) + PHI1
Do While Abs(North1 - N0 - M) > 0.00001
PHI2 = ((North1 - N0 - M) / aFo) + PHI1
M = Marc(bFo, n, PHI0, PHI2)
End Function

Function Marc(bFo, n, P1, P2)
Marc = bFo * (((1 + n + ((5 / 4) * (n ^ 2)) + ((5 / 4) * (n ^ 3))) * (P2 - P1)) - (((3 * n) + (3 * (n ^ 2)) + ((21 / 8) * (n ^ 3))) * (Sin(P2 - P1)) * (Cos(P2 + P1))) + ((((15 / 8) * (n ^ 2)) + ((15 / 8) * (n ^ 3))) * (Sin(2 * (P2 - P1))) * (Cos(2 * (P2 + P1)))) - (((35 / 24) * (n ^ 3)) * (Sin(3 * (P2 - P1))) * (Cos(3 * (P2 + P1)))))
End Function

Function lon(East1, North1)
a = 6377563.396
b = 6356256.91
F0 = 0.9996012717
E0 = 400000
N0 = -100000
PHI0 = 0.855211333
LAM0 = -0.034906585
aFo = a * F0
bFo = b * F0
e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2
n = (aFo - bFo) / (aFo + bFo)
InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo)
nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5)
rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2))
eta2PL = (nuPL / rhoPL) - 1
M = Marc(bFo, n, PHI0, InitPHI)
Et = East1 - E0
X = ((Cos(InitPHI)) ^ -1) / nuPL
XI = (((Cos(InitPHI)) ^ -1) / (6 * nuPL ^ 3)) * ((nuPL / rhoPL) + (2 * ((Tan(InitPHI)) ^ 2)))
XII = (((Cos(InitPHI)) ^ -1) / (120 * nuPL ^ 5)) * (5 + (28 * ((Tan(InitPHI)) ^ 2)) + (24 * ((Tan(InitPHI)) ^ 4)))
XIIA = (((Cos(InitPHI)) ^ -1) / (5040 * nuPL ^ 7)) * (61 + (662 * ((Tan(InitPHI)) ^ 2)) + (1320 * ((Tan(InitPHI)) ^ 4)) + (720 * ((Tan(InitPHI)) ^ 6)))
lon = (LAM0 + (Et * X) - ((Et ^ 3) * XI) + ((Et ^ 5) * XII) - ((Et ^ 7) * XIIA))
End Function

Function lat(East1, North1)
a = 6377563.396
b = 6356256.91
F0 = 0.9996012717
E0 = 400000
N0 = -100000
PHI0 = 0.855211333
LAM0 = -0.034906585
aFo = a * F0
bFo = b * F0
e2 = (aFo ^ 2 - bFo ^ 2) / aFo ^ 2
n = (aFo - bFo) / (aFo + bFo)
InitPHI = PHId(North1, N0, aFo, PHI0, n, bFo)
nuPL = aFo / ((1 - (e2 * (Sin(InitPHI)) ^ 2)) ^ 0.5)
rhoPL = (nuPL * (1 - e2)) / (1 - (e2 * (Sin(InitPHI)) ^ 2))
eta2PL = (nuPL / rhoPL) - 1
M = Marc(bFo, n, PHI0, InitPHI)
Et = East1 - E0
VII = (Tan(InitPHI)) / (2 * nuPL * rhoPL)
VIII = ((Tan(InitPHI)) / (24 * rhoPL * nuPL ^ 3)) * (5 + (3 * ((Tan(InitPHI)) ^ 2)) + eta2PL - (9 * ((Tan(InitPHI)) ^ 2) * eta2PL))
IX = ((Tan(InitPHI)) / (720 * rhoPL * nuPL ^ 5)) * (61 + (90 * ((Tan(InitPHI)) ^ 2)) + (45 * ((Tan(InitPHI)) ^ 4)))
lat = (InitPHI - ((Et ^ 2) * VII) + ((Et ^ 4) * VIII) - ((Et ^ 6) * IX))
End Function

Function degrees(radians)
degrees = 180 * radians / 3.14159265358979
End Function

Function trunc(value)
If value > 0 Then
trunc = Int(value)
trunc = Int(value + 1)
End If
End Function

And here is the code the onclick function of a button called Command01 and it pulls from a screen that has an eastings and northings field on it and which has a Sitename field.

Dim Llatitude As Double
Dim Llongitude As Double
Dim strSitename As String

Llatitude = degrees(lat([Eastings], [Northings]))
Llongitude = degrees(lon([Eastings], [Northings])) - 0.0015
strSitename = Me.Sitename
Dim strlatlong As String
strlatlong = Llatitude & ",+" & Llongitude

‘Here I have two options – the first places a marker on the map – as far as I can tell – the marker is only available within google with the side panel displayed as well. The second shows the map centered on the requested location but without any markers. Choose one

Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong

Command01.HyperlinkAddress ="https://www.google.com/maps/@" & Llatitude & "," & Llongitude & ",18z?hl=en"

And for Developers wanting to get into more detail here is the url for more information on passing parameters to the google maps url.

Google Map URLs

VB Function for Zooming to location in Google Maps at 23 March 2014

mapGoogle recently slightly altered their URL to link to Google Maps. This code passes eastings and northings from fields into a function that calculates Longitude and Latitude and then passes those calculated coordinates to the google maps url for display

Note you will need to have implemented the functions that calculate lat and long for this to be useful.

Private Sub Command01_Click()

On Error GoTo Err_Command01_Click

Dim Llatitude As Double

Dim Llongitude As Double

Dim strSitename As String

Llatitude = degrees(lat([Eastings], [Northings]))

Llongitude = degrees(lon([Eastings], [Northings])) - 0.0015

strSitename = Me.Sitename

Dim strlatlong As String

strlatlong = Llatitude & ",+" & Llongitude

'From 24 March Google changed their hyperlink address this is the new one

Command01.HyperlinkAddress = "https://maps.google.com/maps?q=" & strlatlong & "+(" & strSitename & ")&z=18&iwloc=near&hl=en&ll=" & strlatlong


Exit Sub


MsgBox Err.Description

Resume Exit_Command01_Click

End Sub


Nubuilder Pro 3 – Example Web Application Deletion and Creation – Race Number system

Objective :

Create a simple online web application that can be used to type in race numbers. Once a number is typed in the time will automatically posted to a field. This could be used to manually record numbers of competitors coming across a finish line on mobile phones provided all phones have access to a web browser and they have 4G connection or wifi connection.

First go to the administrator url and sign in with the Global Administrator username and password to get into the design form.

The format of this is

If you wish to delete a project go to the delete tab within the site select the site you wish to delete then hit Delete

It will then present you with a sanity check window

Pressing enter again you get a site administration check box again

Adding a New Site in Nubuilder Pro
Go back to the opening screen and this time go to the Add Tab.

You should be presented with an Add Dialog window.
Type in the name you wish your new project to be here I call it racenumber – note the name has to be all lower case.
Hit create after which there will be a short delay while the site is created.

There will be a short delay while the site is created

You will then be presented with the Administration panel with your new site created something like the following

Now click on the site you are interested in this case race number which will take you to the login panel

Now enter the global username and password that you were originally handed out.

Once logged in you should be presented with the following screen.

Hitting the database button will take you to the php-myadmin control panel

The First step is to create a table to hold all the data

This can be done buy using the create table button on the bottom left.

Here I am creating a table called T002Time to which I add three columns. I set the AI which stands for Auto Increment to yes and I set this field as an index I also add a time field and set the default to current_time – note that in phpmyadmin fields will not necessarily accept default functions but Time variable type accepts current_time. Current_time function returns date and time.

Here I have forgotten the racenumber field so I create some SQL to add this in.

Now we want to go back to the Nubuilder web application panel

Hit the form wizard – now select the T002Time drop down in the table name and use the add to Browse column and add to Form Objects button to transfer everything across to the respective columns as follows.

This will run and in the background create a form which you can see by going through the User Home screen as follows

Now lets remove some fields we no longer need
Go to objects we can see that three columns relate to to the T002Time(form) Lets do several things
1. Remove DTime from the form
2. Remove the Race FKID from the form
3. Change the label of the home Form button T002Timebtn to Race Numbers

Firstly in the objects button go into the two lines you wish to remove from the form

and when the object details appear delete them.

After a warning message you should be presented with the new list of object without the object recently deleted.

The new form should look something like this.

That’s 1 and 2 done now lets try and get that button bigger.

Now in here I am going to remove the RACEFKID and make the DTIME much wider

Now we see

Now lets see if we can move this field to the centre of the screen this can be done while in global administration mode by clicking on the following button

Reposition then add users and that should be you.