Ever wanted to retreive the label information of a textbox control in Ms Access?

Been a while since I’ve written one of these things. Truth is, I haven’t been doing much Access databases in some time. However, I’m back in the fold, writing a small Access 2007 app and reacquianting myself with its nuances and idiosynchroses. In the process, I stumbled across a small little feature that, while obvious once you think about it, is not immediately apparent to the casual user.

Often on Access forms you’ll use a textbox control. Usually, unless you delete them, the textbox control comes with an associated label control. Lets call my example “txtGenericTextBox” with a label called “lblGenericTextBoxLabel” with the string “THIS IS A LABEL” in the caption property of the label.

Now, what if you want to get certain values of that label in your VBA code? For instance, lets write the caption of the textbox label to the screen. Easy-peasy!

MsgBox Me.txtGenericTextBox.Controls(0).Caption

This will display the caption of a label associated with txtGenericTextBox. As such, you can access a host of individual attributes of the textbox’s label using similar methods.

How is this useful? Well, in my case, I’m developing some field validation for a data entry form for a beforeUpdate event. I’m looping through all the controls on a form that have the word “Required” in their TAG property. An example of similar code is shown over at DatabaseDev.co.uk (article link here).

But I want nice ‘user friendly’ error messages. I don’t want the user seeing ‘you haven’t entered proper data in the field txtGenericData’ – I want it to use the nice clean label caption which reads much better.

There may be many more easier ways to do what I’m doing, but this seems pretty straightforward. Hopefully it is of some assistance.