Land Of Oorang

It's often useful to document, either in a module or in a post, the referencs needed to make the module (or code snippet work). Unfortunately you can't copy the path's and names from the references window. Here is a nice and easy way to get the information you need.

1. Press Ctrl-G to open the Immediate Window.
2. Paste this: 
   For Each r in Application.VBE.ActiveVBProject.References:?"'"&r.Name,r.FullPath:Next
3. Press Enter, and enjoy

Some people (me) like this format better, it's a little longer though:
For Each r in Application.VBE.ActiveVBProject.References:?"'"& vbTab+"-"&r.Description:?"'"&vbTab+" "&r.FullPath:Next

In case you missed it. http://www.microsoft.com/office/2010/en/default.aspx

You should always speak the truth. But you shouldn't always speak.

Every now and again I will run across a post like this:

I have created (a) form with check boxes. I am trying program the form so that when a particular box is checked the user cannot check another box in that row. For example, the user should not be able to check "yes", "no" and/or "don't know" at the same time. This is the code I an using, but I keep recieving error messages. What is the problem?

Often such posts are followed by solutions that involve catching the click event of every single CheckBox control, then manually checking each CheckBox that shouldn't be selected and flipping the value: 

Private Sub CheckBox1_Click()
    If Me.CheckBox1.Value Then
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox2_Click()
    If Me.CheckBox2.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox3.Value Then Me.CheckBox3.Value = False
    End If
End Sub

Private Sub CheckBox3_Click()
    If Me.CheckBox3.Value Then
        If Me.CheckBox1.Value Then Me.CheckBox1.Value = False
        If Me.CheckBox2.Value Then Me.CheckBox2.Value = False
    End If
End Sub

    If this code made you cringe, you are not alone. As you can imagine, when you (or the poor soul following you) has to add/rename/delete a CheckBox in future this is going to be real maintenance nightmare. Especially if you, like the user above have a lot of controls. Fortunately there is an easy solution to this problem.

    The easiest solution is, of course, to just use Option Groups. No code needed. But some people just really hate radio buttons and want the look of a CheckBox. And since some of those people might be employing you, I will direct your attention to the GroupName property of the CheckBox control. At first glance you might think all your problems are solved. It looks just like the OptionButton.GroupName property! Yay! Sadly, for whatever reason, in VBA this property will hold a value, but the mutual exclusion of an Option Button Group is just not implemented automatically. But don't despair! It's still pretty easy to work around this. With a Class Based approach you can reduce the code in your Form to this:

Private m_cogGroup1 As New CheckOptionGroup
Private m_cogGroup2 As New CheckOptionGroup

Private Sub UserForm_Initialize()
    m_cogGroup1.InitializeGroup "Group1", Me.Controls
    m_cogGroup2.InitializeGroup "Group2", Me.Controls
End Sub

To do this:

1. Import the two classes posted below.
2. Go ahead and use the GroupName property to specify your groups of checkboxes (just like you would an option group).
3. Implement as shown above, using one array of CheckOptionGroup for each group.

To make life a little easier, I have created an Example Workbook.

If you just want to copy/paste the code use these:

CheckOption.cls

CheckOptionGroup.cls

These are also stored in the Code Archive.

Something I've just noticed recently: Advice given in all caps is almost always wrong.

I have been participating in the development question and answer site StackOverflow. If you haven't been there yet, you really should take a look. StackOverflow is a great place to get an answer. One of the reasons it is such a great place to get an answer is that it's pretty no-nonsense. Any response to a question that is not a correct answer will be voted down, and on some occasions submitted to some less than tactful critique. This is not a bad thing, because it contributes to an enviroment that encourages correct answers. But it is also why the correct answer to this question had me laughing so hard.

I have seen a lot of different ways to get a GUID, from randomly generating each character to using API calls. And every time I do, I think "Gosh that didn't have to be so hard." So for the record... Here is the easy way:

Private Function GetGUID() As String
    GetGUID = Mid$(CreateObject("Scriptlet.TypeLib").GUID, 2, 36)
End Function

Of course if you only need one, you could just go here.

Thanks to BraveTerry for pointing this out: The Manga Guide to Databases.

I'm not a manga fan, but that is just... just... well words fail me

Ok, the bulk of the migration is finished. Things will look the same for the most part, but you will notice a few new features. For one I now have Tagging, for another you now have comments.

Migrating... I will be moving this site into the blog section slowly but surely. 

On to the show:

On occasion you need to open a form in Access, and have that form already know something special. (Say a filter.) The generally accepted way istp pass the form the information with an "Open Argument":

DoCmd.OpenForm "Example", OpenArgs:="Table1.[MyField]='1'"

But what if you want to pass more than one argument? Well the bad news is you can pass one, and only one string to the form. So you must cram all of your data into one string and parse the string as needed. If you have ever had to maintain someone else's work you know that if the original Author was in a hurry they probably just tacked this and that on as they went, and left you with a 5 or 6 custom string parsing procedures across 2 or 3 databases. This can be, in a word, painful.

One way you can spare your successors  this agony, is to use a (somewhat)self-documenting design pattern. A structured way to approach this problem is select a universal argument delimiter, and a common way to access the arguments.

Example follows: