OldValue not set

[Dugo]

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
deleted.

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_AfterUpd ate()
If Not IsNull(species_ code_FD) Then
If IsNull(Ctl25d.O ldValue) Then
Call f_AddRecord(Ctl 25d.Value, Ctl25d.Tag)
ElseIf Not IsNull(Ctl25d.O ldValue) Then
If Not IsNull(Ctl25d.V alue) Then
Call f_UpdateRec(Ctl 25d.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

[jeremygetsmail@gmail.com]

Re: OldValue not set

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.

Jeremy

[Dugo]

Re: OldValue not set

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
info....

[Dugo]

Re: OldValue not set

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!!!

[Wayne Gillespie]

Re: OldValue not set

On 22 Dec 2005 15:19:35 -0800, "Dugo" <Doug_Wilder@np s.gov> wrote:
[color=blue]
>Why, oh why doesn't .OldValue work as you think it should!!![/color]

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_Ent er()

MyVariable = Me.SomeControl

End Sub

To undo it use -

Me.SomeControl = MyVariable


Wayne Gillespie
Gosford NSW Australia

[Dugo]

Re: OldValue not set

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,
Doug