Solved

count number of records in a sub-report

Posted on 2003-10-28
10
826 Views
Last Modified: 2012-06-21
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).
0
Comment
Question by:lbeauvais
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 250 total points
ID: 9636350
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
0
 

Author Comment

by:lbeauvais
ID: 9636915
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!
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9636938
Re:>GlobalReport

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

Mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lbeauvais
ID: 9637097
I have just one report named "GlobalReport" and a sub-report in it, named "SubReport".
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9637286
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
0
 

Author Comment

by:lbeauvais
ID: 9637367
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?


0
 

Author Comment

by:lbeauvais
ID: 9637368
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?


0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9639570
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
0
 

Author Comment

by:lbeauvais
ID: 9643813
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
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9644340
I am glad you found a solution.  Please remember not to award points until you get a workable answer,

Take care,

Mike
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question