Access 97 VBA "IsNull" help

I am still learning how to use the IsNull parameter - As you can see! What am I doing wrong here? Do I need the reserved word "Me" in this code? If so, where?!?

'Opens the form which runs from a query
If CurrentUser() = "jonesr" Then
DoCmd.OpenForm "Not_Completed_JonesForm", acFormDS, "", "", acReadOnly
End If

'If null then open No outstanding art form
If IsNull([Forms]![Not_Completed_JonesForm]![ID]) Then
DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

'If null then close the form
If IsNull([Forms]![Not_Completed_JonesForm]![ID]) Then
DoCmd.Close acForm, "Not_Completed_JonesForm"
End If

TK421Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wesleystewartCommented:
tk421:

When using IsNull on a control, you have to specify the .value property in your expression:

'If null then open No outstanding art form
If IsNullMe![ID].value) Then
DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

'If null then close the form
If IsNull(Me![ID].value) Then
DoCmd.Close acForm, "Not_Completed_JonesForm"
End If

Give that a shot.  Using the "Me" reserved word is faster and is probably better code.

Wes
0
TK421Author Commented:
Is there a "(" missing in your suggestion?

I can't seem to get it to work. I've done exactly as you've suggested and I get this error:

"...Can't the field in my form..."

Now I KNOW the field DOES exist in the refered form. Any ideas?
0
wesleystewartCommented:
Indeed.

It should read:

'If null then open No outstanding art form
If IsNull(Me![ID].value) Then
DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

'If null then close the form
If IsNull(Me![ID].value) Then
DoCmd.Close acForm, "Not_Completed_JonesForm"
End If

Sorry about the goof

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TK421Author Commented:
Even with the missing "(", I'm still having the same error. AAHHH! - I'm trying.....any ideas?
0
wesleystewartCommented:
Are we sure that the field name is spelled correctly?  Does this code reside in the form that is calling it?  If not, "Me" wont work.

W
0
brewdogCommented:
Two thoughts:

1. Value is only slightly better than worthless in VBA -- it's the default property for almost every control, so it doesn't help anything but clarity (maybe).

2. Is ID a bound field? if so, it's entirely possible that when the field is "blank" it's not null but an empty string. Here's what I might use:

If len(nz([Forms]![Not_Completed_JonesForm]![ID]),"") < 1 Then
  DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

That way I can deal with the text box whether it's null or an empty string. Hope that helps . . .

brewdog
0
JimMorganCommented:
Might not you be looking to see if the form is indeed open or not in this case?

If the CurrentUser is not jonesr, then there is no form opened and the IsNull operation on a non-existant control has not meaning.

It seems, if I read the question correctly that the code should look, perhaps, like this.

'Opens the form which runs from a query
If CurrentUser() = "jonesr" Then
DoCmd.OpenForm "Not_Completed_JonesForm", acFormDS, "", "", acReadOnly
End If

'If null then open No outstanding art form
' Not knowing whether you are testing to see if the form is loaded and/or if is loaded, is there an ID, I put in both cases

If FormIsLoaded("Not_Completed_JonesForm" then
   If IsNull([Forms]![Not_Completed_JonesForm]![ID]) Then
DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
Else     '  form not loaded
    DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

'If null then close the form
If FormIsLoaded("Not_Completed_JonesForm") Then
     DoCmd.Close acForm, "Not_Completed_JonesForm"
End If


Function FormIsLoaded(strFormName as String) as Boolean
   FormIsLoaded = (SysCmd(acSysCmdGetObjectState, acForm, strFormName) = acObjStateOpen)
Exit Function

Jim

0
TK421Author Commented:
Ok. Wow! Thanks for all the responses. Give me a little while and I'll see ehat works.
0
TK421Author Commented:
For brewdog:
I think you are on the right track here. I believe you are correct in stating " when it's blank, it's not null but an empty string."
I need the form "No Outstanding Art" to open if no records are listed in the form that runs from the query.

However, I can't get the code w/o errors. As it is, I'm getting this error:

Compile error
Expected: )
0
brewdogCommented:
Oh! I see it. I put the first parentheses in the wrong place. In fact, let me post the whole code I'd have and you can just cut and paste if you like:

If CurrentUser() = "jonesr" Then
   DoCmd.OpenForm "Not_Completed_JonesForm", acFormDS, "", "", acReadOnly
End If

If len(nz([Forms]![Not_Completed_JonesForm]![ID], "")) > 0 Then
   DoCmd.Close acForm, "Not_Completed_JonesForm"         DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
End If

It looks like you want both the close of not_completed_jones and the open of "no outstanding art" done only if the ID is empty. I closed the not_completed_jones form first because you're opening the "no outstanding art" form as a dialog box, meaning that until it closed, no other code in this sub would run (leaving not_completed_jones still open in the background).

Hope this helps . . .

brewdog
0
TK421Author Commented:
We're close here. Now the code works, but its not functioning as I expected. Please, it is my fault for not conveying it clearly. Let me try again:

1) User clicks a button. The button runs a query and looks to see if the user has records open greater than 30 days. The results are displayed in a form in datasheet view.

2)If the user has no outstanding records, (the form actually open and has no records in it). Close the form and open another form that displays  "Thanks for checking".
3) If the forms DOES open with records, I'd like another form to pop up stating "Please close the outstanding records! bla bla bla"
That's about it.

So, back to my current issue:
I have set so there are no records > 30 days. When the form opens, there is no data. How can the code account for NO records in the form?

I hope this helps without making things worse. What IS difficult for me, I have limited time on Access, I have other work to do. So I aplogize for dragging this out a few weeks. Thank you for your help. I'll sweeten the pot a little.
0
brewdogCommented:
why not do this instead then: take a count of the records involved, and if there aren't any, don't open the form. If there are, then give the user a msgbox, like so:

If DCount("ID", "YourQuery") > 0 then
   DoCmd.OpenForm "No Outstanding Art", acNormal, "", "", acReadOnly, acDialog
else
   msgbox "There are no records. Thanks for checking!"
End If

I assume in this that your query has the criteria stuff built in. If you want, I can be more precise if you post the SQL behind that query.

Usually, as an FYI, I won't open a form in this kind of situation; I'll do the check for records first and then take the appropriate course of action.
0
TK421Author Commented:
Ok, Here's the latest code:

If CurrentUser() = "pickeralr" Then
DoCmd.OpenForm "Not Completed by Pickeral", acFormDS, "", "", acReadOnly
End If
       
If DCount("ID", "Not Completed by Pickeral") < 0 Then
MsgBox "There are no records. Thanks for checking!"
End If

If DCount("ID", "Not Completed by Pickeral") > 0 Then
MsgBox "Please see if you can close some of the requests."
End If

Why doesn't this work?
The condition for "no records" is not working. The form opens with no data. I want it to then close and display the msg "There are no records. Thanks for checking!"
0
brewdogCommented:
well, like I said, I usually don't do it this way, but this line might be your problem:

If DCount("ID", "Not Completed by Pickeral") < 0 Then
   MsgBox "There are no records. Thanks for checking!"
End If

I don't think DCount will ever give a number < 0. :o) what happens if you change that to = 0?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TK421Author Commented:
I'm such a doof! AAAhhhh duh! That's it! I changed it to "=0" an it works perfectly.
0
brewdogCommented:
you're not a doof. It's easy to miss things like that. I once posted a 400-point question because I was so frustrated, only to have the answer be "shouldn't you use ! instead of . to connect those two object names?" duh indeed! Welcome to the club, TK421! :o)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.