Problem testing fieldvalue in form data-entry = yes

[Filips Benoit]

Hi,

TBL_CONTACT_PERSON
CNTP_ID (auto)
CNTP_LAST_NAME (required = yes)
CNTP_FUNCTION (required = no)
CNTP_..... (all required = no)

FRM_CONTACT_PERSON_ADD_NEW
Property DATA ENTRY = YES

No closebutton ( user leaves pressing 'Add' or 'Exit no adding'
CmdAdd
CmdExitNoAdd

Problem with Private Sub CmdAdd_Click() > see code below *********

If the user presses 'add' i want to test if Me.CNTP_LAST_NAME has data. If
not > MsgBox "Unable to add person without LastName!" and stay in the form.

1. if i do not enter any value in any field the test
IsNull(Me.CNTP_LAST_NAME) returns True
but MsgBox Me.CNTP_LAST_NAME triggers error 'invalid use of null" ???

2. if i only enter the CNTP_FUNCTION leaving Me.CNTP_LAST_NAME blanc, the
test IsNull(Me.CNTP_LAST_NAME) returns False
Please help,

Filip

************************************************** **************************
**********
Private Sub CmdAdd_Click()

On Error GoTo ErrHandling

If IsNull(Me.CNTP_LAST_NAME) Then
MsgBox Me.CNTP_LAST_NAME 'this msgbox is just for testing
MsgBox "Unable to add person, need LastName!"
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

ErrHandling:
MsgBox Err.Number & " " & Err.Description
End Sub

************************************************** **

[Trevor Best]

Filips Benoit wrote:

Hi,

TBL_CONTACT_PERSON
CNTP_ID (auto)
CNTP_LAST_NAME (required = yes)
CNTP_FUNCTION (required = no)
CNTP_..... (all required = no)

FRM_CONTACT_PERSON_ADD_NEW
Property DATA ENTRY = YES

No closebutton ( user leaves pressing 'Add' or 'Exit no adding'
CmdAdd
CmdExitNoAdd

Problem with Private Sub CmdAdd_Click() > see code below *********

If the user presses 'add' i want to test if Me.CNTP_LAST_NAME has data. If
not > MsgBox "Unable to add person without LastName!" and stay in the form.

1. if i do not enter any value in any field the test
IsNull(Me.CNTP_LAST_NAME) returns True
but MsgBox Me.CNTP_LAST_NAME triggers error 'invalid use of null" ???

2. if i only enter the CNTP_FUNCTION leaving Me.CNTP_LAST_NAME blanc, the
test IsNull(Me.CNTP_LAST_NAME) returns False
Please help,

Filip

************************************************** **************************
**********
Private Sub CmdAdd_Click()

On Error GoTo ErrHandling

If IsNull(Me.CNTP_LAST_NAME) Then
MsgBox Me.CNTP_LAST_NAME 'this msgbox is just for testing
MsgBox "Unable to add person, need LastName!"
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

ErrHandling:
MsgBox Err.Number & " " & Err.Description
End Sub

************************************************** **

Yes, the MsgBox function requires a string expression as the prompt,
hence the error when you send null to it.

Why Me.CNTP_LAST_NAME would not yield a null if left blank is a bit of a
mystery, but one thing I'd suggest is to use the notation
Me!CNTP_LAST_NAME or Me("CNTP_LAST_NAME") as matter of course as the dot
should be for properties and methods, not memebers of collections. This
may not be the cause of the problem here but may cause problems elsewhere.

Set a breakpoint where your test fails and use the imeediate window to
test the field's value, e.g.

?CNTP_LAST_NAME
?Isnull(CNTP_LAST_NAME)
?Len(CNTP_LAST_NAME)
?CNTP_LAST_NAME=""

See what transpires, there may be some other code or something else
setting the field to a zero length string, which isn't null but you
can't tell the difference by looking at it on the screen, both null and
zero length string will look just blank.

[DCM Fan]

Two things:

1) Make sure that the table.field TBL_CONTACT_PERSON.CNTP_LAST_NAME does not
allow zero-length strings.

2) A MsgBox call cannot "parse" a null value....since your test IF ISNULL(
CNTP_LAST_NAME) returns TRUE, there's no point in trying to force MsgBox to
parse its "null-ness." Just use MsgBox "The Last Name field must be provided."

