I am attemtping to write the proper code to get a form to not open if there are no results listed on it. It opens automatically. The form is called Overdue, there is a subform on it called Numbers Subform1 and the field I am looking in to see if it is null is Case_Incident_Number. I cannot seem to get this right.

In the OnOpen property for the main form I have tried something like this:

Private Sub Form_Open(Cancel As Integer)

If Forms!Overdue![Numbers subform1].Case_Incident_Number Is Null Then
DoCmd.Close , Form!Overdue, acSaveNo = acSavePrompt

End If

End Sub

Where am I screwing this up, and is there a better way to achieve my goal? I have several instances where I would like to run a similar procedure.

Thanks as always,
Peter Paul

The Cancel parameter is used to cancel the opening of the form:

Private Sub Form_Open(Cancel As Integer)

If Forms!Overdue![Numbers subform1].Case_Incident_Number Is Null Then
Cancel = True
End If

End Sub

Pat,
thank you for the suggestion, and forgive my ignorance. I entered the code you suggested, and got the error message "Runtime Error 438 Object doesn't support this property or method" And when it goes to the debug screen it is highlighted on this line:

If Forms!Overdue![Numbers subform1].Case_Incident_Number Is Null Then

What else should I try here?

Thanks again for your assistance.

Peter Paul

You can try: If IsNull([Forms]![Overdue]![Numbers subform1].[Form]![Case_Incident_Number] Then....

Thanks Jack, I should have looked further for syntax errors. I guess I wouldn't make a very good compiler. I'd do better as an interpreter http://www.access-programmers.co.uk/ubb/smile.gif

[This message has been edited by Pat Hartman (edited 12-09-2000).]

Pat, you are far too modest. Your skill and understanding of Access is legendary and your ability to explain complex concepts has enlightened me on many occasions.

I am just thankful that I got the above answer correct. It is correct, isn't it?

Thank you for all the answers that I have gleaned from your responses to others and have never taken the time to acknowledge. Thank you!

Jack

Thank you both for the help. I got to work this morning, plugged in your solution and it works perfectly.

Thank you both,
Peter

Using "is null" can be a bit of a fallacy. I have performed similar operations on forms and would recommend that you try;

= ""

instead of "is null".

Checking the contents to see if they conform to a state of null ness doesn’t work because there is nothing there.

adam,
If you want to see if a field has data then ="" will return false whereas IsNull will be true.

A classic example is if you are comparing two fields to see if there have been any changes. If they are both blank, then Field1 = Field2 will return False whereas IsNull(field1) = IsNull(field2) will return True