How to populate a Content Control Combo Box with multiple items in a Word 2010 document via a Macro

I am building a data entry form using a word document.  The logic of why this is a good choice is not relevant.  My problem was that I needed to add a set of combo boxes in table cells, each loaded with a list of option for the user to pick from.

There is the option to hand enter each list item... but as I had about 100 items and a dozen duplicate combo boxes... this would have been painful.  The fact that I would need to update this list in the future just made it torture. So... is there a faster way?

After some googling without any success, I came up with a very quick and dirty method.


My over view is:

I have the list of items I want in the combo box in a text file in the same directory as the word document.  (Note that each item must be Unique as the combo boxes do not like duplicates)

I have built the word document with the Content Control Combo boxes in the correct locations and saved it to the same directory as the text file.

I then added a new module to the word doc via the VBA Editor in Word and wrote this simple little macro.

Option Explicit

Sub PopulateCareProviderComboBoxes()

    Dim itemFromFile As String
    Dim cc As ContentControl
    For Each cc In ActiveDocument.ContentControls
        If cc.Type = wdContentControlComboBox Then  'checks if the control is a combo box
            cc.DropdownListEntries.Clear 'get rid of any existing entries in the combo box
            Open "CareProviderDumpList.txt" For Input As #1  'this is my text file with the items I want to add
                Do While Not EOF(1)
                    Input #1, itemFromFile
                    cc.DropdownListEntries.Add itemFromFile 'put the item from the text file into the combo box
                Loop
            Close #1
        End If
    Next
   
End Sub

I only needed to check if the control is a combo box, as all the combo boxes on my form are the same.  You may need to identify a specific combo box.  You can do this by testing the Title property on the Content Control object.

E.g:

If cc.Title = "Customers" then .... 

Once thats done, I simply ran the macro to load the list from the text file to each of the combo boxes. Then saved the word doc with the new items in place.

Job done.

How to add line breaks to ControlTip Text in Access

Whats the problem?

If you've ever tried to add line breaks or formatting to the ControlTip Text in a control in an Access project you will have noticed that you can add anything to the ControlTip Text property field and it will get put on a single line. I.e...

You can add any sort of tricky string to the property dialog box and get no useful result.

If you have spent any time thinking about property dialog programming you should already see whats going on... input validation.  The code behind the property dialog is "sanitizing" your input to prevent you injecting control characters, illegal characters and other types of idiot/malicious code.  This is a good thing... except when you want to do something like this intentionally.

Whats the solution?

The above description helped me identify the solution... avoid the input validation behind the property dialog rather than try to bend it to my will.  The hack is to add the formatted string for the ControlTip Text via code.  I use the Form_Load sub to set tricky control tip text for all the controls on the form that I want to have multi-line ControlTip Text.

I.e

Private Sub Form_Load()
     showTaskManager.ControlTipText = "Display the Batch Task Manager" & vbCrLf & "Some other text here"
   
End Sub

This will show up as a nicely formatted multi-line ControlTip.

Simple once you see it....

Using querydefs and parameter querys in VBA

Today I will be bitching about using parametrized query's from VBA code in a robust way.  I will also be presenting a roundup of the solutions as far as I know them.

The Problem
You want to modify a query on the fly using some dynamic option or parameter in VBA and then use that as the RecordSource for a form.  Bloody obvious need if one is building a db driven app.

Possible Solutions
The first is to user a parameter query with a field somewhere on a form/global variable that the query can just pick up and run with.  This sucks because its inflexible. I need to essentially plant a global somewhere that always exists and always contains the right value... can anyone say spagetti code? Hello 1980.

The second ( variation) is to try to plug the parameter in on-the-fly by massaging the Querydef object and then pulling the Recordset by hand and somehow pushing it into the form or whatever you are trying to use it as a source. This is just fuggly.

The third is to compose the SQL as a string and put the parameter into the string using string functions. Nice, easy and completely unmaintainable using all the nice GUI tools provided in Access.  Yes I can do it, but it means I end up with my code littered with SQL fragments that I have to then hand maintain every time something changes in the db schema. what a PITA.

The fourth possible solution I have seen floating around is to build a whole dialog that can compose the SQL on the fly using some combo boxes etc. This is just a variation on solutions 1 and 3. With the worst parts of both.  Now you have even more trash to maintain and find when you change anything. The whole point is to reduce complexity not add to it. Blahhhhh.

