Solved

Get specific data from mdb using Crystal Reports

Posted on 2007-11-26
8
794 Views
Last Modified: 2011-10-03
Hello everyone!
I'm using VB6 application to get data from Access database. I want to start using Crystal Reports 8.5 because it has more capabilities for Reports rather than VB DataEnvironment.
I need to get a specific record from the database to the Report.
The code below demonstrates the code I'm doing. The problem relies that when the variable txtPI.text = [any nymber] the report shows all the records in the database when it was supposed to show only one.

How can I manage this problem?
Thanks in advance.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim appl As New CRAXDRT.Application
Dim rep As CRAXDRT.Report
 
Set rs = CreateObject("ADODB.Recordset")
cn.CursorLocation = adUseClient
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\server\simria\DE\Informações\pias2007.mdb"
Set rep = appl.OpenReport("c:\report1.rpt")
 
sql = "SELECT Pedidos.Departamento FROM Pedidos WHERE (Pedidos.PI='" & txtPI.Text & ")'"
 
rs.Open sql, cn, adOpenKeyset, adLockOptimistic, adCmdText
 
rep.DiscardSavedData
rep.Database.SetDataSource rs
 
 
rep.ExportOptions.DiskFileName = "c:\PDF_report.PDF"
rep.ExportOptions.DestinationType = crEDTDiskFile
rep.ExportOptions.FormatType = crEFTPortableDocFormat
rep.Export False
Dim strfile As String
strfile = "c:\pdf_report.pdf"
ShellExecute 0&, "open", strfile, "", "", MAXIMIZE

Open in new window

0
Comment
Question by:nasps
  • 4
  • 2
  • 2
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20349627
If Pedidos.PI is defined as a number field in the database then you need:

sql = "SELECT Pedidos.Departamento FROM Pedidos WHERE (Pedidos.PI=" & txtPI.Text & ")"

If it is text then you have a typo...it should be:
sql = "SELECT Pedidos.Departamento FROM Pedidos WHERE (Pedidos.PI='" & txtPI.Text & "')"
0
 

Author Comment

by:nasps
ID: 20349863
Hello peter57r
Pedidos.PI is a nunber fiield and I've used the sql statement that you've mentioned. Still the pdf file (crystal report) returns all the records in the database.

Either way, thanks :)
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 60 total points
ID: 20350092
I suggest you add a temporary msgbox at line 13 to display your sql statement so you can check it is OK.

sql = "SELECT Pedidos.Departamento FROM Pedidos WHERE (Pedidos.PI='" & txtPI.Text & ")'"
 msgbox sql   ' temporary line
rs.Open sql, cn, adOpenKeyset, adLockOptimistic, adCmdText
0
Technology Partners: 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:nasps
ID: 20350637
The message box shows that the sql statement is "SELECT Pedidos.Departamento FROM Pedidos WHERE (Pedidos.PI=55)" and still the report shows all the records.
0
 

Author Comment

by:nasps
ID: 20350794
Just adding: the rpt file is a Crystal Report connected to the access database and has only two fields Pedidos.PI and Pedidos.Departamento.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 65 total points
ID: 20352337
Was the report built against an ADO dataset?

Check this example
http://support.businessobjects.com/communityCS/FilesAndUpdates/ADO_ConnectionMethods.zip.asp

mlmcc
0
 

Author Comment

by:nasps
ID: 20365003
Hi mlmcc.
I don't think I've understood your question. The report was built with CR wizard. -> ODBC -> MS Access Database -> *.mdb file.
Do I need to have any special concern with the report?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 20370805
You built the report against a database so you need to pass the record selection criteria to the report.  You cannot change the SQL the report was built against through code.

Try adding this line just before the exportoptions get set.

rep.RecordSelectionFormula = "(Pedidos.PI='" & txtPI.Text & ")'"

mlmcc
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Suggested Solutions

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

685 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