[Mike Storr]

Comments mixed in below...

Mike Storr
www.veraccess.com

Filips Benoit wrote:

1. if i do not enter any value in any field the test
IsNull(Me.CNTP_LAST_NAME) returns True
but MsgBox Me.CNTP_LAST_NAME triggers error 'invalid use of null" ???
If you think about it, you are testing to see if there is no value, and
if that is true, you are trying to display no value in a msgbox. You
simply can't do that. Change your use of MsgBox to display something
that will never be NULL.

2. if i only enter the CNTP_FUNCTION leaving Me.CNTP_LAST_NAME blanc, the
test IsNull(Me.CNTP_LAST_NAME) returns False

What does MsgBox display in this case? It may indicate that you have set
a DefaultValue, or made it an empty string somehow.

Please help,

Filip

************************************************** **************************
**********
Private Sub CmdAdd_Click()

On Error GoTo ErrHandling

If IsNull(Me.CNTP_LAST_NAME) Then
MsgBox Me.CNTP_LAST_NAME 'this msgbox is just for testing
MsgBox "Unable to add person, need LastName!"
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

ErrHandling:
MsgBox Err.Number & " " & Err.Description
End Sub

************************************************** **

[Filips Benoit]

Hi,

TBL_CONTACT_PERSON is in a SQL-server-DB
and CNTP_LAST_NAME property 'Allow null' = false

This works:

If IsNull(Me.CNTP_LAST_NAME) Or Me.CNTP_LAST_NAME = vbNullString Then

It seems that when the record is totaly blanc, the value of CNTP_LAST_NAME =
null
and when i add any value in any other field CNTP_LAST_NAME becomes = ""
Why

Filip
"Trevor Best" <nospam@localhost> wrote in message
news:40***********************@auth.uk.news.easyne t.net...

Filips Benoit wrote:

Hi,

TBL_CONTACT_PERSON
CNTP_ID (auto)
CNTP_LAST_NAME (required = yes)
CNTP_FUNCTION (required = no)
CNTP_..... (all required = no)

FRM_CONTACT_PERSON_ADD_NEW
Property DATA ENTRY = YES

No closebutton ( user leaves pressing 'Add' or 'Exit no adding'
CmdAdd
CmdExitNoAdd

Problem with Private Sub CmdAdd_Click() > see code below *********

If the user presses 'add' i want to test if Me.CNTP_LAST_NAME has data. If not > MsgBox "Unable to add person without LastName!" and stay in the form.
1. if i do not enter any value in any field the test
IsNull(Me.CNTP_LAST_NAME) returns True
but MsgBox Me.CNTP_LAST_NAME triggers error 'invalid use of null" ???

2. if i only enter the CNTP_FUNCTION leaving Me.CNTP_LAST_NAME blanc, the test IsNull(Me.CNTP_LAST_NAME) returns False
Please help,

Filip

************************************************** ************************** **********
Private Sub CmdAdd_Click()

On Error GoTo ErrHandling

If IsNull(Me.CNTP_LAST_NAME) Then
MsgBox Me.CNTP_LAST_NAME 'this msgbox is just for testing
MsgBox "Unable to add person, need LastName!"
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

ErrHandling:
MsgBox Err.Number & " " & Err.Description
End Sub

************************************************** **

Yes, the MsgBox function requires a string expression as the prompt,
hence the error when you send null to it.

Why Me.CNTP_LAST_NAME would not yield a null if left blank is a bit of a
mystery, but one thing I'd suggest is to use the notation
Me!CNTP_LAST_NAME or Me("CNTP_LAST_NAME") as matter of course as the dot
should be for properties and methods, not memebers of collections. This
may not be the cause of the problem here but may cause problems elsewhere.

Set a breakpoint where your test fails and use the imeediate window to
test the field's value, e.g.

?CNTP_LAST_NAME
?Isnull(CNTP_LAST_NAME)
?Len(CNTP_LAST_NAME)
?CNTP_LAST_NAME=""

See what transpires, there may be some other code or something else
setting the field to a zero length string, which isn't null but you
can't tell the difference by looking at it on the screen, both null and
zero length string will look just blank.

[Filips Benoit]

Hi,

TBL_CONTACT_PERSON is in a SQL-server-DB
and CNTP_LAST_NAME property 'Allow null' = false

This works:

If IsNull(Me.CNTP_LAST_NAME) Or Me.CNTP_LAST_NAME = vbNullString Then

It seems that when the record is totaly blanc, the value of CNTP_LAST_NAME =
null
and when i add any value in any other field CNTP_LAST_NAME becomes = ""
Why

Filip

"DCM Fan" <dc****@aol.comSPNOAM> wrote in message
news:20***************************@mb-m28.aol.com...

Two things:

1) Make sure that the table.field TBL_CONTACT_PERSON.CNTP_LAST_NAME does not allow zero-length strings.

2) A MsgBox call cannot "parse" a null value....since your test IF ISNULL(
CNTP_LAST_NAME) returns TRUE, there's no point in trying to force MsgBox to parse its "null-ness." Just use MsgBox "The Last Name field must be

provided."

[Filips Benoit]

Hi,

TBL_CONTACT_PERSON is in a SQL-server-DB
and CNTP_LAST_NAME property 'Allow null' = false

This works:

If IsNull(Me.CNTP_LAST_NAME) Or Me.CNTP_LAST_NAME = vbNullString Then

It seems that when the record is totaly blanc, the value of CNTP_LAST_NAME =
null
and when i add any value in any other field CNTP_LAST_NAME becomes = ""
Why

Filip

"Mike Storr" <no****@noname.con> wrote in message
news:Jv***************@news20.bellglobal.com...

Comments mixed in below...

Mike Storr
www.veraccess.com

Filips Benoit wrote:

1. if i do not enter any value in any field the test
IsNull(Me.CNTP_LAST_NAME) returns True
but MsgBox Me.CNTP_LAST_NAME triggers error 'invalid use of null" ???

If you think about it, you are testing to see if there is no value, and
if that is true, you are trying to display no value in a msgbox. You
simply can't do that. Change your use of MsgBox to display something
that will never be NULL.

2. if i only enter the CNTP_FUNCTION leaving Me.CNTP_LAST_NAME blanc, the test IsNull(Me.CNTP_LAST_NAME) returns False

What does MsgBox display in this case? It may indicate that you have set
a DefaultValue, or made it an empty string somehow.

Please help,

Filip

************************************************** ************************** **********
Private Sub CmdAdd_Click()

On Error GoTo ErrHandling

If IsNull(Me.CNTP_LAST_NAME) Then
MsgBox Me.CNTP_LAST_NAME 'this msgbox is just for testing
MsgBox "Unable to add person, need LastName!"
Exit Sub
Else
DoCmd.Close acForm, Me.Name, acSaveYes
End If

Exit Sub

ErrHandling:
MsgBox Err.Number & " " & Err.Description
End Sub

************************************************** **

[DCM Fan]

<<and when i add any value in any other field CNTP_LAST_NAME becomes = ""
Why>>

That's the million-dollar question, and only the application developer can
answer it. SQL Server does NOT insert zero-length strings on its own.

So, either the form has "" as its default value, or some stored-procedure
somwhere has '' (two apostrophes) for an INSERT statement, or something....SQL
Server does not enter zero-length strins on its own (well, SQL 7 + anyway...I'm
not sure about SQL 6.5)