By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,029 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

OldValue not set

P: n/a
In Access 2003, I have a form (continuous form view) with several text
boxes. The text boxes are not bound to a table since the layout of the
form differs greatly from the structure of the normalized table into
which the data must go. Each time the text in any one box is modified,
I have code to carry out some appropriate action. E.g., if the textbox
is empty and a value is added, then a new record is created in the
normalized table. Also e.g., if the textbox has a value in it already
and the value is changed, then the matching record in the table is
found and updated. If a value is removed, then the matching record is

To achieve this, I'm using each textbox's OldValue property. It works
quite well if you enter data into only one text box. But if you enter
in one box, then enter more data in another box, then go back to the
first box and remove the data, an Invalid Use of Null error is
given--the OldValue is Null even though there was a value in the box.
In the code below, when I remove a value from a textbox (which should
delete a record in the table), I get an error on the line "Call
f_AddRecord..." since OldValue is mysteriously set to Null and, of
course, the textbox's current value is Null too since I just removed
the value.

Here's the code for one of the text boxes:

Private Sub Ctl25d_AfterUpdate()
If Not IsNull(species_code_FD) Then
If IsNull(Ctl25d.OldValue) Then
Call f_AddRecord(Ctl25d.Value, Ctl25d.Tag)
ElseIf Not IsNull(Ctl25d.OldValue) Then
If Not IsNull(Ctl25d.Value) Then
Call f_UpdateRec(Ctl25d.Value, Ctl25d.Tag)
Else 'Ctl25d is null
Call f_RemoveRecord(Ctl25d.OldValue, Ctl25d.Tag)
End If
End If
End If 'species code null
End Sub

Any help is greatly appreciated!

Doug in Fairbanks

Dec 22 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
Hmm. Not sure how it works when you deal with only one field, but the
OldValue property doesn't do anything for you if you're working with an
unbound form. There are a lot of ways to deal with this, including
tossing the original value into the tag of the control at the same time
as you load the value into it.


Dec 22 '05 #2

P: n/a
Yes, well, I must confess that the form actually *is* bound to a table
so as data are entered, they are stored there. But that table is not
the normalized table and is only a failsafe place to store the data if
for some reason the data are not written to the proper table (the
normalized one).

The .tag idea is a good one but I'm using the tag already to hold a
value used when the data are mapped into the normalized table. But I
could use something like the status bar text or controltip to hold the

Dec 22 '05 #3

P: n/a
So I'm now using the StatusBarText to hold the old value and it's
working until you go to a new record and try to add a value in a text
box that was given a value in an earlier record. That is, the
StatusBarText is not reset to null when you go to a new record.

Why, oh why doesn't .OldValue work as you think it should!!!

Dec 22 '05 #4

P: n/a
On 22 Dec 2005 15:19:35 -0800, "Dugo" <Do*********> wrote:
Why, oh why doesn't .OldValue work as you think it should!!!

It may not work as you think it should, but it does work exactly as explained in the help files.
OldValue only works with BOUND controls because the OldValue is actually the current value of the control's bound field
as stored in the underlying table before the record is edited. If the control is unbound then there is no OldValue.

Why not use a form level variable to store the value of the control?

Sub SomeControl_Enter()

MyVariable = Me.SomeControl

End Sub

To undo it use -

Me.SomeControl = MyVariable
Wayne Gillespie
Gosford NSW Australia
Dec 22 '05 #5

P: n/a
You replied to my "why o why" statement just as secretly did to myself
(it's something in *my* thinking that's the problem). The form and
textboxes actually are bound to a table so I'm still not sure why
..OldValue isn't working as I hoped (again, it must be something with my
hope that's the problem).

I did try using a form level variable but abandoned it for some
reason...maybe I should revisit it. But I've got around the problem by
having the statusbartext set in the GotFocus event for each text box.
Not too elegant but it works.

All in all, if I didn't have to stick with a normalized table to store
the data, things would be much easier. And since this db won't be used
by more than 5 people at once and because the number of records won't
top 100k in any one table for years to come, it seems to me that
normalization is unneeded in this case. But we're grandfathered into it
so I must cope.

Thanks again,

Dec 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.