Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Access 2007 Conversion-Control Source

Avatar of ca1358
ca1358 asked on
Microsoft AccessVisual Basic Classic
2 Comments1 Solution357 ViewsLast Modified:
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.