I am including a database in 2003 format and 2007 format.
Correction: I tied to zip 2003 and 2007 but it wont accept 2007 in the zip file on your site. So including 2003 with two forms.
What I need to do is for a MsgBox to count the number of records in the subform that match the main form CommitNumber.
Main Form: frmMaintenance2003
SubForm: frmPairOutBefore
The 2003 format , By clicking on the frmMaintenance2003 shows the Msgbox will show the count.
Form Current
MsgBox "There are " & (CountPairOutBefore) & " " & "Pairouts", vbOKOnly, "History"
So I tried to follow the instruction in the Link , but it is not working.
Any help would be greatly appreciated.
I am including the page:
********************************************************8 http:/allenbrowne.com/RecordCountError.html
Public Function FormHasData(frm As Form) As Boolean
'Purpose: Return True if the form has any records (other than new one).
' Return False for unbound forms, and forms with no records.
'Note: Avoids the bug in Access 2007 where text boxes cannot use:
' [Forms].[Form1].[Recordset].[RecordCount]
On Error Resume Next 'To handle unbound forms.
FormHasData = (frm.Recordset.RecordCount <> 0&)
End Function
Now use this expression in the Control Source of the text box:
=IIf(FormHasData([Form]), Sum([Amount]), 0)
1. Leave the [Form] part of the expression as it is (i.e. do not substitute the name of your form.)
2. For Access 97 or earlier, use RecordsetClone instead of Recordset in the function.
3. A form with no records still has display problems. The workaround may not display the zero, but it should suppress the #Error.
________________________________________
In reports
Use the HasData property property, specifically for this purpose.
So, instead of:
=Sum([Amount])
use:
=IIf([Report].[HasData], Sum([Amount]), 0)
If you have many calculated controls, you need to do this on each one. When Access discovers one calculated control that it cannot resolve, it gives up on calculating the others. Therefore one bad expression can cause other calculated controls to display #Error, even if those controls are bound to valid expressions.
For details of how to do this with subreports, see Bring the total from a subreport onto a main report. FormHasData.mdb