Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2017, 10:07 AM   #1
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 29
Thanks: 33
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Unhappy IsNull Problem

Hello Guys,
I have trouble understanding the IsNull process.
I have a CmdButton from a form that opens another form. I would like a message to pop up if there are no cr available to see. My code is on the "load" properties for the 2nd form and here is what I have. Can you please help me?

Private Sub Form_Load()
Dim crname As String
crname = Me.EmpID.Value
If IsNull(crname) Then
crname = MsgBox("No Credit available", vbOKOnly)
Else
crname = ""
End If
End Sub

Thanks so much

PatAccess is offline   Reply With Quote
Old 07-17-2017, 10:23 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,436
Thanks: 8
Thanked 3,576 Times in 3,520 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: IsNull Problem

Try simply

If IsNull(Me.EmpID) Then MsgBox("No Credit available", vbOKOnly)
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 07-17-2017, 10:31 AM   #3
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 29
Thanks: 33
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: IsNull Problem

Not it's giving me an error message
Compile error:
Expected: =
So should I declare a variable? or what am I missing?
Thank you

PatAccess is offline   Reply With Quote
Old 07-17-2017, 10:33 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,436
Thanks: 8
Thanked 3,576 Times in 3,520 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: IsNull Problem

Did you get rid of everything else? That shouldn't throw an error.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 07-17-2017, 10:40 AM   #5
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 29
Thanks: 33
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: IsNull Problem

Yes I took everything off
This what I have now:

Private Sub Form_Load()
If IsNull(Me.EmpID) Then MsgBox("No Credit available",vbOKOnly)
End Sub
PatAccess is offline   Reply With Quote
Old 07-17-2017, 10:44 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,436
Thanks: 8
Thanked 3,576 Times in 3,520 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: IsNull Problem

Try

If IsNull(Me.EmpID) Then MsgBox "No Credit available"
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
PatAccess (07-18-2017)
Old 07-17-2017, 11:09 AM   #7
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,655
Thanks: 36
Thanked 834 Times in 748 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: IsNull Problem

Just to explain why Paul's suggestion is a good one, VBA function and subroutine syntax can be ambiguous with respect to parentheses. There are times when you need them and times when you don't.

Consider this sample:

Code:
    MsgBox "No credit available", vbOKOnly
You are using an IMPLIED "Call" and you are discarding the result of the function.

On the other hand,

Code:
iAnswer = MsgBox( "Press OK to continue or Cancel to abort", VBOKCancel )
Here you are expecting to use the value returned by the MsgBox function, which will tell you whether the user clicked the OK or the Cancel button.

But this case is neither fish nor fowl:

Code:
If IsNull( Me.EmpID ) Then MsgBox("No Credit Available", vbOKOnly)
After the "Then" keyword, the next item is a statement, which in this case begins with an identifier ("MsgBox") followed by parentheses that make it look like you are worried about the return value - but there is no place to PUT that return value. Which is why it wants the equals sign. Essentially, VBA is confused by a statement that does, but does not, want the value of the function.

I have a different take on this, though. Using this syntax,

Code:
crname = Me.EmpID.Value
I'm not sure WHAT you'll get back.

First, you don't need the .Value since for any statement that appears to be an expression, .Value is the default property. But we can let that slide.

Second, you are executing this in the Form_Load routine, which fires BEFORE the Form_Current routine. That means that you would never see any value there. But I don't know that you would ever see a null in that case, either. You MIGHT see an empty string. If I were coding that (in some other event than _Open or _Load), I might use

Code:
If Nz(Me.EmpID, 0) = 0 Then ...
' OR, if EmpID is mixed alphanumeric...
If Nz(Me.EmpID, "") = "" Then ...

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
The Following 2 Users Say Thank You to The_Doc_Man For This Useful Post:
Orthodox Dave (07-18-2017), PatAccess (07-18-2017)
Old 07-18-2017, 04:52 AM   #8
PatAccess
Newly Registered User
 
Join Date: May 2017
Posts: 29
Thanks: 33
Thanked 1 Time in 1 Post
PatAccess is on a distinguished road
Re: IsNull Problem

Thank you Guys for the help and the explanation
This forum is pretty awesome!

PatAccess is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
IsNull/Not IsNull usage in Iif Query headache Eqrepro Reports 12 08-08-2015 03:33 AM
IsNull problem in query excelHk Queries 1 04-08-2014 01:30 PM
If IsNull / If Not IsNull / Reference to MainForm and Subform steve21nj Modules & VBA 15 10-19-2012 06:40 AM
IsNull Problem? robben Modules & VBA 9 07-29-2008 04:49 AM
If IsNull problem in my form christheancient Forms 5 02-26-2003 01:13 PM




All times are GMT -8. The time now is 11:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World