Close form with no records

[tfaiers]

It seems so simple to do, but I've spent ages looking through the forums and haven't found a simple implementation of it.

How would you close a form based on a query called 'Retired' if no records are found?

I assume it's going to be a bit of VBA code, but I'm still learning VBA. :-(

 

[KenHigg]

Are you opening the form from a command button?

 

[tfaiers]

The form opens when the database first opens, so there is VBA code in the Switchboard to automatically open the form over the top of the switchboard, but obviously I need it not to open the form if no records from the query are found.

 

[KenHigg]

Can you do a dcount() to see if any records exist, then if the dcount() returns a '0', skip opening the form...

Would this work?

 

[Jibbadiah]

Based on Kens response

Do a query against that query with a count... where count of records equals zero run the follow procedure

Public Function Close_Form(Formname)
Dim dbs As Database
Dim Frm As Variant
Dim bisopen As Boolean

bisopen = False
Set dbs = CurrentDb

For Each Frm In Forms
If Frm.Name = Formname Then
bisopen = True
End If
Next Frm
If bisopen = True Then
DoCmd.Close acForm, Formname, acSaveNo
End If
Set Frm = Nothing
Set dbs = Nothing

End Function

 

[maxmangion]

put the following on the onLoad event of your form

If Me.RecordSetClone.RecordCount = 0 Then
DoCmd.Close
End If

you can even put a msgbox before the docmd.close statement if you like.

 

[tfaiers]

Although I assume that would work, would the Dcount function not be easier, or does it have to be that complicated to close a form with no records?

I've tried in the On Open in the Switchboard VBA code:

If DCount("Reference","Notification",>0) Then DoCmd.OpenForm "Notify"

Where Reference is a field name, Notification is the query and Notify is the Form name

But that doesn't work

 

[KenHigg]

If dcount("*","myQueryName") <> 0 then
open form
End if


???

 

[tfaiers]

Many thanks all of you, those final solutions have worked perfectly for the solution to my problem.

What would I do without the Access Programmers Forum