Null Errors when Inserting Unbound Form Values into Class

[TomHW]

I have a form that is used for data entry and for editting previous data. The form is unbound and all interaction with the underlying tables is done through a class module which keeps track of one line in a table at a time. The point of this is to allow entries and edits that can be moved across multiple forms and changed and converted without changing the original line in the table (Makes for easy undo after many changes) The Class Module can pull in the data from the fields on a form to add/update the table, and it can also populate the fields on a form based upon a given key field.

The problem I am having is when a field is empty/Null or a field in the database is empty/Null, I get errors because I am attempting to put a Null value into a variable in the class or field on the form when it won't allow it. Not all fields in the database are required or expected to always hold values. The only solution I have found is to check for null every time I try to get the values out of the class, or enter the values into the class.

I am wondering if there is a way to do this without all of the hassle of having to enter in "if not isnull(field) then" about 20-40 times depending on the number of fields in the table.

Any help would be appreciated.

Tom

 

[gol4]

may not be much help but what about the nz() function

 

[TheAceMan1]

How are ya TomHW . . . .

By any chance do you have the [blue]Required[/blue] property for any fields of interest set to [blue]Yes[/blue] in any respective table/s?

[purple]Required set to Yes does not allow Null![/purple]

See Ya! . . . . . .

 

[TomHW]

I do not have any of the tables set as Required other than the primary key. I have a class which has variables that parallel the fields in the table. Example:

TABLE
-----
tblDocument:
ID
Subject
...
Comments

CLASS
-----
Private mlngID as Long
Private mstrSubject as String
.
.
.
Private mstrComments as String

Public Property Get ID() As Long
ID = mlngID
End Property

Public Property Let ID(lngValue as Long)
mlngID = lngValue
End Property
.
.
.

I have about 25 of these properties as there are about 25 fields in the table. I use this class to store an instance of one row of the table. I have functions which fill all of the fields on a form based on a specific row of a table and also a function which pulls all of the data from a form into an instance of this class. This isntance can then either be added to the database through it's own code or updated if it already exists. The problem is that the class pulls in every field from a table and tries to put each field into a corresponding variable. Some of the fields are empty and I get an error because I am trying to set these variables to a null value.

I wanted to know if there was anyway to get around this without writing a specific

If Not IsNull(!Subject) Then
mstrSubject = !Subject
End If

for each of the 25 fields. Then having to write those same if statements for when I pull info from the fields on the form and put them into the properties of this class and also for when I get the properties of the class in a form to populate its fields.

Any help is greatly appreciated.

Tom

 

[TheAceMan1]

TomHW . . . . .

Although I've used [blue]Class Modules[/blue], I've never run into this problem. However, in my notes I do have a warning about [blue]Nulls & Empty Strings[/blue] with a reference to the following link:

http://www.di-mgt.com.au/classes.html

I double checked and the site is currently restructuring. Hopefully they'll finish soon. One things for sure . . . . if its in my notes, it had to be good. I'm now waiting myself so I can copy the info. Meantime I'm researching this for future reference. If I find anything I'll post back here.

In the meantime, unless another tipster comes up with a method, it looks like your stuck with Nz().

See Ya! . . . . . .

 

[TomHW]

Thanks TheAceMan1,

That site seems to be heading in the right direction. Although with the versions they are using and the copyright dates at the bottom, it seems there is little hope of them completing any of the tutorials.