Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 807
  • Last Modified:

Get specific data from mdb using Crystal Reports

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
nasps
Asked:
nasps
  • 4
  • 2
  • 2
2 Solutions
 
peter57rCommented:
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
 
naspsAuthor Commented:
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
 
peter57rCommented:
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
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.

 
naspsAuthor Commented:
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
 
naspsAuthor Commented:
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
 
mlmccCommented:
Was the report built against an ADO dataset?

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

mlmcc
0
 
naspsAuthor Commented:
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
 
mlmccCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now