Solved

Get specific data from mdb using Crystal Reports

Posted on 2007-11-26
8
792 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

 

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: 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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
A short article about a problem I had getting the GPS LocationListener working.
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…

840 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