Results 1 to 7 of 7

Thread: [RESOLVED] Pass Date Parameters from VB6 to CR XI

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Resolved [RESOLVED] Pass Date Parameters from VB6 to CR XI

    I've spent the last couple hours searching for information on how to do this but haven't found an answer I can understand. Some of the better replies have been "look in the help file" - there is nothing about this in the help file ... or "Don't use parameters". Sheesh. Or links to the old Business Objects website that now go to a generic SAP page. I'm frustrated with that approach so I'm going to ask here.

    Anyway, I designed the report in CRXI's main report designer program and then added it to my VB6 project as a dsr and form - it's the developer edition of CRXI. On the report I created two parameter fields, "Start Date" and "End Date". They are the dates between which I want the records to be shown. This works just fine (the parameter input screen is shown when starting the report) except I do not want to have the user enter the dates because a form already asks for that information. Instead I just want to pass the dates selected from dtPickers to the parameters in the report. There has to be a pretty simple way to do this. Maybe I'm just dense, but I can't understand half of the replies I've read talking about dlls, and so many of them just stop with no resolution.

    The code I have isn't going to be of much use ..
    Code:
    CRViewer1.ReportSource = Report
    
    Report.DiscardSavedData
    
    CRViewer1.ViewReport
    Is there someone out there who can tell me the command and syntax to use while I still have at least a little hair left?
    TIA
    Ken

  2. #2
    I'm about to be a PowerPoster! Joacim Andersson's Avatar
    Join Date
    Jan 1999
    Location
    Sweden
    Posts
    14,649

    Re: Pass Date Parameters from VB6 to CR XI

    Moved to the Reporting forum.

    It's been ages since I worked with Crystal Reports and VB6 but I think you do something like this:
    Code:
    CRViewer1.ParameterFields(0) = "@ParamName;Value"

  3. #3
    Addicted Member Cristian's Avatar
    Join Date
    Jun 2006
    Posts
    228

    Re: Pass Date Parameters from VB6 to CR XI

    Maybe you don't need to pass parameters to the report. The report has an property called Database that has a table collection. To each table you can to assign a recordset. If the recordset is open with parameters, the data in the recordset is already filtered.
    This must be do like this:
    Dim RsFiltered as New ADODB.Recordset
    RsFiltered.Open "SELECT * FROM MyTable WHERE Codigo=" & Cstr(xValue), Conexion, adOpenStatic, adLockReadOnly
    Report.Database.Tables(1).SetDataSource RsFiltered

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Pass Date Parameters from VB6 to CR XI

    Thanks Joacim. Trying all possible syntaxes for that consistently gives me "Object doesn't support this property or method."

    Cristian. I may have to just pass my sql statement to the report as it seems passing parameters is just too difficult. My sql statement is somewhat more involved that your example. Unfortunately when I try it with your suggestion I also get the "Object doesn't support this property or method" complaint or I get "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another".

    Code:
    SQL=SELECT Transactions.DateOfPayment, Transactions.AmountOfPayment, Reservations.Charges, Reservations.MaidRateR, Reservations.Tax FROM Reservations INNER JOIN Transactions ON Reservations.ReservationID = Transactions.ReservationID WHERE DATEOFPAYMENT >=#1/1/1980# AND DATEOFPAYMENT <=#4/11/2013#
    Perhaps it simply isn't possible and I'll have to make the user put in the dates a second time.

  5. #5
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: Pass Date Parameters from VB6 to CR XI

    Perhaps it simply isn't possible and I'll have to make the user put in the dates a second time.
    That's why it's better not to use parameters in rpts that are called by an application
    We ask for the dates in our own vb6 forms and just construct the RecordSelectionFormula and pass to the rpt before we call it
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  6. #6
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: Pass Date Parameters from VB6 to CR XI

    Anyway, I forgot to show how to pass parameters to rpt from vb6 app
    The rpt read data from a sql server (it doesn't matter) but could be from any db

    References:
    Crystal Reports Viewer Control
    Crystal Reports ActiveX Designer Run Time Library
    Code:
    ' Typical Variable Declarations
    dim crApp as New CRAXDRT.Application
    dim crRept as New CRAXDRT.Report
    dim crParamDefs as CRAXDRT.ParameterFieldDefinitions
    dim crParamDef as CRAXDRT.ParameterFieldDefiniton
    dim crDBTab as CRAXDRT.DatabaseTable
    
    ' Open Report File
    set crRept = crApp.OpenReport("WHATEVER.RPT")
    
    ' Logon to SQL server
    crRept.Database.LogonServer "p2ssql.dll", "server name", "database name", "userid", "userpassword"
    
    ' Set table locations (because my reports run against multiple servers)
    foreach crDBTab in crRep.Database.Tables
      crDBTab.SetLogonInfo "server name", "database name", "userid", "userpassword"
    next
    
    ' Disable Parameter Prompting for the end user
    crRep.EnableParameterPrompting = FALSE
    
    ' Gather the list of available parameters from the report
    set crParamDefs = crRep.ParameterFields
    
    ' Loop through all parameters in the report by name, filling in the appropriate parameter with the right value
    foreach crParamDef in crParamDefs
      select case crParamDef.ParameterFieldName
        case "SubTitle"
          crParamDef.SetCurrentValue "My Report Subtitle Goes Here"
        case "@BeginDate"
          crParamDef.SetCurrentValue datevalue(txtBeginDate)
        case "@EndDate"
          crParamDef.SetCurrentValue datevalue(txtEndDate)
        case "@IntegerParam"
          crParamDef.SetCurrentValue val(int(txtIntegerParam))
      end select
    next
    crViewer1.ReportSource = crRept
    crViewer1.viewReport
    You can pass any value you want to CR, whether the user typed it or your program generated it in this way.
    (Parameters that begin with @ are for use with stored procedures)
    Last edited by jggtz; Apr 10th, 2013 at 05:25 PM.
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Sep 2006
    Posts
    236

    Re: Pass Date Parameters from VB6 to CR XI

    Thanks for the reply JG. I found something of an easier workaround though. I removed the two parameters and added this:

    Code:
    Report.RecordSelectionFormula = "{Transactions.DateOfPayment} >= " & "Date (" & Format(frmRevenue.dtStart, "YYYY,mm,dd") & ")" & " AND {Transactions.DateOfPayment} <= " & "Date (" & Format(frmRevenue.dtEnd, "YYYY,mm,dd") & ")"
    It took a few times through to get the syntax right, but this works as I intended.

    Outside of some bug testing, this completes the project .. although I have three more in the wings.

    Thanks again. I will keep your code on hand for the next time I might need it. I don't find CR very friendly as far as the VB interface is concerned ... it would be nice if there were a help file specifically for this.

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width