count number of records in a sub-report

How can I count the number of records in s sub-report and use it to write a title ( or not if there is no one in the sub-report).
lbeauvaisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Put this code in OnOpen event of the report.

Dim rs 'As Dao.RecordSet
Set rs=CurrentDB.OpenRecordSet(Me!sub-report.Report.RecordSetClone)
If rs.RecordCount>0 Then
rs.movelast
lblTilte.Caption="Number of records are: " & rs.RecordCount
Else
lblTilte.Caption="Zero records."
End IF

Mike

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lbeauvaisAuthor Commented:
Thank a lot Mike,

I am not very hot with VBA code so If you can help me again with that:

If my report name is : GlobalReport
And ny sub-report name is : Subreport

And I if  put the code "Set rs=CurrentDB.OpenRecordSet...
in the OnOpen event of the GlobalReport

What would be the rest of the code: Set rs=CurrentDB.OpenRecordSet...???

And ifI put it in the OnOpen event of the Subreport, what would it be ???

Thank again!
Mike EghtebasDatabase and Application DeveloperCommented:
Re:>GlobalReport

Do you have one report called GlobalReport?  Or, you have many reports defined by GlobalReport variable?

Mike
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lbeauvaisAuthor Commented:
I have just one report named "GlobalReport" and a sub-report in it, named "SubReport".
Mike EghtebasDatabase and Application DeveloperCommented:
Put this code in OnOpen event of the GlobalReport.

Dim rs 'As Dao.RecordSet
Set rs=CurrentDB.OpenRecordSet(Me![sub-report].Report.RecordSetClone)
If rs.RecordCount>0 Then
rs.movelast
lblTilte.Caption="Number of records are: " & rs.RecordCount
Else
lblTilte.Caption="Zero records."
End IF

Mike
lbeauvaisAuthor Commented:
Thank, Your very kind to try to help me!

But I'm sorry it doesn't work!

It gives me an error message like:

Impossible to find 'sub-report' field you refered in your expression. (translate from french)

(I have a french Access 2000 but normaly it is the same code as the english one).

Do you think that could make the bug?


lbeauvaisAuthor Commented:
Thank, Your very kind to try to help me!

But I'm sorry it doesn't work!

It gives me an error message like:

Impossible to find 'sub-report' field you refered in your expression. (translate from french)

(I have a french Access 2000 but normaly it is the same code as the english one).

Do you think that could make the bug?


Mike EghtebasDatabase and Application DeveloperCommented:
I am will offer another solution.  We will start without criteria first therefore the number will be high to begin with.  After adding a criteria, we should get right answer.

In the OnCurent event of the subform, include:

txtNoOfItems="Quantity: " & Nz(DCount("*","TableName"),0)

txtNoOfItems is a text box on your subform where you want to display quantity.
TableName is table where you want to count number of record.

Now, if this works, it will show a very large number (including all items).  We need to add a criteria to limit it to what we need.

Later on, we will cahnge the code to:

txtNoOfItems="Quantity: " & Nz(DCount("*","TableName","CustID=" & Parent!CustID),0)

Please answer to these questions:

Q1: Do you have a text box/field in the main report like CustID to be used to limit quantity?
Q2: Do you have possibly more than one criteria to be used?
Q3: What is variable type of each citria (string, number, date)?

Mike
lbeauvaisAuthor Commented:
Hi Mike,

It will probably be good, but you already gave me the clue to find the solution by myself.
Here is what I did (may be it could help some one else):

I put a hided independent textbox named “DetailsInst” in the under form of the report.
(I tried to put it in the GlobalReport but it does not work, I don’t know why)

In the OnOpen event of the GlobalReport,  I  put this code to verify if there is at least one record in the table used to fill the SubReport, and then fill the “DetailsInst” textbox with the title I want:

    Dim rs As DAO.Recordset
    Dim DB As Database
    Dim IDentificateur As Long
    Set DB = Application.CurrentDb
    Set rs = DB.OpenRecordset("tblInstalsoum", dbOpenDynaset)
   
    IDentificateur = Form_frmSoumissions.IDSoumission
    rs.FindFirst "IDSoumissionInstalSoum = " & IDentificateur
   
    If rs.NoMatch = True Then
        Form_frmSoumissions.DetailsInst = ""  ‘(I don’t want a title if there is none)
    Else  ‘(I want this title if there is at least one record
        Form_frmSoumissions.DetailsInst = "Detail de l'installation"    
   End If
   
    rs.Close
    Set rs = Nothing
    Set DB = Nothing

Then, in the GlobalReport, I put a textbox with this control source :
    =[Forms]![frmSoumissions]![DetailsInst]

And it works!

I know that there is probably a more simple way to do it but the important for me is that it works now.

Thank you very much for the time you took helping me.

Excuse my English I’m a French people.

Regards,

Lucie
Mike EghtebasDatabase and Application DeveloperCommented:
I am glad you found a solution.  Please remember not to award points until you get a workable answer,

Take care,

Mike
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.