?
Solved

Add text file to Data Report

Posted on 2003-03-18
15
Medium Priority
?
196 Views
Last Modified: 2009-07-29
Using Data Report Designer to create reports from an Access 97 database.  However I have some information stored in a text file that I would like to display in the Data Report.  How can I have the Data Report display the text file?

Thanks in advance for any and all help.

-Jeff
0
Comment
Question by:bowen18
[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
  • 8
  • 7
15 Comments
 
LVL 3

Expert Comment

by:QJohnson
ID: 8161526
If the data is small enough to fit in a text box (less than 32K I believe), you can just use the rptTextBox control.

The pain about this is that you can't simply assign to it.  You have to know the index of the control to do that.

So force your app to tell you the index of the control you want.  I always paste this function into my reports for this purpose:

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

I call it in the report's intialize event and comment it out after I have the values I need from it during my report design.  If I need to add any controls, I just uncomment it and let it run again.

Once you have the index number, you can use code like this to set the caption of your control:

   MySectionName.Controls(IndexNumber).Caption = sBuffer

where sBuffer is the string you have populated by reading your text file.

Do you need code for reading the file?
0
 
LVL 2

Author Comment

by:bowen18
ID: 8161668
Thanks for the quick response.  This is what I've been using on my forms to display the text file.  Are you saying this will work in some form on the Data Report?

Dim MyData As String
    Open "H:\time\data\" & deTime.rscomTime![Name] & ".txt" For Input As #1
    Do Until EOF(1)
        Input #1, MyData
        txtComments.Text = txtComments.Text & vbCrLf & MyData
    Loop
    Close #1
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8162233
Yes, that will work, as long as you substitute properly for the clunky way you have to reference controls on the report.

You could probably avoid some screen flicker and refresh time, by the way, by just using some buffer variable and making a single assignment to the textbox.

i.e.,

sBuffer = ""
Do Until EOF(1)
      Input #1, MyData
      sBuffer = sBuffer & MyData
Loop
Close #1
txtComments.Text = sBuffer

0
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.

 
LVL 2

Author Comment

by:bowen18
ID: 8162386
I apologize, but I'm not sure I understand.  At what point/procedure would I add my text file?  If its not too much trouble, could you possibly provide an example?

Thanks!

-Jeff
0
 
LVL 2

Author Comment

by:bowen18
ID: 8162557
Also, its giving me a varible not defined error on the txtComments.text part.  I double checked and txtComments is the correct name of the text box on the report.  Any ideas?

Thanks,
-Jeff
0
 
LVL 3

Accepted Solution

by:
QJohnson earned 180 total points
ID: 8162729
re: >> , its giving me a varible not defined error on the txtComments.text part <<

This is exactly what I'm talking about.  You can't reference report controls in this fashion. You have to do it via their index in the section's controls collection.  Funky, but true!

Rather than refer you back to the original post, let me offer the instructions again now that you more fully appreciate their necessity.

(1) Copy the following sub's code into the code module for the report object:

>>>>>> code follows >>>>>

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

<<<<<<<<<  end of code to paste
In the intialize event of the report put a call to this function and just let the program run and show the report.  You may want to put a break point on the call to the funtion so that you can step through it (Cntl-F8) and veiw the debug window (Cntl-G) to see what the sub wrote there.  You will see section names and the names of controls in each section and their indexes.  Make a note of those (maybe copy the text there and paste it into Notepad and print it for project documentation?).

The index you see listed for txtComments is the important number we're looking for.

Now - comment out the call to the function (you don't need it anymore, right?).  Then just add a the following code:

Dim sBuffer as String
Dim MyData as String
Dim nHandle as integer
sBuffer = ""
MyData = ""

nHandle = FreeFile

Open "H:\time\data\" & deTime.rscomTime![Name] & ".txt" For Input As #nHandle

Do Until EOF(nHandle)
      Input #1, MyData
      sBuffer = sBuffer & MyData & vbCrLf
Loop
'  if you want to remove the trailing CR/LF pair then do this one, else leave it out
sBuffer = left$(sBuffer, Len(sBuffer) - 2)

PageHeader.Controls(IndexForTxtCommentsGoesHere).Caption = sBuffer

and substitute the proper section name for PageHeader, of course, for whichever one actually holds txtComments.

0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8162745
OK - OOPS!
please get rid of the reference to #1 that I left in the code in that last post and substitute #nHandle.  Sorry about that... I hit submit too fast.

The offending line should read:

    Input #nHandle, MyData

Sorry.
0
 
LVL 2

Author Comment

by:bowen18
ID: 8166717
Wow, that's alot of great information.  Your going above and beyond what I expected!  I do have one issue with the supplied code.  When I run the first part to get the index, it gives me a varible not defined error on rptStatement.Sections.  Any help is very much appreciated.

Thanks,
-Jeff
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8167159
Please type that line of code that give the error.
0
 
LVL 2

Author Comment

by:bowen18
ID: 8167198
> For Each mySect In rptStatement.Sections

the debugger highlights rptStatement.Sections as the culprit.
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8169732
Just replace rptStatement with the name of YOUR report object.
0
 
LVL 2

Author Comment

by:bowen18
ID: 8170073
Thanks for all your help but I'm still having a problem.  Now its with the last line of the code.

You wrote
PageHeader.Controls(IndexForTxtCommentsGoesHere).Caption = sBuffer

I changed it to
Section1.Controls(21).Caption = sBuffer

because Section1 is the Details section which is where txtComments is.  And 21 is the index of the txtComments text box.

The error I'm getting is a "varible not defined" and its looking at Section1 of the Section1.Controls(21).Caption = sBuffer line.  I've used Details.Controls(21).caption and even drUserInfo.Sections(1).Controls(21).Caption with no luck.

Probably another one of my stupid mistakes but would love some help :)

Thanks,
Jeff
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8170654
in my code i'm expecting you have the text you want on the report in a variable called sBuffer.  Is it there?

I actually used rptLabel controls in my app and they certainly had a .Caption property.  Perhaps the text box you are using doesn't.  Perhaps it has a .Text property like standard textboxes.  

So you can try two things.  

(1)  Change the code to

     Section1.Controls(21).Text = sBuffer

If that fails,

(2)  change it back to .Caption, set a breakpoint (hit F9 while you edit the line in the code window), and let the project run.

It will stop at the breakpoint and you can let your mouse hover over the sBuffer part of the code.  The contents of sBuffer will become visible.   Are the contents there???

Obviously you can do these in reverse order if you want.
0
 
LVL 2

Author Comment

by:bowen18
ID: 8174061
I had to modify the code you sent me eariler to grab the section name.  Its as follows:

Dim curSect As Object

For Each curSect In drUserInfo.Sections
        If curSect.Name = "Section1" Then
            curSect.Controls(21).Caption = sBuffer
        End If
Next

Good news is its working this way.  I can't thank you enough for all your help!  If I had a million points, I'd give them to you.

-Jeff
0
 
LVL 3

Expert Comment

by:QJohnson
ID: 8174494
You're very welcome.  

Does it make you wonder if more powerful reporting tools might be useful??? <grin>

FYI, I am a REAL fan of VS View Reporting Edition from ComponentOne.  Among MANY other features, the two I most appreciate are: it allows me to build a report definition in Access, import to this tool and use it in VB to produce a report with three lines of code AND it supports writing PDF files as output (as well as MANY export formats).  

Very cool and worth the money.  But for real value, you probably shouldn't buy just THAT tool.  Their whole suite of tools (Call ComponentOne Studio for ActiveX - or .NET - pick one) is just a little more than the price of the single tool and you get so many other fine controls (including two of the most popular grids in the VB world: TrueDBGrid and VSFlexGrid).

Just one developer's opinion.
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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

770 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