?
Solved

Access 97 VBA "IsNull" help

Posted on 1999-11-09
16
Medium Priority
?
673 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:TK421
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2194783
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
 

Author Comment

by:TK421
ID: 2195068
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
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2195070
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 

Author Comment

by:TK421
ID: 2195105
Even with the missing "(", I'm still having the same error. AAHHH! - I'm trying.....any ideas?
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2195229
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2195376
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
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2195583
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
 

Author Comment

by:TK421
ID: 2196697
Ok. Wow! Thanks for all the responses. Give me a little while and I'll see ehat works.
0
 

Author Comment

by:TK421
ID: 2220832
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2220857
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
 

Author Comment

by:TK421
ID: 2220979
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2221023
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
 

Author Comment

by:TK421
ID: 2225881
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
 
LVL 10

Accepted Solution

by:
brewdog earned 400 total points
ID: 2225908
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
 

Author Comment

by:TK421
ID: 2225936
I'm such a doof! AAAhhhh duh! That's it! I changed it to "=0" an it works perfectly.
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2225955
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses
Course of the Month3 days, 3 hours left to enroll

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question