The Combo Box That Taught Me VBA

The listings are coloured like Visual Studio on a white sheet. Switch the site to Light (top right) if names look faint - keywords stay blue either way.

People often ask me how I got into writing VBA code. The honest answer is gravel. Well, gravel and a combo box, but the gravel came first.

The gravel problem

Back in the 1990s I ran a gravel company. We didn't dig the stuff ourselves. All our gravel was bought in from pits dotted around the area and delivered to customers. That sounds simple enough until you try to price a job.

Every pit charged a different price for its material. Every customer was a different distance from every pit. On top of the material you had the haulage charge, and the haulage worked out very differently depending on whether the lorry was carrying a full load or a part load. So the cheapest pit for one customer wasn't necessarily the cheapest for the next. Even for the same customer, the best pit for a full load might not be the best pit for a partload.

I wanted a quick way of answering one question: for this customer and this load, which pit should we collect from?

The software house with the plush carpet

My first idea was to pay someone else to do it. In the 1990s I went with Jim, our accountant, to see a software company in Egham in Surrey. It was a beautiful building, and they led us into the boardroom. I remember I could hardly walk, because the carpet was such a deep plush it was like walking through lawn grass.

I said I wanted the system written for the new Windows. They basically laughed at me and said it would have to be done in DOS, because Windows would never amount to anything.

The whole vibe was wrong. It felt like some sort of scam, and I was very suspicious. I don't think I would have employed them anyway, but around then I had a conversation with an independent programmer who told me how these companies operate. They will design a database to your specification, and that's the trap. Your specification is never good enough, never detailed enough, so there's loads of room for them to add extras.

For instance, you would expect a box on the screen to give you a drop-down list to choose from. Instead they would give you a sheet of paper with the choices written on it, and you'd have to type in a number to pick the one you wanted. When you complained, they'd say, "Well, that's what you asked for." A drop-down, of course, is extra! Multiply that throughout your database, and something that was going to cost £1,000 ends up costing £5,000.

So I decided to do it myself.

SuperCalc and the angle that didn't matter

My first go was on a spreadsheet called SuperCalc. I used the Ordnance Survey grid, the 1 km squares on the map, to give every pit and every customer an easting and a northing. From those you can work out the distance as the crow flies.

This is where I made my first proper programming mistake, and I hadn't even written any code yet. I got it into my head that I needed the direction of the line from the pit to the customer, the angle, the vector. I agonised over it for ages. Then one day the penny dropped. I didn't need the angle at all. I only needed the distance, and that's just Pythagoras:

Distance = SQRT((CustomerEasting - PitEasting)^2 + (CustomerNorthing - PitNorthing)^2)

Lesson one, and it's served me well ever since: work out what you actually need before you start solving problems you haven't got.

Alpha 4 couldn't sort

Next I moved on to a database. Alpha 4 was a DOS database I got off a free cover disc on a computer magazine. It could hold the pits, the prices and the grid references, and it could list the pits for me.

What it couldn't do, or what I couldn't make it do, was sort that list into order, cheapest first. And that was the whole point! If the cheapest pit isn't at the top of the list, you're back to running your finger down a column of numbers.

Access and my brother-in-law

Then Microsoft Access came along. I asked my brother-in-law whether this new-fangled Windows-type database had legs. His answer: "Well, it's from a new company called Microsoft and they seem pretty good!" New to me, anyway.

He wasn't wrong. Access could sort, and that was enough to win me over.

The combo box moment

The thing that really hooked me was the combo box. I set one up to list the pits, sorted by delivered price with the cheapest at the top. Delivered price meant the price of the material plus the haulage from that pit to the customer.

But the order had to change. It depended on where the customer was, and it depended on whether it was a full load or a part load. A combo box on its own can't do that. So I started writing little bits of VBA to change the list behind the combo box, its row source, whenever the customer or the load type changed.

That's how I began to use VBA. I started by manipulating combo boxes.

The funny thing is that the combo box is exactly the drop-down those people in Egham would have charged me extra for.

I've still got the table structure of that original database, but the front end, with the forms and the code, has gone walkabout. There's probably a copy somewhere, but I can't find it! So here's an illustration of the idea, not the real thing. Imagine a table called tblPits with the fields PitID, PitName, Easting, Northing and PricePerTonne. The form has a combo box called cboPit, text boxes for the customer's easting and northing (in metres), and an option group called fraLoadType where 1 means a full load and 2 means a part load.

Private Sub fraLoadType_AfterUpdate()
    RefreshPitList
End Sub

Private Sub txtCustEasting_AfterUpdate()
    RefreshPitList
End Sub

Private Sub txtCustNorthing_AfterUpdate()
    RefreshPitList
End Sub

