Solved

Get specific data from mdb using Crystal Reports

Posted on 2007-11-26
8
781 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
 

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

705 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

15 Experts available now in Live!

Get 1:1 Help Now