We see the occasional question in the Access area which at its core is about the difference between what is seen
in a control on a form and the underlying value in that control. This article, and the attached sample show the differences between the two.
Text ... Value ... OldValue
Unlike other platforms where textbox controls only have a Text property to get or set the data, Access textboxes and combo boxes have several such properties – the Text property, the Value property and the OldValue property.
What's seen in the UI...
The Text property gets or sets the data as it is seen in a control. This changes real-time, as the user enters data.
This property can only be used when a control has the focus (as in the Change Event), and will cause errors when used at other times.
... Is not necessarily what is currently in the tables
In a bound control, the Value property gets or sets what is stored in the underlying field (Control Source) in the table or query that defines the Record Source of the form or report. The Value property is the default property for data entry controls, and can be referenced either implicitly or explicitly:
Me.txtMyTextbox '<- Implicit reference to the value property (this is more common)
Me.txtMyTextbox.Value '<- Explicit reference, meaning the same thing
Unlike the Text property, the Value property does not update ‘real-time’ while the user is entering data. The value stays the same until the user exits the control, at which point it changes and catches up with the Text property, which has been updating the with each of the user’s keystrokes in the meantime.
If the user has left the control for another control on the same record, the data is not fully committed to the table. That happens when the user navigates to a different record, closes the form or otherwise forces a save of the complete record. Until a complete record save occurs, the previous value of that control is available (and recoverable) through the read-only OldValue property:
Once the complete record is saved, the OldValue property can no longer be used to restore the previous value of the field.
What about unbound controls?
While the article up until now has been focused on bound controls, surprisingly there is also a distinction beteen the Text and Value of unbound controls. As with bound controls, the Text property of unbound controls reflects what is seen in the UI, changing real time as the user types. The value property, even though there is no table-stored data updates only when the user leaves the control. The sample database uses unbound controls to demonstrate the difference between Text and Value.
The difference with unbound controls is in the OldValue property. As mentioned previously, the OldValue property is used to get/recover previously stored data in the currently edited record, before changes are committted to the table. Since it pertains to stored data, it is not relevant to unbound controls. Nonetheless, it compiles and works -- it just behaves like a read-only version of the Value property for unbound controls (both properties always show the same information).
So what’s the value of all that?
The differences between what is seen and what is stored, and the Text, Value, and OldValue properties have many practical uses in your applications. These are just a few...
'As you type' filtering and data checks
The text property, since it is shows what is typed as it is being typed is useful in creating filters that update as the user types. For example the change event of a textbox or combo can be used to filter a listbox or subform character by character as data is entered, using the Text property in a wildcard search as follows:
Private Sub txtLastName_Change()
Me.subfrmContacts.Filter = LastName LIKE " & chr(34) & Me.txtLastName.text & chr(34) & "*"
The Text property can also be used to check data being entered, on the fly. For example, this will determine the length of data entered, which can then be used for data validation.
Rolling back changes to the current record
Private Sub txtLastName_Change()
Me.txtLength.value = Len( "" & Me.txtLastName.text )
As mentioned earlier, the edits to the current record are not fully committed to the table until the user leaves the record or a save is forced. Before that happens, previous data can be restored by a variety of methods.
At the control level, data can be restored in any event up until the control updates using the Undo method or the OldValue property. For example the Undo method *can* be used in the Change Event to validate data and automatically restore previous data (I don't recommend this, as it may create a very annoying user interface). Since we are validating date 'real-time' while the control is in focus, we're using the Text property, not the Value property:
Private Sub txtMyTextBox_Change()
' This will automatically restore previous data if the user enters text ending in a 'q'
If Right(Me.txtMyTextBox.Text, 1) = "q" Then Me.txtMyTextBox.Undo
At the control level, a more common (and slightly more pleasant) approach would be to use the Before Update event. The control has lost the focus at this point, so note that the Value property is being used instead of the Text property:
Private Sub txtMyTextBox_BeforeUpdate(Cancel as Integer)
' This checks the data entered and reverts to the previous data based on user input
If Right(Me.txtMyTextBox.Text, 1) = "q" Then
If MsgBox ("This data really shouldn't end with a q. Do you want to cancel this?", vbYesNo, "Cancel?") = vbYes then
Cancel = true
The Value of a single control
can be restored even after it's update, as long as the record itself has not been updated. This can be done in any event prior to the form's update, using the OldValue property:
Private Sub cmdResetMyTextbox_Click()
Me.txtMyTextbox.Value = Me.txtMyTextBox.OldValue
' ** Reminder - 'Value' is the default property, so this will also work
' Me.txtMyTextbox = Me.txtMyTextBox.OldValue
The previous values of all edited controls
can also be restored in one go with the Undo method applied to the form (as opposed to individual controls). This can be done though any event prior to the form's update. For example, a custom Cancel button can be used:
Avoiding Write Conflicts
Private Sub cmdCancel_Click()
' This reverts previous data based on user input
If msgBox("Do you really want to cancel and restore previous data?", vbOKOnly, "Cancel?")Then
' You can use the Undo method on the form as a whole, not just a single control.
Understanding what data is in a field and when can also help prevent those pesky write conflict dialogs. These can occur when one user or process attempts to save a record while another is still editing it. A common scenario is when a form opens up a popup form which edits data in a table that the main form is also bound to. If the user makes changes to the data through the Popup form and then attempts to save the data in the main form, this message will appear.
The reason for this is that edits to data in the main form are not automatically committed to the table when a user opens and changes focus to a popup window that affects the same data The record being edited in the main form is still in that state described earlier, where the ‘old value’ property is still usable and changes can be either dropped or saved. When a popup is opened to edit the same data, it is almost like a second user making conflicting changes (hence the warning).
Avoiding these messages is simple – it just requires a forced save before opening the popup form.
There are several ways to force a record save in Access.
acCmdSaveRecord is very straightforward - the syntax describes exactly what it does:
You can also force a save through the Dirty property of the form. If a form has been edited, it's Dirty property is True. Makes sense, right? Setting the Dirty property of the form back to false will immediately save any changes to the underlying recordsource:
Me.Dirty = False
The Refresh method is another way to force a record save:
You can conditionally update the recordsource based on whether the form data has been edited like this:
' Isn't VBA a great language?
If Me.Dirty = True Then Me.Refresh
Any of these methods can be used to save a record, and once the data has been saved, a popup form can be opened for editing data, without resulting in Write Conflict Messages:
Showing current data in subsequently opened reports and forms
It is similarly important to save data in a form prior to opening a report. The data seen in the form is not necessarily in sync with the data in the underlying table. That means that if a report or another form is opened filtered to the current record, any edited data may not display as expected. Forcing a save updates the table with the current values. This in turn ensures that the data displayed on your report is current, since the report uses saved data and would not otherwise display edits made to the current record on your form.
What is seen in a form is not necessarily what is stored in the underlying table. I hope this article and the demo database have shown some of the differences between the two, some ways to use those differences to your advantage as a developer, and some tips for avoiding common problems associated with them.