1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Crystal Report difficulties in VB6

Discussion in 'Software Development' started by SteveJT, Oct 13, 2008.

Thread Status:
Not open for further replies.
  1. SteveJT

    SteveJT Thread Starter

    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. ;)

  2. Aftab


    Oct 15, 2003
    Could you paste the rest of the relevant code.
  3. SteveJT

    SteveJT Thread Starter

    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
    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
    'Finish with the recordset.
    Set rss = Nothing
    sql = ""
    Select Case printOK
    Case True
    'Substantiate new POPicklist selective recordset
    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.
    'In case we need to, start at beginning of recordset.
    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.

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

    Exit Sub
  4. Aftab


    Oct 15, 2003

    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.
  5. SteveJT

    SteveJT Thread Starter

    Oct 10, 2008
    The noprinterr error handler is as follows:

    Select Case Err
    Case 3021
    MsgBox ("No records found to print")
    Resume cmbWOClickExit
    Case Else
    MsgBox Err.Number & ":" & Err.Description, vbCritical
    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.


  6. 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
    m_Report.Database.AddADOCommand m_connection, m_cmd
    3. Where you have:
    frmEcomedPOViewer.Visible = True
    change it to
    with frmEcomedPOViewer
    .reportsource = m_report
    .Visible = True
    end with

    Hope this helps
  7. SteveJT

    SteveJT Thread Starter

    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,

  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/758691

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice