Solved

Get specific data from mdb using Crystal Reports

Posted on 2007-11-26
8
786 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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…
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 …

914 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

18 Experts available now in Live!

Get 1:1 Help Now