What is in a field?  Value vs Text and the differences between form data and table data

Level: Beginner-intermediate

Introduction

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.

TextVersusValueDemo.mdb

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.  

Forms!frmMyFormName.txtMyTextBox.Text
                      Me.txtMyTextBox.Text
                      

Select all Open in new window

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
                      

Select all Open in new window

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:

Me.txtMyTextbox.OldValue
                      

Select all Open in new window

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) & "*"
                                Me.SubfrmContacts.FilterOn =True
                      End Sub
                      

Select all Open in new window

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.

Private Sub txtLastName_Change()
                                Me.txtLength.value = Len( "" &  Me.txtLastName.text )
                      End Sub
                      

Select all Open in new window

Rolling back changes to the current record

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
                      End Sub
                      

Select all Open in new window

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
                                         me.txtMyTextbox.Undo
                                         Cancel = true
                                 End If
                           End If
                      End Sub
                      

Select all Open in new window

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
                      End Sub
                      

Select all Open in new window

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:

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.
                                   me.Undo
                           End If
                      End Sub
                      

Select all Open in new window

Avoiding Write Conflicts

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:

DoCmd.RunCommand acCmdSaveRecord
                      

Select all Open in new window

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
                      

Select all Open in new window

The Refresh method is another way to force a record save:

Me.Refresh
                      

Select all Open in new window

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
                      

Select all Open in new window

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:

DoCmd.OpenForm "YourPopupForm"
                      

Select all Open in new window

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.


Conclusion

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.