By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,134 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Why Values in Unbound Form Controls Don't Persist

Expert Mod 15k+
P: 31,306
We get fairly frequent questions on here about why settings (including both values AND formatting) of unbound controls on a form, are not stored for reference later, but instead, each setting seems to be applied to EVERY instance of the control, for all records that show.

I will try to lay down all the reasoning and arguments that occur to me. Sometimes, it's harder to explain things when they appear to be self-evident. To me, this is entirely logical behaviour, so please feel free to say if this doesn't make understanding the situation any easier.

Firstly, we should consider that a form, in Access, is a view of underlying recordset data. It is conceptually like a window. It can have pretty things stuck on around the side, but ultimately, it is a way of viewing data in an underlying (bound) recordset. It can also be used just to show the pretty things (unbound controls, unrelated to the underlying recordset), but we're not interested in that side of things for now.

Although the form can show various records, it doesn't itself, at any stage, store any of the values. Apart from the underlying recordset, nor does it make any arrangements to remember any settings for any of its controls. Although it appears to have a whole set of instances of itself (in continuous forms or datasheet modes), it only ever has as many instances as can be shown at any one time on the screen. All these instances are exact copies. Change any setting (including the value) and this will effect each and every one of them.

Points to Ponder:
For a control to maintain and persist associated data (as opposed to letting the data be managed by the underlying recordset), the whole concept would have to be fundamentally reorganised. More resources would be required (either a temp table just for this transient task or dynamically assigned RAM). Although maintaing such resources for a data set, for which originally at least, the limits may yet be unknown, is possible, it is not a trivial thing and would certainly introduce complication into a situation which doesn't really require it. I can't resist adding here that, with MS's reputation for losing track of resources and not closing stuff down properly - would this be a good move?

As I mentioned earlier though, the form is like a window that allows both viewing and access (manipulation) of a small subset of the recordset at a time. Extending the concept to handle a full recordset, without linking that to the recordset itself (using Bound controls in other words) would not even be desirable. It would actually fit the logic less well and be some sort of hybrid that made PROPER sense to no-one.

Interestingly, though the previous part of the article can remain in place and still be considered to be true, there is a loophole that may prove interesting to many who desire to show different formatting and display properties of a control (and its value) for each record individually. Fascinating, I hear you say (Echoing my own thoughts when the concept was introduced to me by Stewart Ross in the thread Conditional Formatting Access 2007 - Reports).

NB. Because of how this works, it only makes sense that it can work for a Report object, but not for a Form. Once a control design is changed it is refreshed for all instances on a Form, but this is not how a Report works. Read on to understand why.

Fundamentally, taking the concept of an old-style film camera, one can update the control just before the image is painted onto the Report (Using the Format event procedure, usually of the Detail section). As it is only changed (and always changed) immediately prior to formatting, the formatted output always reflects the results of the conditions pertaining to each individual record.

Here's an example that illustrates the flexibility :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  5.     With Me
  6.         .lblScore.BackColor = &HFFFF00 + 2 * .txtScore
  7.         .txtScore.BackColor = &HFF00FF + (&H100& * (&HFF& - 2 * .txtScore))
  8.     End With
  9. End Sub
The score ranges from 0 to 101 in the sample data and, to avoid a dull grey illustration, I left out one of the hues from the colours of each control. It displays like :

Attached Images
File Type: jpg CondFormatting.Jpg (7.2 KB, 1955 views)
Sep 22 '08 #1
Share this Article
Share on Google+