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