Only open form if data present

[spudracer]

Hello again...

I've got a form that will open and display data for a particular user, but what I want the form to do is if there is no data in the child form a message box will pop up and say something like "NO INFORMATION FOR THIS PERSON."

I've asked a similar question before for a report, but don't know if the same process would work here. Also, what would I base the "null" criteria on? Could it be based on two fields?

 

[ghudson]

Something like this in the forms on open event should do what you want...

    If Me.RecordsetClone.RecordCount = 0 Then
        cancel = True
        'MsgBox "No records in data source.", vbCritical, "Form OnOpen"
    End If

Or you could use the Dcount() function and test if there are any records before opening the form. Check the help files on how to format the function.

 

[rainman89]

Yes, You can use the null criteria for 2 fields. I have never done this before, but I don't see why you couldn't check it in the onLoad event

 

[vbaInet]

Yes, You can use the null criteria for 2 fields. I have never done this before, but I don't see why you couldn't check it in the onLoad event

If the form is being opened via a control it's just efficient to check before considering even loading it in memory.

 

[spudracer]

So say I have two fields, "MemberID" and "DecorationID", where would I insert these in the module? Also, how can I maintain the Open form and only display matching records?

I have PK's and FK's set up in both tables.

 

[rainman89]

If the form is being opened via a control it's just efficient to check before considering even loading it in memory.

Good info. Thanks for that

 

[vbaInet]

Good info. Thanks for that

No worries rainman89. I implement this strategy especially when opening reports, pretty neat.

 

[spudracer]

Ok, after using the

    If Me.RecordsetClone.RecordCount = 0 Then
        cancel = True
        'MsgBox "No records in data source.", vbCritical, "Form OnOpen"
    End If

And still using the filters that run before the form opens, it works perfectly!

Thanks guys!

 

[vbaInet]

Ok, after using the

    If Me.RecordsetClone.RecordCount = 0 Then
        cancel = True
        'MsgBox "No records in data source.", vbCritical, "Form OnOpen"
    End If

And still using the filters that run before the form opens, it works perfectly!

Thanks guys!

Lovely. Thanks to ghudson.

 

[AliyuKatsina]

Hello again...

I've got a form that will open and display data for a particular user, but what I want the form to do is if there is no data in the child form a message box will pop up and say something like "NO INFORMATION FOR THIS PERSON."

I've asked a similar question before for a report, but don't know if the same process would work here. Also, what would I base the "null" criteria on? Could it be based on two fields?

On the OnOpen event of the form create a string variable containing an SQL statement to select you data then type this code.
Set MyRec = CurrentDb.OpenRecordset(mySQL)
If MyRec.EOF Then
Rc = 0
Else
MyRec.MoveLast
Rc = MyRec.RecordCount
End If

If Rc = 0 Then
MsgBox "There is no information to display!", vbCritical, "No Data"
DoCmd.CancelEvent
End If

 

[gemma-the-husky]

I normally test the recordsource in the open event for the form

sub form_open(cancel as integer)
if dcount("*",me.recordsource) = 0 then
 msgbox("No Item")
 cancel = true
 exit sub
end if

note that this throws a error code 2501 to the form that tried to open the form, which will need handling.