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