Avatar of ca1358
 asked on

Access 2007 Conversion-Control Source

I am trying to convert to Access2007 and ran into a problem.

ControlSource:  =frmPairOutBefore.Form.RecordsetClone.RecordCount
Name of Control:  CountPairOutBefore

The expression will not run in Access2007 as a controlsource. And found this link.

See http://allenbrowne.com/RecordCountError.html

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:

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:
    =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.
Visual Basic ClassicMicrosoft Access

Avatar of undefined
Last Comment

8/22/2022 - Mon
Scott McDaniel (EE MVE )

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thank you!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck