Full Version: Form On Open Not Isnull
UtterAccess Forums > Microsoft® Access > Access Forms
chatnet26
Dear,
exercise a sample form that on open event if there is no record then a pop up message box will appear that there is no record in this event. But If there is a record then this sample form will open. I made a sample vba code but only the no data event is avail while if it has a data the form will not open. Still recalling the no data event.
CODE
If (qryDemo) = 0 Then
    MsgBox "There is no data for this report...!", vbOKOnly, "Canceling report"
        Cancel = True
    End If
If Not IsNull (qryDemo) Then
    DoCmd.OpenForm "frmDemo"
        End If
Tiesto_X
Hi.
Create Textbox on the Form footer and set Visible properties OFF, if you dont need number of records on it.
HAs control source use: =Count(*)
OnLoad event:
CODE
If Me!TextBox = 0 then
MsgBox "There is no data for this report...!", vbOKOnly, "Canceling report"
docmd.close
else
DoCmd.OpenForm "frmDemo"
end if

HTH.
Kamulegeya
Hello
If i have understood you correct, you want the form only to open if there is data to display
There can be several approaches including a recordsetclone for the form
e.g on the load event of the form use
CODE
Dim rst As Dao.Recordset
Set rst = Me.RecordsetClone
      If Not rst.EOF Then
     ' there are records
     code to open form here
Else
' no records
code to close form here..
      
      End If

Ronald
theDBguy
Hi,
ere's yet another way to handle this:
If DCount("*","qryDemo")>0 Then
DoCmd.OpenForm "frmDemo"
Else
MsgBox "There are no records to display.",vbExclamation,"No Records"
End If
Just my 2 cents... 2cents.gif
chatnet26
He Guys,
Thanks to all your quick response and appreciate it very much. All those methods are applicable.
Again, thank you so much...
Best Regards,
Tiesto_X
No problem. Anytime.
L with your DB. : )
datAdrenaline
Are you dealing with a Report or a Form? Your code in the opening post has a message that says something about a Report ... but ... assuming this is a Form object, then as much as I admire and respect theDBguy, I would not suggest the DCount() method. In addition the =Count(*) method is not something I'd recommend either. The reason is simple, when there are records in the query object, the DCount(*) cause you to hit the database twice, which may be a time consuming thing. The Count(*) can also wind up being time consuming.
Here is a generic way I prevent a Form object from opening ...
CODE
Private Sub Form_Load()
    If Me.CurrentRecord = 0 Or (Me.CurrentRecord = 1 And Me.NewRecord) Then
        MsgBox "No Records are returned from the Recordsource"
        DoCmd.Close acForm, Me.Name
    End If
End Sub
chatnet26
He datAdrenaline,
Thanks to your recommendation, appreciated very much. And again I am gaining a great value to all your usual support.
Actually a form but on the message that is just only a supplement requirement on my demo.

Best Regards.
gemmathehusky
I must say, i tend to use dcount, despite it running twice , although brent's code is quite simple.
Oalso do it in the open event, and then cancel the open.
Can you use your code in the open event, brent?
if dcount("*",me.recordsource)=0 then ...
cancel = true
exit sub

Finally bear in mind that stopping the form opening, raises a 2501 error.
datAdrenaline
>> Can you use your code in the open event, brent? <<
Hope ... IIRC the CurrentRecord property is always 0 at the Open event. But you can use the .Recordset (.RecordsetClone) property like Kamulegeya has done, although I would not use an object variable for this exercise ...
CODE
Private Sub Form_Open(Cancel As Integer)
    If Me.Recordset.EOF = True Then
        MsgBox "NO RECORDS"
        Cancel = True
    End If
    
End Sub
datAdrenaline
Hey Db!
>> Although, I am curious... If the issue is this:
Would it still be the same if we use DLookup() instead? For instance: <<
Yes the issue would be the same with DLookup() ... the source will get hit twice if the source has data. And you are right, the original code I provided the form would does not prevent the form from from loading. The only way to completely keep the form out of memory is to prevent to DoCmd.OpenForm call. The best place to halt the Form object from loading is in the Open event with the Cancel argument.
There are definately different ways and preferences to do things that are certainly not wrong.
---
PS> I always enjoy 'deep dives' with you my UA brother!
theDBguy
Hi Brent,
Thanks for replying.
Orealize that both DCount() and DLookup() would cause a double hit on the table but just trying to determine if the cost of hitting the table with DLookup() is less than with DCount() because I presume DLookup() will stop as soon as a match is found. Thinking more about clock cycles because you mentioned in your earlier post that DCount() might be "time consuming."
Any thoughts on that?
theDBguy
Hi Brent,
Thanks for your thoughts. Very interesting topic...
C
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.