Using MySQL, Visual Basic 6, and Crystal Reports 9
Introduction
So, you have a huge amount of data in your MySQL™ database, and your users can move data in and out at will with the VB application that you wrote, but now they want the data in a more meaningful form. In the first Visual Basic application I wrote, I tried to give them summaries and reports by creating forms with dynamically allocated arrays of labels, building the form as I worked my way through the data.
While such a method worked, and the form looked fairly nice, making changes was difficult. Moving elements around on the form required modification of various constants that were used to control placement and spacing, and changes required a re-compile (granted, I could have loaded the constants from a text file, but I was new). Later, I discovered Crystal Reports®, a powerful tool for report generation. With Crystal Reports, I was able to rebuild my code-based report in a matter of minutes, and modify it at will. When a change was made, I simply had to replace the report file, and Visual Basic would show the new report the next time it was viewed. This really helped me save time on data reporting, and I was able to churn out new reports any time a user would request one.
One of the challenges I had when trying to work with Crystal Reports was getting the report to show in my VB application with data generated using an ADO recordset and MySQL. In fact, with the version of Crystal Reports that comes included with VB6 (version 4.6 of Crystal Reports), this feature was not really supported. I personally prefer to develop software without using a DSN, but in this case I had to make an exception and set up a DSN on my target system so that Crystal Reports could access live data, with the VB application modifying the selection formula of the report in order to allow the user control over what data was displayed in the report.
Fortunately, when I found myself a developer position in a company that is a reseller for Crystal Reports, and I found myself able to work with Crystal Reports 9. Moving from Crystal Reports 4.6 to Crystal Reports 9 was an improvement similar to moving from hard-coding reports to using Crystal Reports. Version 6 of Crystal Reports introduced Active Data, which allows us to create reports based on ADO provided data. Version 8 of Crystal Reports introduced the Report Designer Component, which greatly improved the way reports are loaded and viewed in VB. In this article I will cover designing and displaying reports using Visual Basic 6, Crystal Reports 9, and MySQL. In a future article I will also cover displaying data with Crystal Reports versions 6 and 4.6.
This article will assume that the reader is using the MySQL RDBMS, but should apply to developers using other database management systems. For an overview of why MySQL is a good choice for Visual Basic developers, see the Why VB/MySQL article on this site. Further information on MySQL, along with full downloads, can be obtained from the MySQL web site. This sample will require the latest version of Connector / ODBC (formerly MyODBC), available for download here (currently 3.51.06). As I stated, this article will be based on Crystal Reports 9 (evaluation copy available for download here). I recommend installing maintenance pack 1 and all monthly hot fixes, available through support.crystaldecisions.com.
On the Visual Basic side, I recommend you have service pack 5 installed for VB, which you can download here. Additionally, the latest version of MDAC (Microsoft Data Access Components) should be installed, and can be found here (currently 2.7 SP1). Finally, this article applies to ADO 2.7 and VB6. It is not applicable to ADO.NET under VB.NET (Unless you reference ADODB in your project and use it for database access).
Creating The Report
Anatomy Of A Report
A crystal report is composed of several sections:
- Report Header
- Page Header
- Group Header
- Detail Line
- Group Footer
- Page Footer
- Report Footer
Anything placed in the report header and footer will be displayed once in the report. Items in the page header and footer will be displayed on every page. Group headers and footers will be shown once per group of detail lines, and there will be a detail line for every row of data. All sections can be either hidden or suppressed so that they do not appear in the report: hidden sections can be displayed by ‘drilling-down’ through double clicking on a related section that is displayed, suppressed sections are not displayed at all.
Various information can be placed in any of the report sections including:
- Text Labels
- Database Fields
- Images
- Graphs
- Summaries
- Formulas
Most of these are self-explanatory. Summaries include totals, counts, averages and other aggregate values based on different fields. Formulas allow the report designer to add complex logic and formatting to different fields.
Creating The Field Definitions
Creating a typical report is very simple. First we define the fields that we will be reporting on, then arrange those fields on the form, grouping and summarizing as needed. A field definition file allows us to define the data we will be accessing in our report. Lets start with the table we will be reporting from:
CREATE TABLE report(
transactionid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
salesman CHAR(20) NOT NULL,
region CHAR(20) NOT NULL,
amount FLOAT
);
To create a field definition, we bring up the Crystal Reports database expert. The database expert can be accessed by opening a blank report, or by clicking theDatabase Expert menu option under the Database drop-down menu. The available connections treeview will have a Create New Connection branch that when expanded will reveal the Field Definitions Only option. By clicking Create File… button on the file browser dialog, a tool for creating field definition files will be displayed.
Field definition files are tab separated with .ttx extensions with four values:
- field name
- field type
- field length (if string)
- sample data
Field name is taken directly from the name of the field in your recordset (this could be the field name from your underlying table, or the alias you assigned to it using the AS keyword in your query), field type is the datatype of the field, field length specifies the width of a string, and sample data is a static value used to assist in laying out the fields.
A field definition file for the table defined above might look like the following:
transactionid uLong 1 salesman String 20 George region String 20 Calgary amount Currency 30.00
Once we have created our field definition file, we can use it to design our report by dragging it from the ‘Available Data Sources’ box to the ‘Selected Tables’ box in the Database Expert and clicking the OK button. Before we go any further, it should be noted that a .ttx file can also be created automatically from a recordset. This can be useful for determining which report datatypes to use, But I find you still need to go in after and give meaningful sample values. Here is a quick sample of how to automatically generate a .ttx file, create a new project with a reference to ADO 2.7 and place the following code in the code section of form1.:
Option Explicit
'p2smon.dll MUST BE PRESENT IN SYSTEM DIRECTORY
'IT CAN BE FOUND IN THE \TOOLS\DEVELOPERS SUBFOLDER
'OF YOUR CR9 DIRECTORY
Private Declare Function CreateFieldDefFile Lib "p2smon.dll" (lpUnk As Object, _
ByVal fieldDefFile As String, ByVal bOverWriteFile As Long) As Long
Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim result As Long
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=123.456.789.101;" _
& "DATABASE=test;" _
& "UID=myuser;" _
& "PWD=mypass;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM report", conn, adOpenStatic, adLockReadOnly
'PASS RECORDSET, PATH TO GENERATED FILE, TRUE/FALSE TO OVERWRITE
result = CreateFieldDefFile(rs, App.Path & "\report.ttx", True)
If result Then
MsgBox "File " & App.Path & "\report.ttx created successfully", vbInformation, "Success!"
Else
MsgBox "Failed to create ttx file!", vbCritical
End If
End Sub
Placing Fields, Grouping, and Summarizing
By choosing the Field Explorer under the View menu, we can use the field explorer to choose fields and place them onto the form. After dragging each of the fields onto the form, we can group data to make it more meaningful.
Under the Report menu, the Group Expert option will bring up the Grouping Expert. With the grouping expert we can choose which fields we will be grouping by (in our case region, then salesman).
Once grouping is specified, we will see the two groups added to our report, with the group names now shown as part of the main report in the group header sections. By right-clicking on these two section headers, we can choose the suppress option to prevent these group headers from being displayed.
To summarize data, we click on the field we wish to summarize, and choose Summary from the Insert menu. The Insert Summary dialog gives us options to choose which field to summarize (in our case the amount field), what summary to perform (we will calculate the Sum), and where the resulting value is to be placed (will will place one in the footer for each group and in the report footer). The summaries are then available on the report for us to format and place.
Adding Formulas
Once our data is placed on the report, we can create calculated fields by creating formulas. Formulas can be written in either the Crystal Reports syntax, or in CR9, we can use Visual Basic syntax. Lets add a simple formula to display commission for our sales; in the field explorer, right click on the Formula Fields header and choose New, this will display the formula editor after prompting you for a formula name (commission will suffice for a name). In the top right you will find a drop-down box displaying Crystal Syntax, change it to say Basic Syntax so we can work with Visual Basic type syntax. Next, in the formula window type the following:
formula = {article.amount} * 0.05
Now you can click save and close the formula window. The new formula is now available in the field browser and can be placed on the report in the detail section. We can add summaries of the formula if we wish.
Well, that gives us a basic report, you can press the F5 key to see a preview of the report using the sample data you entered in your .ttx file. You next step will be to show the report to your users in your VB application using data retrieved from MySQL.
Displaying The Data
With Crystal Reports 8 and higher, the OCX Crystal Viewer is done away with to be replaced with the Report Designer Component. This approach allows for a more object-oriented approach to displaying reports. One benefit is that the report viewer is not placed directly on the form, allowing for the report to appear in a specific region of the form without spawning a separate form.
The process for displaying the report in VB is fairly simple:
- instantiate ADODB Connection and Recordset objects
- populate recordset object with query
- instantiate Crystal Reports Application and Report objects
- open report
- bind report to recordset
- bind report to viewer
- show report
To display a report in your Vb application you will need the following:
References:
- Microsoft ActiveX Data Objects 2.7 Library
- Crystal Reports 9 ActiveX Designer Run Time Library
Components:
- Crystal Reports Viewer Control 9
You will also need a Crystal Reports Viewer Control placed on your form named CRViewer.
Preparing Data
First we are going to need the data that we will be reporting on. This is done using ADO:
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient 'SERVER-SIDE NOT RECCOMENDED
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=123.456.789.101;" _
& "DATABASE=test;" _
& "UID=myusername;" _
& "PWD=mypass;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841 'SET ALL PARAMETERS
conn.Open 'THESE OPTION VALUES ARE BEST FOR VB
conn.Execute "DROP TABLE IF EXISTS report", , adExecuteNoRecords
'ADEXECUTENORECORDS IS USED FOR
'QUERIES THAT RETURN NO DATA
'(CREATE, INSERT, DELETE, ETC.)
conn.Execute "CREATE TABLE report(" _
& "transactionid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY," _
& "salesman CHAR(20) NOT NULL," _
& "region CHAR(20) NOT NULL," _
& "amount Float)", , adExecuteNoRecords 'THE TABLE WE WILL REPORT OFF OF
'PUT SOME DATA IN THE TABLE
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','Calgary',30.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','Calgary',15.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('George','San Jose',10.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('Jerry','Calgary',30.00)", , adExecuteNoRecords
conn.Execute "INSERT INTO report(salesman, region, amount) VALUES('Jerry','San Jose',100.00)", , adExecuteNoRecords
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM report", conn, adOpenStatic, adLockReadOnly
Preparing and Displaying the Report
Now that we have a recordset, we just need to load the report, link it to the recordset and the viewer, and display it:
Dim crystal As CRAXDRT.Application 'LOADS REPORT FROM FILE Dim report As CRAXDRT.report 'HOLDS REPORT CRViewer.DisplayBorder = False 'MAKES REPORT FILL ENTIRE FORM CRViewer.DisplayTabs = False 'THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED CRViewer.EnableDrillDown = False 'REPORT DOES NOT SUPPORT DRILL-DOWN CRViewer.EnableRefreshButton = False 'ADO RECORDSET WILL NOT CHANGE, NOT NEEDED Set crystal = New CRAXDRT.Application 'MANAGES REPORTS Set report = crystal.OpenReport(App.Path & "\report1.rpt") 'OPEN OUR REPORT report.DiscardSavedData 'CLEARS REPORT SO WE WORK FROM RECORDSET report.Database.SetDataSource rs 'LINK REPORT TO RECORDSET CRViewer.ReportSource = report 'LINK VIEWER TO REPORT CRViewer.ViewReport 'SHOW REPORT Do While CRViewer.IsBusy 'ZOOM METHOD DOES NOT WORK WHILE DoEvents 'REPORT IS LOADING, SO WE MUST PAUSE Loop 'WHILE REPORT LOADS. CRViewer.Zoom 94
Once the report is displayed, we can then cleanup out objects:
rs.Close 'ALL BELOW HERE IS CLEANUP Set rs = Nothing conn.Close Set conn = Nothing Set crystal = Nothing Set report = Nothing
Finally, we can add some code to the Form_Resize event to ensure that our report occupies the entire form:
Private Sub Form_Resize() 'MAKE SURE REPORT FILLS FORM CRViewer.Top = 0 'WHEN FORM IS RESIZED CRViewer.Left = 0 CRViewer.Height = ScaleHeight CRViewer.Width = ScaleWidth End Sub
Conclusion
And there you have it. If all goes well, you now have a report displaying on your VB form, generated from MySQL data. From here you can look at creating more complex formulas, pulling data from multiple recordsets, and reporting on queries more complex than your typical SELECT * query.
Crystal Reports is a registered trademark of Crystal Decisions: www.crystaldecisions.com
November 22nd, 2006 at 10:13 am
Nice.
December 15th, 2006 at 7:11 pm
how can i change the connection string at runtime? tks.
January 17th, 2007 at 2:17 am
pls do try to give examples on how to print official receipt using vb6-Mysql… thanh you.
February 2nd, 2007 at 6:18 pm
Hi,
Would it be possible for you to let me know how to create a Data Definition file ( Ascii text file with placeholders to represent data fields). I would appreciate your assistance in this matter.
Thanks and regards
February 7th, 2007 at 11:45 pm
Why cant i get to see someone acctually showing this sort of thing using ODBC instead of ADODB or OLEDDB………….. so frustrated i need to do my project soon im bloody stuck on this thing if anyone some samples how to display data using crystal report would be nice. i am using Visual Studio.NEt 2003 and MySQL i need help.
email tosi_uk@yahoo.co.uk
February 8th, 2007 at 1:22 pm
I solved the problem I was having. I was picking up the data from Oracle recordset and this was filling up the data in the data definition table. Thanks for the solution.
March 8th, 2007 at 12:39 pm
Very nice…
That really helped me.
March 8th, 2007 at 1:23 pm
hi all,
if anyone needs help on displaying the data on CR from VB.NEt using odbc let me know sorted…
March 21st, 2007 at 3:50 am
can u tell me that.. how to attache crystal report 8 with viusal basic 6.0..through parameters.
thx
March 27th, 2007 at 3:28 am
COOL! Thanx! This helped me!
April 15th, 2007 at 4:17 am
Please send me a sample program Using MySQL, Visual Basic 6, and Crystal Reports 9
April 25th, 2007 at 9:53 am
please send me a simple programe how to make creystal report in visual basic
April 25th, 2007 at 9:57 am
how we use sql as back hand in visual basic
April 26th, 2007 at 3:23 pm
Can someone send me code for displaying crystal report using VB6, Crystal Report 11 and ODBC Connection…
Thanks
May 23rd, 2007 at 5:20 am
Hi
can anyone tell me how to read the used Database fields alone from a crystal report file using Visual Basic Code
May 24th, 2007 at 9:55 am
HI,
I still can view full data list of a table. I can not see separate rows of data when run a query accordingly.
Povil
May 24th, 2007 at 9:56 am
HI,
I still can view full data list of a table. I can not see separate rows of data when run a query accordingly. View all the data at design time still.
Povil
May 26th, 2007 at 9:49 pm
Please send me a sample program Using MySQL, Visual Basic 6, and Crystal Reports 9
May 27th, 2007 at 11:09 am
hi
i want to display start date and end date in report header ; directly to report(not through any database field) how can i do?
i used vb2005 and crystal report 11.
pls help me
May 28th, 2007 at 9:38 am
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary>50000″
Set rst3 = cnn.Execute(sSql2)
Set crystal = New CRAXDRT.Application
Set Report = crystal.OpenReport(App.Path & “\crReportFirst.rpt”, 1)
Report.Database.SetDataSource rst3, 3, 1
Report.DiscardSavedData
CRViewer.ReportSource = Report
CRViewer.ViewReport
End Sub
May 28th, 2007 at 9:43 am
Soryy. That was wrong. Here is the code.
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary
May 28th, 2007 at 9:55 am
sorry here is the full code. Pl discard previous two.
Private Sub cmdPreview_Click()
dbOpen
CRViewer.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
sSql2 = “SELECT * FROM employee WHERE salary
May 28th, 2007 at 9:56 am
sorry here is the full code. Pl discard previous two.
Private Sub cmdPreview_Click()
dbOpen
sSql2 = “SELECT * FROM employee WHERE salary
May 29th, 2007 at 3:22 pm
I want to display the image in crystal report using VB 6.0, I ve tried but the data is being displayed but Image is not displayed, Anyone can help me?
June 4th, 2007 at 1:05 pm
Thank u
this code definitely help me in making dynamic crystal report,but can u provide any method to set the field header
June 18th, 2007 at 4:34 am
I can not change the data dynamically. Report always gives data like at design time. The code is showing below.
Private Sub cmdPreview_Click()
sSql = “SELECT * FROM employee WHERE salary
June 18th, 2007 at 5:46 am
SORRY AGAIN. I HAVE A PROBLEM OF SENDING A COMMENT TOO. :(. Only the most upper part of my comment is viewed. IS THIS A ERROR OF MY BROWSER OR CONNECTION? WHAT I WANTED TO KNOW IS DESCRIBED BELOW. HOPE THIS TIME I CAN SEND ALL.
!!
June 18th, 2007 at 6:02 am
AAAAHHHHHHHH. Can anyone help me pl? I can’t send a comment.
June 18th, 2007 at 3:55 pm
Hi, I am using MYSQL 5.0, and Microsoft Visual Studio 2005, I cant seem to figure out how to get Crystal Reports to connect to my database, if anyone could give me a quick crash course in what I am doing wrong I would really appreciate it.
June 24th, 2007 at 2:38 am
how to use vb and cr9 with subreport?
i have 2 field definition file in my report and 1 subreport that use field definition too.
the report successfull bring data when i using:
report.database.setdatasource rs,,1
report.database.setdatasource rs,,2
but the sub report didn’t display any record.
i tring to use
report.opensubreport(”mysubreport.rpt”).database.setdatasource rs2,,1
but it failed too..
how to make myreport display record from sub report using vb?
June 25th, 2007 at 6:32 pm
YO Kool but can someone help me? I need to allow the report to change the table name at runtime
eg. “select * from orders……..
to “select * from temp_orders ……..
all this at run time
June 26th, 2007 at 8:51 am
use alter table before select statement
June 26th, 2007 at 8:57 am
sql=”alter table orders rename temp_orders”
conn.execute sql
sql=”select * from temp_orders”
conn.execute sql
i think it’s just like that..
June 30th, 2007 at 1:14 pm
I am using the designer of cystal report 9 in MS Visual Basic 6 and I am using data field definition for the stored procedure that I create to view the records, thru recordset. The problem is if I am using a parameter in my stored procedure to filter the records there is no result set in my crystal report viewer at runtime in VB, but if “I am not using a parameter” to filter the records it shows “all the records at run time in MS VB6″??? just like I wanted in my project, how can I show the records at runtime in MS VB6 thru recordset using ADO in crystal report by using “parameters” to filter out my data? can anyone help me, thanks!!
June 30th, 2007 at 1:18 pm
additional to my questions is that, I am using a parameters in my stored procedure so that the recordset in my project is alredy filtered.
July 10th, 2007 at 3:01 pm
i have made crystal reports and m using it in vb6.0. they are working fine with .net framework 1.1 but not working with 2.0 can anyone suggest me some solution
August 6th, 2007 at 6:49 am
How to display crystal report XI using vb 6.0
August 7th, 2007 at 10:19 am
Thanks for the tip on CRViewer.Zoom, I’ve been struggling to get that to work for ages
August 8th, 2007 at 5:11 am
please tell me the code to display the crystal report 11 using vb 6.0
August 8th, 2007 at 8:48 pm
I think It is very help full to me becaus it is simple and nice i not tried yed but when we use RedcordSet object it eliminate but one Question “where we use selection formula “
August 23rd, 2007 at 4:14 pm
Hi,
Am using the Crystal Report Engine Automation API.
I display the report(s) using the syntax below:
frmPreview.RunRpt report_path ,param1, param2, param2
frmPreview has the following code:
Private CrystalApp As New CRPEAuto.Application
Private WithEvents CrystalRpt As CRPEAuto.Report
Dim dboRepDB As CRPEAuto.database
Dim tbsRepDB As CRPEAuto.DatabaseTables
Public Sub RunRpt(rptPath As String, sTitle As String, Optional rFromDate As String, Optional rToDate As String)
Dim intTeller As Integer
Set CrystalRpt = CrystalApp.OpenReport(rptPath)
Set dboRepDB = CrystalRpt.database
Set tbsRepDB = dboRepDB.Tables
CrystalRpt.DiscardSavedData
CrystalRpt.FormulaFields(”Title”).Text = “‘” & param1 & “‘”
If param2 “” Then
CrystalRpt.FormulaFields(”param2 “).Text = “‘” & param2 & “‘”
End If
If param3 “” Then
CrystalRpt.FormulaFields(”param3 “).Text = “‘” & param3 & “‘”
End If
If Not CrystalRpt Is Nothing Then
Me.Caption = sTitle
CrystalRpt.Preview sTitle, , , , , 16777216 + 524288 + 131072 + 65536, Me.hwnd
End If
Screen.MousePointer = vbDefault
Set tbsRepDB = Nothing
Set dboRepDB = Nothing
Exit Sub
ErrHandler:
Screen.MousePointer = vbDefault
Select Case err.Number
Case 20545
Exit Sub
Case Else
MsgBox err.Description & “: ” & err.Number & “, ” & err.Source
End Select
Set tbsRepDB = Nothing
Set dboRepDB = Nothing
End Sub
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Set CrystalRpt = Nothing
End Sub
Sub on_close()
Unload Me
End Sub
Uisng the above code I get the error: Run-time error ‘20599′ Cannot open SQL Server
September 10th, 2007 at 3:04 pm
I have tried this atciles and it’s work, but i had modify the code in query sql, i add parameter in query to control which record will be display, but it didn’t work cant anyone help me, here the code :
Dim crystal As CRAXDRT.Application ‘LOADS REPORT FROM FILE
Dim Report As CRAXDRT.Report ‘HOLDS REPORT
Private Sub Form_Load()
If sConn.State = 1 Then
Tutup
End If
Buka
If sRec.State = adStateOpen Then sRec.Close
sComm.CommandText = “select * from exim_beacukai_trc_bc40,exim_beacukai_trc_bc40_dtl where exim_beacukai_trc_bc40_dtl.no=exim_beacukai_trc_bc40.no and exim_beacukai_trc_bc40.no=’” & FlagDataSearch1 & “‘”
sComm.ActiveConnection = sConn
sRec.Open sComm, , adOpenDynamic, adLockOptimistic
CRViewer91.DisplayBorder = False ‘MAKES REPORT FILL ENTIRE FORM
CRViewer91.DisplayTabs = False ‘THIS REPORT DOES NOT DRILL DOWN, NOT NEEDED
CRViewer91.EnableDrillDown = False ‘REPORT DOES NOT SUPPORT DRILL-DOWN
CRViewer91.EnableRefreshButton = False ‘ADO RECORDSET WILL NOT CHANGE, NOT NEEDED
Set crystal = New CRAXDRT.Application ‘MANAGES REPORTS
Set Report = crystal.OpenReport(App.Path & “\ReportProgram\Report1.rpt”) ‘OPEN OUR REPORT
‘Report.DiscardSavedData ‘CLEARS REPORT SO WE WORK FROM RECORDSET
‘Report.Database.SetDataSource sRec ‘LINK REPORT TO RECORDSET
Report.ParameterFields(0).AddCurrentValue FlagDataSearch1
CRViewer91.ReportSource = Report ‘LINK VIEWER TO REPORT
CRViewer91.ViewReport ‘SHOW REPORT
Do While CRViewer91.IsBusy ‘ZOOM METHOD DOES NOT WORK WHILE
DoEvents ‘REPORT IS LOADING, SO WE MUST PAUSE
Loop ‘WHILE REPORT LOADS.
CRViewer91.Zoom 94
sRec.Close ‘ALL BELOW HERE IS CLEANUP
Set sRec = Nothing
Set crystal = Nothing
Set Report = Nothing
End Sub
Private Sub Form_Resize()
CRViewer91.Top = 0
CRViewer91.Left = 0
CRViewer91.Height = ScaleHeight
CRViewer91.Width = ScaleWidth
End Sub
but viewer didn’t control which record will be load based on my parameter ( flagdatasearch1)
Anyone can help me.
Thx’s
September 18th, 2007 at 10:07 am
sir ,
i am having a problem in a my school s/w .i want to create a long sheet to print a big data sheet. which is only created by crystal report ,but i dont know anything abt crystal report so plz help me if posible to anyone
October 4th, 2007 at 9:37 am
Hi,
Anybody know how to connect a mysql database with CR11.
When I try to make a connection through ‘Create New Connection’->’ODBC RDO’ or ‘Create New Connection’->’OLE DB (ADO)’ it give error and application close automatically.
October 25th, 2007 at 5:12 am
can any body tell me how i can make ledger in crystal report.
my email address is muddasirmunir@yahoo.com
October 26th, 2007 at 5:01 am
Anyone can tell me, how to print half page of A4, with CR9, about the custom size , i tried make custome size for half page, page it still print full page!, please advice me at acholyte_rbo@yahoo.co.id. Thx
November 2nd, 2007 at 4:48 am
hi,
i have developed a web browser in visual basic 6 when im trying to open a crystal report its not opening can any tell how to display crystal reports in VB
November 2nd, 2007 at 7:44 am
no body is there to give the correct solution.
Please let me know
what we need to do to display the report(view the report using vb)
using sybase.
November 2nd, 2007 at 7:46 am
Dim RptName As String
Dim tmpName As String
Dim q As String
RptName = App.Path & “\exec\r1a15b_1.rpt”
tmpName = “Report”
On Error GoTo Chk_Err
q = “SELECT * from ap_voucher_upload ”
Crpt.ReportFileName = RptName
Crpt.SQLQuery = q
Crpt.WindowTitle = tmpName
Crpt.Connect = “DSN=phase2;UID=” & user_name & “;PWD=” & user_pwd & “;database=fasctpr50″
Crpt.UserName = user_name
Crpt.Password = user_pwd
Crpt.PrintReport
Exit Sub
Chk_Err:
If Err.Number = 20507 Then
MsgBox tmpName & ” [” & RptName & “] could not be found.” & Chr(13) & “Please make sure the Report exists in” & Chr(13) & App.Path & ” before proceeding.”, vbExclamation, “GL Cashflow Report”
End If
November 5th, 2007 at 6:34 am
How about if you’re using visual foxpro 9 as your platform in programing?How can you create a crystal report? thanks..
November 5th, 2007 at 9:09 am
I have created a browser in VB i want to create a product key and license for my browser can anyone plz help me
November 22nd, 2007 at 7:49 am
How to Using SQLServer, Visual Basic 6, and Crystal Reports 9
Tell Me Pls.
Kow
November 22nd, 2007 at 11:58 am
how about if you are using an microsoft visual fox pro 9? Can you have an idea on how to connect one table to another table of a database?…i am presently creating my research proposal (Thesis) and my topic is all bout microsoft visual foxpro 9 connected to mySQL..if you dont mind please create me a simple program showing the procedures..This will help me very much!thanks….i’ll wait for you’re response regarding my topic..hope you’ll grant my wish!thanks again!
November 29th, 2007 at 3:22 am
I need your help ? Urgent ..
I’ve got 1 textbox in Visual Baasic 6 with value is 31 , I want to assign value of this textbox to Report of Crystal Report. Pls help me
November 29th, 2007 at 3:25 am
I need your help ? Urgent ..
I’ve got 1 textbox in Visual Baasic 6 with value is 31 , I want to assign value of this textbox to Header of Report (Crystal Report). Pls help me
November 29th, 2007 at 3:28 pm
have report showing in vb6 (designed in crystal tied to database fields) but only the numeric fields show - no alpha text fields show as well as a blob field from the database. any suggestions?
also, one of my numeric fields needs to be converted to alpha (ie 1 = mr, 2 = mr & mrs, etc.) anyone tell me how to do that? is it done in crystal design or on the vb side?
thanks!
December 7th, 2007 at 4:46 pm
Hi all. I need to change the SQL statement of a .DSR (CR9) thru VB6. Basically, I need the same form, but I want the data to be retrieved from other tables.
However, when I right-click on Database fields, I can only see Show SQL Query, and the SQL itself within is greyed out. I can change the SQL in no way.
Please help.
Thanks & regards,
Mark
December 9th, 2007 at 4:35 pm
How to make connection string in VB6 to change connection in ODBC DSN with multiple ms ACCESS database Coz my Chrystal report only connect with one ms ACCESS database if i use ODBC Connection. I’m so confuse. Please give me an answer. I really need this source code.
January 18th, 2008 at 1:32 pm
hi
i want to display start date and end date in report header ; directly to report(not through any database field) how can i do?
I am woking on crystal report 9.0
My Mail Id is…..sonagupta04@gmail.com
pls help me
January 25th, 2008 at 5:02 pm
Hi everyone!
I am a new programmer and I have this fondness for vb6. I will eventually transfer to JAVA. I ws just wondering if nyone can send me a simple vb6 program with full mySQL database usage? Please??? Here’s my e-mail, blood_left_the_ruins@yahoo.com. Thanks!!
February 3rd, 2008 at 7:09 am
upto how many groups does crystal report support
February 19th, 2008 at 10:11 am
hi! can someone tell me how to connect the database to crystal report? i’ having problem on it!
(vb 6.0-sp6, Crystal report 9, mySql)
February 26th, 2008 at 2:38 pm
can some one tell me,how to increase length of datafield in crystal report.i had a field in database which consist 1000 characters but in crystal report it shows only 100 characters……..
arsal_2009@yahoo.com
February 27th, 2008 at 10:42 am
Format the field and select Paragraph Formatting tab in the Text Interpretation tab select RTF format or HTML format to display the entire content.
March 14th, 2008 at 8:58 am
can somebody help me with the codes linking vb to mysql
March 22nd, 2008 at 6:53 pm
hi this is very sufull me..
March 22nd, 2008 at 6:55 pm
this is a beautyfull programming my best help me….
April 10th, 2008 at 8:15 am
hie pliz help!im trying to print crystal reports in visual basic 6.
April 24th, 2008 at 9:09 am
Dear Experts,
I am using visual Basic 6.0 as front end and MySQL 4.0.16 as a database. I want to create report with Crystal Report 8.5. How can I connect this and print this report.Please sent me the step by step. Please reply me.
Thanking you,
Sudip
May 9th, 2008 at 9:39 am
Dear Experts,
My trouble like Sudip, when i connect to mysql i’ve got error
run-time error ‘394′ :
Property is write only
please help me…
Thank & Regrad’s
Fitri