Code to assess whether any records in a subfrom

[rdc1963]

I want a subform to display only if there are any records in it. I'm quite happy with the If............Visible bit of the code but I'm struggling with the code to assess whether there are any recodrs present. The subform is based on a query.

 

[starsky51]

I'll show you the way I do it, it probably isn't the most efficient way but it does the trick:

. Place a new text field in the form footer of your subform
. Set the new text field's Control Source to '=Count([AnyField])' where AnyField is one of the fields in the detail section
. Run this form to check that the new text field displays the number of records on the form
. Add a new text field to the main form and point the Control Source at the field in the subform's footer. This field should should change as the number of records in the subform changes
. You can tidy up the subform by shrinking the new text field down to 0,0

Hope this helps,

Good Luck,
starsky51

 

[rdc1963]

thanks Starksky
I've tried this but the problem is when there are no records in the subform the text field in the main form returns #Error.

 

[Garridon]

Visit this web site. They have just what you're looking for (pluse a lot of other things you'll probably find a use for!).

http://www.mvps.org/access/

Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[SeeThru]

Had the same thing - worked fine with some records, returned #error# with 0 records.

I now have a label box. I have written a function that updates it (there are several things that can make it update, that is why it is a private function)

If Not IsNull(ContactID) Then 'if this is a new record (no ID) dont run count.
strSQL = "SELECT CallID FROM Calls WHERE ContactID= " & Me!ContactID & " AND CountExclude=False" 'select record I want to count
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL) 'open the list of records
If rst.RecordCount = 0 Then 'if no records
txtCallCount = 0
Else
rst.MoveLast 'make sure all records counted
txtCallCount = rst.RecordCount 'get record count
End If
rst.Close
dbs.Close
Me![Callcount].Caption = txtCallCount

There is another bit after this if the number counted is greater than a number variable I set earlier, but I left it out for clarity.

Hope this helps.