Private Sub RefreshPitList()
    Dim dblHaulRate As Double
    Dim strDistanceKm As String
    Dim strDelivered As String

    'Can't work anything out without the customer's location
    If IsNull(Me.txtCustEasting) Or IsNull(Me.txtCustNorthing) Then Exit Sub

    'Haulage cost per tonne per km (made-up figures)
    If Me.fraLoadType = 1 Then
        dblHaulRate = 0.15   'full load
    Else
        dblHaulRate = 0.4    'part load costs more per tonne
    End If

    'Pythagoras on the grid references, turned into kilometres
    strDistanceKm = "Sqr((Easting - " & Me.txtCustEasting & ")^2 + " & _
                    "(Northing - " & Me.txtCustNorthing & ")^2) / 1000"

    strDelivered = "PricePerTonne + " & strDistanceKm & " * " & Str(dblHaulRate)

    Me.cboPit.RowSource = "SELECT PitID, PitName, " & _
                          "Round(" & strDelivered & ", 2) AS Delivered " & _
                          "FROM tblPits " & _
                          "ORDER BY " & strDelivered & ";"
    Me.cboPit = Null
End Sub

Set the combo box's Column Count to 3 and its Column Widths to something like 0cm;4cm;2cm, so the ID is hidden and you see the pit name with its delivered price. Change the load type and the list reshuffles itself, cheapest first. Setting the row source makes Access requery the list, so you don't need to do it yourself. That was magic to me back then, and to be honest I still enjoy it now.

Why the combo box is the ideal first VBA project

A combo box is useful on its own. You can build one with the wizard and never write a line of code. But add a small amount of VBA and it can be moulded to do some fantastic and useful things for you.

That's what makes it such a good place to start. You write two or three lines, you press a button, and something visibly happens on the screen. You get a reward straight away, and that keeps you going. Along the way you pick up events like AfterUpdate, properties like RowSource, columns, SQL and eventually loops, without ever feeling you've sat down to "learn programming".

It worked for me. One of my first projects was a search form which, incidentally, sold quite well. It's basically the same Nifty Search Form I still offer today, although it has evolved over the years. It had combo boxes, and I manipulated them with VBA.

Three small wins to try

If you've never written any code, try one of these.

The first is showing extra details from the combo box. If your customer combo box has hidden columns for the address and phone number, you don't need a big query behind your form to show them. Put this in the combo box's AfterUpdate event:

Me.txtAddress = Me.cboCustomer.Column(2)
Me.txtPhone = Me.cboCustomer.Column(3)

Columns are counted from zero, so Column(2) is the third column. That catches everyone out at least once.

The second is adding <ALL> to the top of the list. Use a union query as the row source:

SELECT "<ALL>" AS PitName FROM tblPits
UNION
SELECT PitName FROM tblPits
ORDER BY PitName;

The < sign sorts before the letters, so <ALL> sits at the top. In your code you check whether the user picked "<ALL>" and, if they did, you clear the filter instead of applying one.

The third is handling NotInList. Set Limit To List to Yes. Then, when someone types a name that isn't there, ask them if they want to add it:

Private Sub cboCustomer_NotInList(NewData As String, Response As Integer)
    If MsgBox("'" & NewData & "' isn't in the list. Add it?", _
              vbYesNo + vbQuestion) = vbYes Then
        CurrentDb.Execute "INSERT INTO tblCustomers (CustomerName) VALUES ('" & _
                          Replace(NewData, "'", "''") & "')", dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
        Me.cboCustomer.Undo
    End If
End Sub

The next rung: code that works anywhere

Once you're comfortable, you start noticing you're writing the same code over and over. That's when it gets interesting.

A while back someone on Access World Forums asked about filtering a subform with several combo boxes. Instead of naming every field in the code, I put the field name in each combo box's Tag property. Then a single function loops through the controls on the form and builds the filter. I call it straight from each combo box's After Update event property by typing =fCreateFilter() in the property sheet. (It has to be a function, not a sub, for that to work.)

The person who asked was Spanish, so the combo boxes said Sí, No and Todas. That led me to a nice discovery. By swapping the value list to Yes, No and ALL, the same code worked in English without touching it.

I call this sort of thing a drop-in component: a piece of code you can drop into your form and it works without modification. I think that's especially appealing to beginners, because you can use it straight away and then pull it apart to see how it works.

Learning by helping

I didn't learn any of this on a course. I started out as an unskilled, untrained Microsoft Access developer. I joined Access World Forums in July 2003 and read other people's answers, and gradually I learnt.

Then one day I saw a question I knew the answer to, so I answered it. That was a big and difficult step! But the more I did it, the more proficient I got. Seeing other people's questions teaches you as well. I went on to moderate the forum for nearly 20 years. I kept taking the bespoke answers I'd written for people and turning them into generic ones, and many of those ended up here on Nifty Access and in the hundreds of videos on my YouTube channel.

Your turn

So here's my challenge. Open one of your databases, find a combo box and give it one small job to do with VBA. Show a customer's address from a hidden column, add <ALL> to the top, or let people add a new item with NotInList. Just one. When it works, and it will, you'll have written your first bit of VBA, and I suspect it won't be your last.

Coming soon: years later I started wondering whether the angle mattered after all, when I thought about scheduling a lorry's day and which next load was "on the way".