# Crystal Report difficulties in VB6



## SteveJT (Oct 10, 2008)

Hi all,
I have been trying to get a report working under VB6 which uses Crystal Reporting version 10.
I created an SQL command in VB6 to pick up data from a required table matched on a work order number. On displaying the records in the resulting recordset (m_RS) I could see they were for the required work order.

I tried to link the Crystal report to this recordset via:

m_Report.Database.AddADOCommand m_cmd.ActiveConnection, m_RS.ActiveCommand

However when the Crystal Viewer displays the report it shows a list of all available records, not just the one for the work order selected.

But this is not what we want.

Any idea why this is happening?
Any help is appreciated. 

Regards,
SteveJT


----------



## Aftab (Oct 15, 2003)

Could you paste the rest of the relevant code.


----------



## SteveJT (Oct 10, 2008)

The following is the code on clicking the Work Order entry button. I know its a bit of a mess but I had to experiment:

Dim RS As New ADODB.Recordset
Dim m_RS As New ADODB.Recordset

Private Sub cmbWO_Click()
Dim rss As ADODB.Recordset
Dim vbmess As VbMsgBoxResult
Set rss = New ADODB.Recordset
'Default is no printout
printOK = False
'Store the selected Work order to use with Crystal Report
SelectedWO = Trim(cmbWO.Text)
'If no Work Order chosen then exit
If SelectedWO = "" Then GoTo cmbWOex
'Set up SQL search string.
sql = "Select * From scheme.bmwohm where works_order = '" & SelectedWO & "'"

'Recordset of work orders.
Set rss = CsN.Execute(sql)
'Substantiate new report.
Set m_Report = New CrystalReportEcomedPOPick
'Substantiate a new connection
Set m_connection = New ADODB.Connection
'Open the connection
strConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=demo;Data Source=LAB1"
m_connection.Open strConnect

'Open full datasource recordset from POPickList view
Set RS = New ADODB.Recordset
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS.Open "POPickList", m_connection, , , adCmdTable
Dim s As String

'Check if if there is any report data to report on
If (RS.BOF And RS.EOF) Then
MsgBox ("Pick report is empty")
GoTo cmbWOex 'Nothing to print
Else
RS.MoveFirst
End If

'Add the datasource to the report - view POPicklist.
m_Report.Database.AddOLEDBSource m_connection, "POPickList"

'Make the display fields visible..
lblWarehouse.Visible = True
lblProductCode.Visible = True
txtWarehouse.Visible = True
txtProductCode.Visible = True
'..and move the bmwohm record data to the display.
txtWarehouse = rss!warehouse
txtProductCode = rss!product_code
'Confirm the selection
vbmess = MsgBox("Confirm Selection", vbYesNo, "Confirm Print")
Select Case vbmess
Case 6
'Confirmed selection
printOK = True

Case 7
'Selection NOK, reprompt
printOK = False
End Select
cmbWOex:
'Finish with the recordset.
Set rss = Nothing
sql = ""
Select Case printOK
Case True
'Substantiate new POPicklist selective recordset
'm_RS.Close
Set m_RS = New ADODB.Recordset
'Get record set data according to the workorder selection..
sql = "Select * From dbo.POPickList where works_order = '" & SelectedWO & "'"
m_RS.Open sql, m_connection, adOpenDynamic, adLockOptimistic, adCmdText

'See if report is empty for selected record..
If (m_RS.BOF And m_RS.EOF) Then
MsgBox ("Report on " & SelectedWO & " is empty")
Exit Sub 'Nothing to print so exit.
Else
'In case we need to, start at beginning of recordset.
RS.MoveFirst
End If

On Error GoTo noprinterr

Set m_connection = New ADODB.Connection
strCnn = strConnect
m_connection.Open strCnn

'Create a new instance of an ADO command object.
Set m_cmd = New ADODB.Command
Set m_cmd.ActiveConnection = m_connection
m_cmd.CommandText = sql
m_cmd.CommandType = adCmdText

'Add record set to report
m_Report.Database.AddADOCommand m_cmd.ActiveConnection, m_RS.ActiveCommand
'm_Report.Database.AddADOCommand m_RS.ActiveConnection, sql
'Sub AddADOCommand(m_connection, m_cmd)
While Not m_RS.EOF And Not m_RS.BOF
MsgBox ("Work order " & m_RS!works_order)
'In case we need to, start at beginning of recordset.
m_RS.MoveNext
Wend

frmEcomedPOViewer.Visible = True
Screen.MousePointer = vbDefault
lblWarehouse.Visible = False
lblProductCode.Visible = False
txtWarehouse.Visible = False
txtProductCode.Visible = False

Exit Sub


----------



## Aftab (Oct 15, 2003)

Hello,

I've always used stored procedures to get the data for crystal reports, but I'll have a go at trying to resolve your problem.

comment out the error handling statement (On Error GoTo noprinterr), just incase an error is occurring. How are you handling the errors within this handler, it's not just a "resume next" statement is it? If its is you could be losing an error message.

Also, before running the report call the .discardsaveddata method.


----------



## SteveJT (Oct 10, 2008)

The noprinterr error handler is as follows:

noprinterr:
Select Case Err
Case 3021
MsgBox ("No records found to print")
Resume cmbWOClickExit
Case Else
MsgBox Err.Number & ":" & Err.Description, vbCritical
Stop
Resume
End Select

The above errors are not being printed during the run so I assume the program has not gone into this error trap.

I will try your suggestion with the .discardsaveddata method.

Thanks,

SteveJT


----------



## Aftab (Oct 15, 2003)

One thing I've noticed is that you're selecting a datasourcw twice. Firstly with .AddOLEDBSource and then with .AddADOCommand. Is the first one necessary? I think that as you're seeing all the rows from the table in your report, the AddADOCommand may not be working, the datasource is still "POPickList" from the first datasource. Also, can I ask why you're using these methods, these should only be used when creating reports dynamically in code. If your table structures change frequently, then fine use this method. Otherwise, you should select the tables in the report definition at design time. Anyway, if you need to do it this way, try the following:

1. Use only AddADOCommand 
2. Change the line 
m_Report.Database.AddADOCommand m_cmd.ActiveConnection, m_RS.ActiveCommand
to
m_Report.Database.AddADOCommand m_connection, m_cmd
3. Where you have:
frmEcomedPOViewer.Visible = True
change it to 
with frmEcomedPOViewer
.reportsource = m_report
.viewreport
.Visible = True
end with

Hope this helps


----------



## SteveJT (Oct 10, 2008)

Thank you - your comments were most helpful!

I'm sure that selecting the data source twice is a big part of my problem. I really don't know the VB6 too well. VB seems so very complicated - perhaps unnecessarily so. If there is one good way to do something, why confuse a developer with say 9 other ways that are less good. I have enough trouble just remembering _one_ 'method'!
There seems too much emphasis on a proliferation of "methods", "objects" and "properties" in Microsoft as a whole and VB design in particular.
A pity there aren't more efficient, goal-oriented solutions available for plodders like myself to follow using a "less flexible" command set that works 100% of the time. If only the makers of VB had stuck to one method to accomplish one thing, but anyway...wishing won't make it so.

I'm relatively new to this programming stuff in VB6 as you may have gathered, though I did quite a lot of programming in Quick Basic once. Without the required help being available in the company in which I work I've had to revert to a "trial and error" approach with VB6 which is not ideal.

I did actually manage to get the report selection to work a different way - by passing a parameter to the report identifying the records I wanted to print. I set the SQL search up within the report, and this seems to have worked.
I may cut out the adding of the recordset to the report.
Would you suggest that?

Best Regards,

SteveJT


----------

