Checking value of a Null field

[BWD]

Hello Group

I have a form with a field that is used to add records to a table.
The first field on the form is the Primary key. I wrote a snippet of
code on the lost focus event that checks the value of the PK field to
make sure it is not null. However, the code does not seem to work and
keeps producing a Run Time Error "424: "Object Required".

Here is the code:

Private Sub Customer_Number_LostFocus()
Dim x As Variant
Set x = Me.Customer_Number (**This is the PK field)
'check to be sure that the customer # field has a value
If x = Null Then
MsgBox "You must enter a customer #", vbOKOnly, "Enter a Customer
#"
End If

End Sub

Any help appreciated!!

 

[Linq Adams via AccessMonster.com]

If you actually have this line

Set x = Me.Customer_Number (**This is the PK field)

in your code you need to place a single quotation mark before

(**This is the PK field)

to let Access know that it's a comment and not part of the code.

A much simpler code for this purpose would be


Private Sub Customer_Number_LostFocus()
If IsNull(Me.Customer_Number) Then
MsgBox "You must enter a customer #", vbOKOnly, "Enter a Customer #"
End If
End Sub

 

[Allen Browne]

Try:
If IsNull(Me.Customer_Number) Then
instead of:
If x = Null

Nothing is ever equal to Null. For an explanation of why, see:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
It's error #5 in that article.

BTW, you might be better using the BeforeUpdate event procedure of the
*form* if you want to give a custom message about this field. That will work
even if the user never visits this field.

(The fact that the field is first is no guarantee that the user will visit
it. For example, if another field has focus in an existing record, and you
then move to the new record.)

 

[Klatuu]

Your code should be in the Before Update event of the control. The Beforeu
Update event can be canceld so the cursor stays in the control and allows the
user to correct the problem before continuing:

Private Sub Customer_Number_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Customer_Number Then
MsgBox "You must enter a customer #", vbOKOnly, "Enter a Customer#"
Cancel = True
End If
End Sub