Solved

count number of records in a sub-report

Posted on 2003-10-28
10
791 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
  • 5
  • 5
10 Comments
 
LVL 33

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 33

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
 

Author Comment

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

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 33

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 33

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

948 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now