Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

vb and datareport

Posted on 2003-03-15
14
Medium Priority
?
265 Views
Last Modified: 2010-04-07
Hi,

  I've made a vb program with a datareport that I use to show stats to my client.
  I know how to change data on run time and I do it like this
      DR3.Sections("Section3").Controls.Item(1).Caption = "Something from my database"

  This work fine with textbox object but now I want to chage a label object that is in the Header of my report, how can I do it ?
0
Comment
Question by:deschenesp
  • 7
  • 7
14 Comments
 
LVL 3

Expert Comment

by:QJohnson
ID: 8142957
I change several rptlabel objects in my DataReport in one of my apps.  In my particular case, I read the report header items from a data source and populated some module-level string variables with them.  Then I assigned their values to the rptlables with the code shown below:  

   Dim mySection  As Object
   Dim iControl   As Integer
   Dim sCustID    As String
   Dim sCustName  As String
   Dim sCustAddr1 As String
   Dim sCustAddr2 As String
   Dim sMsg       As String
   
   fnPopulateReportLabels = True
   On Error GoTo FPRLErr
   
   'Call WriteOutIndex     ' for getting control's index
   
   For Each mySection In rptStatement.Sections
      Select Case mySection.Name
     
         Case "PageHeader"
            mySection.Controls(1).Caption = msCompany
            mySection.Controls(2).Caption = msAddr1
            mySection.Controls(3).Caption = msAddr2
            mySection.Controls(4).Caption = msAddr3
           
         Case "cmDeRpt_Grouping_Header"
            mySection.Controls(21).Caption = _
               frmStmtSetup.txtBegDate & " - " & frmStmtSetup.txtEndDate
           
         Case "cmDeRpt_Grouping_Footer"
            mySection.Controls(4).Caption = msCompany
            mySection.Controls(5).Caption = msAddr1
            mySection.Controls(6).Caption = msAddr2
            Debug.Print sCustID
     
         Case "PageFooter"
            mySection.Controls(1).Caption = frmStmtSetup.txtComment.Text
            Debug.Print "comment "; frmStmtSetup.txtComment.Text

      End Select
     
   Next   ' rptStatement Section

<<<<< end of code for changing rptlabel captions at run time  <<<<<

The nasty part, of course, is figuring out which controls have which index numbers.  I actually wrote a short sub to help me with this.  I copied the text from the Debug window into notepad and printed it for my project's documentation.  Very helpful.  When I added new objects to the report, I re-ran it, of course:

   Dim iCtrl   As Integer
   Dim mySect  As Object
   
   For Each mySect In rptStatement.Sections
      Debug.Print " --> " & mySect.Name
      For iCtrl = 1 To mySect.Controls.Count
         Debug.Print "             " & mySect.Controls(iCtrl).Name & " i = " & iCtrl
      Next
   Next
   Set mySect = Nothing
0
 

Author Comment

by:deschenesp
ID: 8143043
Ok first where do I put this : fnPopulateReportLabels = True and what is it for.

The label I want to change is in Report Header,
and I can see how to make work this code, but your code seems great. Just need some little more help on this one.
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8143132
Sorry if this was confusing - I didn't include the function declaration and it was
       Private Function fnPopulateReportLabels() as Boolean
so I just put in that line of code so it would generate a return value.  YOU CAN IGNORE IT - so just delete it from whatever routine you place the rest of the code.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 3

Accepted Solution

by:
QJohnson earned 1000 total points
ID: 8143169
Please note I also omitted the error trapping code.  So the on error goto won't work for you.  

Here is the routine in its entirety>>>>>>

Private Function fnPopulateReportLabels() As Boolean
'*************************************************************
'Procedure:    fnPopulateReportLabels()
'Parameters:   None
'Created on:   08/25/00 By:   QJ
'Description:  We need to generate information on the report that
'              are not linked to any data members.
'Modified On:
'Mod Notes:
'*************************************************************
   Dim mySection  As Object
   Dim iControl   As Integer
   Dim sCustID    As String
   Dim sCustName  As String
   Dim sCustAddr1 As String
   Dim sCustAddr2 As String
   Dim sMsg       As String
   
   fnPopulateReportLabels = True
   On Error GoTo FPRLErr
   
   'Call WriteOutIndex     ' for getting control's index
   
   For Each mySection In rptStatement.Sections
      Select Case mySection.Name
     
         Case "PageHeader"
            mySection.Controls(1).Caption = msCompany
            mySection.Controls(2).Caption = msAddr1
            mySection.Controls(3).Caption = msAddr2
            mySection.Controls(4).Caption = msAddr3
           
         Case "cmDeRpt_Grouping_Header"
            mySection.Controls(21).Caption = _
               frmStmtSetup.txtBegDate & " - " & frmStmtSetup.txtEndDate
           
         Case "cmDeRpt_Grouping_Footer"
            mySection.Controls(4).Caption = msCompany
            mySection.Controls(5).Caption = msAddr1
            mySection.Controls(6).Caption = msAddr2
            Debug.Print sCustID
     
         Case "PageFooter"
            mySection.Controls(1).Caption = frmStmtSetup.txtComment.Text
            Debug.Print "comment "; frmStmtSetup.txtComment.Text

      End Select
     
   Next   ' For Each mySection In rptStatement.Sections

FPRLDone:

   Set mySection = Nothing
   Exit Function

FPRLErr:
   fnPopulateReportLabels = False
   Set mySection = Nothing
   sMsg = "Error while generating report labels. " & vbCrLf & _
      "System's number for the error is: " & Err.Number & vbCrLf & _
      "System's description for the error is: " & vbCrLf & Err.Description
   MsgBox sMsg, vbInformation, gsAppName

End Function

<<<<< end of pasted code.

You obviously will not have gsAppName.  But you probably want to use your own error trapping.  If you want to use mine, just substitue App.Title for gsAppName and it will work nicely.

Also, you can eliminate the Cases that use my custom grouping from the DataEnvironment I used.  If you find it convenient to insert your own name there, fine.  Likely you can ignore the group levels since you are just working with the Page Header section.

I hope this pasted code formats well here.  Ask again if you still have any questions.

Q
0
 

Author Comment

by:deschenesp
ID: 8143201
while running your function a get the error number 438
Description : object doesn't support this property or methode
0
 

Author Comment

by:deschenesp
ID: 8143224
ok I don't have a control(4) so I've just deleted this line but it doesn't change the labels at all.
I did it like this and it dont want to work

   Case "Section4"
           MsgBox ("ALLO") 'the function pass here but the rest dont work
           mySection.Controls(1).Caption = "msCompany"
           mySection.Controls(2).Caption = "msAddr1"
           mySection.Controls(3).Caption = "msAddr2"
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8143278
You only want to change a label in the header right?

If so,  just delete the bottom three cases in the select case statement and remove all the lines of code in the PageHeader case except the first one.

That leaves the Select Case looking like:


     Select Case mySection.Name
     
        Case "PageHeader"
           mySection.Controls(1).Caption = msCompany

     End Select

Now change that only line of code in the PageHeader to:

       mySection.Controls(X).Caption = "YourTextHere"

where X is the control's index that you either already know, or have determined by running the CallWriteIndex subroutine.  A call to it is commented out in the code.  

That routine is the one whose code I posted at the bottom of my first reply here (again, without its subroutine declaration, so add that)... in fact, here's a pastable version of that code to make it easier.

Private Sub WriteOutIndex()
   Dim iCtrl   As Integer
   Dim mySect  As Object
   
   For Each mySect In rptStatement.Sections
      Debug.Print " --> " & mySect.Name
      For iCtrl = 1 To mySect.Controls.Count
         Debug.Print "             " & mySect.Controls(iCtrl).Name & " i = " & iCtrl
      Next
   Next
   Set mySect = Nothing

End Sub
0
 

Author Comment

by:deschenesp
ID: 8143464
it's still not working ...
here's what I get ...

   with the sub WriteOutIndex() I get

    --> Section4
             Label1 i = 1
             Label2 i = 2
             Label3 i = 3
             Line1 i = 4
             lbtest i = 5

lbtest is the label I want to change.
 
   so I call the function this way from my function:

        a = fnPopulateReportLabels()


     and here's your function adapted to my need's

   Private Function fnPopulateReportLabels() As Boolean
'*************************************************************
'Procedure:    fnPopulateReportLabels()
'Parameters:   None
'Created on:   08/25/00 By:   QJ
'Description:  We need to generate information on the report that
'              are not linked to any data members.
'Modified On:
'Mod Notes:
'*************************************************************
  Dim mySection  As Object
  Dim iControl   As Integer
  Dim sCustID    As String
  Dim sCustName  As String
  Dim sCustAddr1 As String
  Dim sCustAddr2 As String
  Dim sMsg       As String
 
  fnPopulateReportLabels = True
  On Error GoTo FPRLErr
 
  For Each mySection In DR.Sections
     Select Case mySection.Name
     
        Case "Section4"
             mySection.Controls(5).Caption = "msCompany"
      End Select
     
  Next   ' For Each mySection In rptStatement.Sections

FPRLDone:

  Set mySection = Nothing
  Exit Function

FPRLErr:
  fnPopulateReportLabels = False
  Set mySection = Nothing
  sMsg = "Error while generating report labels. " & vbCrLf & _
     "System's number for the error is: " & err.Number & vbCrLf & _
     "System's description for the error is: " & vbCrLf & err.Description
  MsgBox sMsg, vbInformation, gsAppName

End Function

what is wrong with the way I do it. I don't get any error and I know that the command mySection.Controls(5).Caption = "msCompany" as been executed but no change in my report.
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8143625
Change the key section of code a bit to help us... try:


       Case "Section4"
            msgbox "Control's name: " & mySection.Controls(5).Name
            msgbox "Current Caption: " & mySection.Controls(5).Caption
            msgbox "Setting new caption to: " & "msCompany"
            mySection.Controls(5).Caption = "msCompany"
            msgbox "After changing caption, it is: " & mySection.Controls(5).Caption


by the way, have you compiled with Cntrl-F5 yet?  I still see gsAppName in the error trap code and I'll bet you don't have it declared any where.  Are you using Option Explicit?
     
0
 

Author Comment

by:deschenesp
ID: 8143670
I finnaly found the problem,

The datareport needed a refresh (DR.refresh)
That's all.

Thank you for your time an your help.

You save me a lot of time.

I'll give you an A+++ for your help.

Thank's again
0
 

Author Comment

by:deschenesp
ID: 8143675
Excellent support...
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8143677
No problem - glad to help.

It's really a pain that we can't use the controls' name properties to do something as simple as

    reportcontrols("MyControlName").Caption = "something"

and its a pleasure to save someone else the frustration I had until I figured out this solution.

That's enough work for a weekend....enjoy yourself <grin>
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8143742
thanks for the comment, deschenesp!
0
 

Author Comment

by:deschenesp
ID: 8143750
no problem
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

577 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