Fifth possible solution is to roll my own object that somehow can be instantiated, wrap a Querydef object and gracefully update it as required.  Since there are no useful hooks to build this around, it gets back to having a global object somewhere and messing with it on demand... back to option 1 but with the overhead of a whole class of code to now maintain.

Main Bitch
What I would much prefer is a nice wrapper object around a Querydef that could be used to create a temporary Querydef with parameter slots, I could then plug in the params, test that the Querydef is stable, and pass it to the form as its Recordsource.  This way I can maintain the Querydef using the Querybuilder GUI and all the other test tools, use the QueryDef gracefully from code and not littler my code with all sorts of fragile text string and maintenance headaches. Oh and I want MS to supply this wrapper so I don't need to roll my own. Bastards.

Usually when I find myself banging my head against what seems to be an obvious problem, I usually find that its just that I don't know the "Right way" to do things in that language or framework and that with a little bit of reading I can find it and get my head right (usually refactor a truck load of code) but get back on the true path and start making progress again.  However in this case it seems like its a common problem that doesn't really have any particular "right solution" its just lots of ugly hacks. (There are some truly UGLY solutions floating around the forums to this problem. But I don't feel like I have a more pleasing solution so its much of a muchness. Still they offend my eye in ways that indicate they are fragile, hard to maintain and error prone)

Ok head is a bit clearer... bitching complete. I need to go read up on the QueryDef object I think.


Edit:

The best solution I have come up with ( tip of the hat to a forum post I read but can't remember... its probably common knowledge anyway) is to maintain the query in as a normal QueryDef which gives me the ability to use the GUI and compile the SQL for testing; the Query contains a parameter which is essentially a unique key phrase that I then use the "Replace" function to replace with my desired parameter info.

For instance

    Dim qdef As QueryDef

    'contains a param "[ContID]"
    Set qdef = CurrentDb.QueryDefs("ContinuityRelationshipFormSpecificQuery")
   
    Dim theSql As String
    theSql = Replace(qdef.sql, "[ContID]", str(relationshipID))
     
   Me.RecordSource = theSql

This is a fairly simple and maintainable system. It takes about four lines to do what I would like to do in less, but its still only a line per parameter so not too horrible a trade-off.

Profiler for VBA code in Access

I found a nice little stack style Profiler for VBA from a book called Access Cookbook. Its been very handy for identifying and tracking down a couple of memory leaks (My fault as usual) The Profiler is in a module called basProfiler and is discussed in an article called "Create an Execution Time Profiler" which has been reprinted all over the web. I had to fix a small bug in the code that formatted the output to the log file. Other than that it was good to go out of the box.

My usage of basProfiler

1. Import the module.
2. Set the path to the log file to somewhere conveinent. (I put mine into my working directory for subversion to manage)
2. Instrument the code blocks with the opening and closing lines.

Public Sub Whatever()

     acbProPushStack "Whatever" 'profiler

         'code to be profiled here

         '.....

     acbProPopStack 'profiler

End Sub


I open the log file in Notepad++ on the other monitor and every time I run the code, Notepad++ detects that the file has changed on disk and allows me to reload it. This gives me the power of a real text editor rather than a log window to play with the output.

Simple and elegant. Just the way I like my tools.

I considered hacking it to output a comma delimited file for import into excel for analysis but I ended up not needing that sort of power on this job.

And we are done.

Quicksort for VBA array of custom objects

Can you believe that there is no sort function for the collection object in Access VBA? Anyway, I have dumped the collection for a typed array to avoid some of the overhead of type casting variants everywhere. (Long story involving a profiler and a lot of time which finally resulted in finding a different source for the bug that was causing the slow down.)

Back to the main thread. I went looking for a good implementation of a sorting algorithm in VBA. After a couple of absolutly spectacular crappy implementations I found this one.

http://www.java2s.com/Code/VBA-Excel-Access-Word/Data-Type/QuickSort2.htm

Which is short, simple and elegant. In comparison to some of the other multi-page examples I found its really ... beautiful.

Anyway, the first thing I did was refactor out the comparison operation and the swap operation, so I could apply the operation to an array of custom objects without adding clutter to the quicksort sub.  By extracting the comparison operation, I can now sort the objects in the array on a variety of properties.  If I get bored I will implement both ascending and decending sort but I don't need it right now.

The most painful part of this is not being able to write a template for the code and make type independent. (Anyone mentions VBA variants as being equivalent and they are outa here ...)

Just thought I would share...