Solved

count number of records in a sub-report

Posted on 2003-10-28
10
785 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

21 Experts available now in Live!

Get 1:1 Help Now