Over the years (almost two decades), there was (it has been fixed now in the latest versions) a glitch in the Access Datasheet Wizard that created a confounding condition that led many to take to the help forums to find a solution. You see, one of my favorite features of the datasheet is using the control labels to display the desired column heading at the top of the datasheet in normal view. This seemed to work for the first few columns, but then suddenly, it stopped working as you started looking at the rest of the columns down the line. Users had a hard time trying to figure out why this was happening.
Some gave up and decided, after researching the internet, that the only solution was to use the control’s Caption property to specify the desired heading. This method was not as desirable as using labels, as you had to select the control and look at the Caption property on the property sheet to see what the column heading was going to be.
The cause and solution were harder to imagine, as many budding developers (and even some hardcore ones) refused to believe that Microsoft, in its infinite wisdom, could be so “dumb” as to make the glitch in the first place, much less allow it to continue for version after version. I know I spent three days arguing with one developer on Experts Exchange to try and get him to understand why it was happening and what he needed to do to fix it. He argued that my explanation of what was happening was just not believable, as Microsoft couldn’t possibly do something like that. I had to plead with other developers, who were also trying to help but didn’t understand the reason themselves, to please test my solution and verify that it worked. Finally, one did, but not until the developer posting the question decided to give up and use captions.
The cause was simple. For the label to be used as a heading, it had to be attached to the control. Unlike in VB.Net, where you never get both a control and an attached label—just the control without a label—in Access, you get both the control and an attached label! However, the wizard was only attaching labels to the controls in the first column of controls it placed on the form in design view. The controls placed in the 2nd and remaining columns did not have attached labels, so the labels could not act as column headers.
How can you tell? Easy! When you select a control, its attached label will have a gray, square block on its top-left corner. If the label was not attached, it would not have the block. You can see this condition in the screenshot below of an Access 2010 wizard-created datasheet with the layout removed and the textbox controls selected. The first column of controls has labels with the gray block in the top-left corner. The ones in the second column do not!
So how do you fix it? Well, that is just a little tedious and time-consuming. You must cut the label to the clipboard, then select the control and paste the label to the control.
Helpful hint about attached labels: An attached label will “grey-out” with the control if you set the control to “Enabled = No” and “Locked = No” To keep the label from graying out when you disable the control, you need to detach the label from the control by cutting it to the clipboard, then deselecting the control and pasting the label to the form.
That’s all there is to it. Now, the label can act as a column heading!
