This is a fairly common error when using field values to set the values of controls (such as TextBoxes), and is caused by a concept which can be hard to grasp - Nulls.
Null is not a value, it is instead the absence of a value. It is not the equivalent of 0 (for numeric fields) or "" (for string fields), it is unknown or not set.
If you try to set the value of a textbox (or other control) to a field which is Null, the control doesn't know how to deal with it - as you are basically saying "dont have a value at all" instead of "be an empty string".
The following examples assume that your current code is like this:
Code:
Text1.Text = myRecordset.Fields("FieldName").Value
Note that the corrections shown will work for most controls, and syntax for specifying the field (eg: myRecordset.("FieldName") / myRecordset!FieldName).
In order to fix the problem you can detect if the field is Null, and if so just use an empty string as the value, eg:
Code:
If IsNull(myRecordset.Fields("FieldName").Value) Then
Text1.Text = ""
Else
Text1.Text = myRecordset.Fields("FieldName").Value
End If
Alternatively, you can use a nice little trick to convert the value to a valid string. If you append an empty string ("") to a Null in VB, the resultant text is an empty string - while appending an empty string to the end of a string value will make no difference!
This means that the only extra code you need for each string/text field which can be Null is a few characters:
Code:
Text1.Text = myRecordset.Fields("FieldName").Value & ""
One thing to bear in mind is how you treat the values when you write them back to the database - do you want to write an empty string to the databse if the textbox is empty, or do you want to write Null? This is a question which only you can answer, and the methods you can use to do this will depend on your circumstances.
