Populating Forms

[Hollie]

Hi,

Can anyone help me with this problem?

I am populating a form from a SQL Server database. Some of the columns from the database are NULL and I need to show all the info from the database.

The form can then be updated. (NULL values may be written back.

Problem is when I'm assigning my textboxes their values. If the value is NULL I get the error message - invalid use of NULL. I've tried checking to see if the value is null before the assignment but nothing seems to work

e.g.
me.txtAddress = !address
me.txtPhone = !phonenumber

as I said putting if statements round them to trap the NULL values doesn't work.

Any ideas?

H.

[Surgeon]

Hollie, I guess your problem is that your way to trap the null values ISN'T right. Bare with me :

Let's take your example. If you did somehting like this :
*******************************************************************
If !address=null Then me.txtAddress="" Else me.txtAddress=!address
*******************************************************************
then this is no good! Why? Because : -I quote-: "Expressions that you might expect to evaluate to True under some circumstances, such as If Var = Null and If Var <> Null, are always False. This is because any expression containing a Null is itself Null and, therefore, False."

You get it ? So instead of what I've written above, you should use :
*****************************************
If IsNull(!address)Then me.txtAddress="" Else me.txtAddress=!address
*****************************************

The IsNull function returns a Boolean value that indicates whether an expression contains no valid data (Null).

Plz tell me if that helped ya :-)

Surgeon

[paulw]

Have you tried

If IsNull(!address) Then
  Me.txtAddress = vbNullChar
Else
  Me.txtAddress = !address
Endif

Or

Me.txtAddress = IIf(IsNull(!addresss), vbNullChar, !address)

vbNullChar is effectively a zero length string, when you right the data back this may give you problems since a zero lenght string is NOT Null.

BTW Why do you allow Nulls in the back-end anyway - that decision alone makes you deserve the pain...

Cheers,

P.

[Stevie]

Why not just

Me.txtAddress = "" & !address

[Hollie]

Thanks Surgeon and Paul - it works!

I'll try your suggestion Stevie!

H.