How do I identify a blank field?

[Stapes]

Hi

I am trying to stop a user adding data into a subform before it's
parent record has been created, so I am testing the key field of the
parent to see if there is one:

If Forms![Order Form]![Order Details].Form!PK_Order = "" Then
MsgBox "You need to start by clicking the New Order / Enquiry
button or the Quotation button."
Else
MsgBox "Order number '" & (Forms![Order Form]![Order Details].Form!
PK_Order) & "'"

Now the display following quite clearly shows me, the value of
PK_Order is '' (that's the quotes on either side).

So, if it can show me the field is blank, why can't it see the field
as blank itself?

The air is blue here with my cussing.

Stapes

 

[Maurice]

Try this piece:

If isnull(Forms![Order Form]![Order Details].Form!PK_Order) then....

this will check it too

 

[Pat Hartman]

To clarify - "" is a zero-length-string (ZLS) which is not the same as null.
If you want to check for null values in code, you MUST use the IsNull()
function. No other method will work because when you compare something to
null, the answer is always null. So - If somefield = Null - will never be
true even when somefield actually is null because the answer returned by the
If is null. One common way to check for both "" and null at the same time
is to concatenate a ZLS with a field - If somefield & "" = "" - this will
return true if somefield is null or "" because concatenating the ZLS changes
the field from null to a ZLS.

 

[Ken Sheridan]

You can prevent a user entering a new record in the subform until the parent
form has a record by putting the following in the subform's BeforeInsert
event procedure;

Cancel = IsNull(Me.Parent.PK_Order)

Or if you also want to alert the user:

Const conMESSAGE = " You need to start by clicking " & _
"the New Order / Enquiry button or the Quotation button."

If IsNull(Me.Parent.PK_Order) Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Cancel = True
End if

Ken Sheridan
Stafford, England

 

[gllincoln]

Hi,

One way to detect that null or blank/empty field is to check it this way -

If Len(Nz(Forms![Order Form]![Order Details].Form!PK_Order.Value))= 0 Then

The Nz function (called the null to zero) returns a "" string or 0 if a numeric expression is being evaluated. We can reliably use the Len function against the returned value from Nz. A fellow programmer demonstrated the Nz function to me a few months ago; I've found myself using it - a lot.

Hope this helps...

By the way, one way to ensure that a parent record is created before the user inputs data into the line items sub form is to set the subform visible to false until the requisite parent record has been generated. The user won't be typing into a form until they see it.

Gordon