CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1

    [RESOLVED] CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    I keep getting an 'Error in Formula' message that states that the 'field name is not known' in the record selection when I open my report a second time from my VB6 application. It works perfectly the first time, but fails every time after that until I close and re-open my application.

    Anyone seen anything like this?

  2. #2
    Join Date
    Jul 2005
    Posts
    1,083

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    1.- Check if there's a blank or space or CR or NL at the end of the RecordSelectionFormula
    Obviously you are not going to 'see' it, but if there's one, then the cursor is going to be in the next line of the code
    Delete it and test

    2.- If point 1 is not the solution then export the rpt to Report Definition format, Destination DiskFile
    Please, post attach the created txt file
    JG


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

  3. #3

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Here you go...





    Crystal Report Professional v8.5 (32-bit) - Report Definition

    1.0 File Information

    Report File:
    Version: 8.0

    2.0 Record Sort Fields
    A - {ANALYTE_LOOKUP.PRINT_ORDER}

    3.0 Group Sort Fields

    4.0 Formulas

    4.1 Record Selection Formula
    {ANALYTE_MANIFEST_JOIN.RELEASE_RESULT} and
    {CHAIN_OF_CUSTODY.SAMPLE_REF_NO} = 32467.00

    4.2 Group Selection Formula


    4.3 Other Formulas

    Name: {@analysis_side}
    Formula: if {ANALYTE_LOOKUP.ANALYSIS_SIDE_ID} = 1 then
    "Fingerprint"
    else
    "Metals"


    Name: {@limit}
    Formula: if {ANALYTE_MANIFEST_JOIN.GREATER_LESS} = 2 then
    if {ANALYTE_MANIFEST_JOIN.RECD_LIMIT} = -999 then
    "NA"
    ELSE
    "> " + TOTEXT({ANALYTE_MANIFEST_JOIN.RECD_LIMIT})
    ELSE IF {ANALYTE_MANIFEST_JOIN.GREATER_LESS} = 1 THEN
    IF {ANALYTE_MANIFEST_JOIN.UPPER_RECD_LIMIT} = -999 THEN
    "NA"
    ELSE
    "< " + TOTEXT({ANALYTE_MANIFEST_JOIN.UPPER_RECD_LIMIT})
    ELSE IF {ANALYTE_MANIFEST_JOIN.GREATER_LESS} = 3 then
    if {ANALYTE_MANIFEST_JOIN.RECD_LIMIT} = -999 or {ANALYTE_MANIFEST_JOIN.UPPER_RECD_LIMIT} = -999 then
    "NA"
    ELSE
    TOTEXT({ANALYTE_MANIFEST_JOIN.RECD_LIMIT}) + "-" + TOTEXT({ANALYTE_MANIFEST_JOIN.UPPER_RECD_LIMIT})
    ELSE
    "NA";

    5.0 Sectional Information

    5.1 Page Header Section
    Visible, Keep Together

    Subsection.1
    Visible, Keep Together

    Subsection.2
    Visible, Keep Together

    Sample Description/Matrix:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {GENERATOR_PROFILE_FUEL_TYPE.FUEL_TYPE_DESCRIP}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Generator Name:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {GENERATOR.GEN_NAME}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Lab Sample ID:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {CHAIN_OF_CUSTODY.LAB_SAMPLE_ID}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Company_Info.rpt
    Subreport, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    Subreport begins

    1.0 File Information

    Report File: Company_Info.rpt
    Version: 8.0

    2.0 Record Sort Fields

    3.0 Group Sort Fields

    4.0 Formulas

    4.1 Record Selection Formula


    4.2 Group Selection Formula


    4.3 Other Formulas

    Name: {@Co_Address}
    Formula: StringVar strTemp;

    strTemp:= "";

    IF IsNull ({@Co_Add1}) = TRUE or trim({@Co_Add1}) = "" THEN
    strTemp:= strTemp
    ELSE
    strTemp:= strTemp + trim({@Co_Add1}) + Chr (10)
    ;

    IF IsNull ({@Co_Add2}) = TRUE or trim({@Co_Add2}) = "" THEN
    strTemp:= strTemp
    ELSE
    strTemp:= strTemp + trim({@Co_Add2}) + Chr (10)
    ;

    IF IsNull ({@Co_CtStZip}) = TRUE or trim({@Co_CtStZip}) = "" THEN
    strTemp:= strTemp
    ELSE
    strTemp:= strTemp + trim({@Co_CtStZip})
    ;


    strTemp

    Name: {@Co_CtStZip}
    Formula: if not isnull({COMPANY_INFO.City}) and not isnull({COMPANY_INFO.State}) and not isnull({COMPANY_INFO.Zip}) then
    if {COMPANY_INFO.City} <> "" and {COMPANY_INFO.State} <> "" and {COMPANY_INFO.Zip} <> "" then
    {COMPANY_INFO.City} + ", " + {COMPANY_INFO.State} + " " + {COMPANY_INFO.Zip};


    Name: {@Co_Add2}
    Formula: {COMPANY_INFO.Address_2}


    Name: {@Co_Add1}
    Formula: {COMPANY_INFO.Address_1}


    5.0 Sectional Information

    5.1 Report Header Section
    Visible, New Page Before

    Subsection.1
    Hidden, Keep Together

    Subsection.2
    Visible, Keep Together

    {@Co_Address}
    String, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {@Co_CtStZip}
    String, Hidden, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {@Co_Add2}
    String, Hidden, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {@Co_Add1}
    String, Hidden, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    Grr!
    String, Visible, Left Alignment, Top Alignment,
    Keep Together



    {COMPANY_INFO.Co_Name}
    String, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    5.2 Report Footer Section
    Visible, New Page After

    Subsection.1
    Hidden, Keep Together

    Subsection.2
    Hidden, Keep Together

    5.3 Details Section
    Hidden

    Subsection.1
    Hidden, Keep Together

    Subreport ends

    Chain of Custody

    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Expand


    Final Analytical Results

    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Expand


    {@RptReportValue}
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Sample Reference Number:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {CHAIN_OF_CUSTODY.SAMPLE_REF_NO}
    Numeric, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand,
    Leading Minus, No Thousands Separators, No Leading Zero, 0 Decimal Places, Rounding: 1,
    Thousands Symbol: ',', Decimal Symbol: '.'


    Sampled by:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {PERSONNEL.PERSONNEL_NAME}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Load Number:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {CHAIN_OF_CUSTODY.LOAD_NO}
    Numeric, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand,
    Leading Minus, 0 Decimal Places, Rounding: 1,
    Thousands Symbol: ',', Decimal Symbol: '.'


    Profile Approval Number:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {GENERATOR_WASTE_PROFILE.PROFILE_NUMBER}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Manifest Number:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {CHAIN_OF_CUSTODY.MAN_NO}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Lab Technician:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {PERSONNEL_1.PERSONNEL_NAME}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    Trailer Number:
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand


    {MANIFEST.TRAILER_NUMBER}
    String, Visible, Left Alignment, Top Alignment,
    Keep Together, Expand, Using System Default Formatting, Word Wrap


    5.2 Page Footer Section
    Visible, Keep Together

    PrintDate
    Date, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Windows Default Type: Use Windows Short Date, Date Order: Month Day Year, Year Type: Long, Month Type: Numeric Month, Day Type: Numeric Day, Leading Day Type: None, First Separator: '/', Second Separator: '/', Leading Day Separator: ''


    PrintTime
    Time, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Time Base: 12 Hour, AM/PM Type: AM/PM After, Hour Type: Numeric No Leading Zero, Minute Type: Numeric, Second Type: Numeric, AM String: 'AM', PM String: 'PM', Hour Minute Separator: ':', Minute Second Separator: ':'


    PageNofM
    String, Visible, Right Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    Fuels Management System
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together



    5.3 Report Header Section
    Hidden, New Page Before

    5.4 Report Footer Section
    Hidden, New Page Before, New Page After

    5.5 Group Header Section #1
    Visible

    Analytical
    String, Visible, Left Alignment, Top Alignment,
    Keep Together



    Result
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together



    Units
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together



    Limit
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together



    Pass/Fail
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together



    5.6 Group Footer Section #1
    Hidden

    5.7 Group Header Section #2
    Visible

    {@analysis_side}
    String, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    5.8 Group Footer Section #2
    Hidden

    5.9 Details Section
    Visible

    Subsection.1
    Visible, Keep Together

    Format Formulas

    Background Color: //if RecordNumber mod 2 = 0 then silver else white

    {ANALYTE_MANIFEST_JOIN.PASS_FAIL}
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {@limit}
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {ANALYTE_MANIFEST_JOIN.UNITS}
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    {ANALYTE_MANIFEST_JOIN.RESULT}
    String, Visible, Horizontal Centre Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    Font Color Formulas -
    if {ANALYTE_MANIFEST_JOIN.ANALYTE_ID} = "% Water" and isnumeric({ANALYTE_MANIFEST_JOIN.RESULT}) and tonumber({ANALYTE_MANIFEST_JOIN.RESULT}) > 25 then
    red



    {ANALYTE_MANIFEST_JOIN.ANALYTE_ID}
    String, Visible, Default Alignment, Top Alignment,
    Keep Together, Using System Default Formatting, Word Wrap


    Font Color Formulas -
    if {ANALYTE_MANIFEST_JOIN.ANALYTE_ID} = "% Water" and isnumeric({ANALYTE_MANIFEST_JOIN.RESULT}) and tonumber({ANALYTE_MANIFEST_JOIN.RESULT}) > 25 then
    red

  4. #4
    Join Date
    Jul 2005
    Posts
    1,083

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Have You verified the database?
    JG


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

  5. #5

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Several times

  6. #6
    Join Date
    Jul 2005
    Posts
    1,083

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    And what happens when you are in Crystal Reports ide? It gives you the same error when preview?
    JG


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

  7. #7

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    No, it works every time without issue when I run it from the IDE. This is only when my VB6 application calls it. It works the first time, but fails every time after that. I looked at every variable I could find to see if there was a difference between the 1st and 2nd calls and couldn't find one.

  8. #8
    Join Date
    Jul 2005
    Posts
    1,083

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    May you post the code where you call the rpt & subrpt also if you send some values & recordselection folrmula
    JG


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

  9. #9

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Sure, I will get that together and post it. It may be tomorrow though.

  10. #10

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Alright, here's the 'meat' of the code and the values being passed...

    strConnect = "DSN=server;UID=username;PWD=password;DSQ=DBName"
    strSelect = "{ANALYTE_MANIFEST_JOIN.RELEASE_RESULT} = true and {CHAIN_OF_CUSTODY.SAMPLE_REF_NO} = 32624"
    strReportFile = "C:\program\reports\coc_final_results.rpt"

    crwManReport.ReportFileName = strReportFile
    If Len(strSelect) > 0 Then
    crwManReport.ReplaceSelectionFormula strSelect
    End If
    With crwManReport
    .Connect = strConnect
    intSubCount = .GetNSubreports
    For iCurRpt = 0 To intSubCount - 1
    .SubreportToChange = .GetNthSubreportName(iCurRpt)
    .Connect = strConnect
    Next
    If Not modBIF.blnRunBatch Then
    .WindowState = crptMaximized
    .WindowShowCloseBtn = True
    .WindowShowPrintSetupBtn = True
    .Action = 1
    End If
    End With

  11. #11
    Join Date
    Jul 2005
    Posts
    1,083

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Some things to do (almost sure that you already did) :
    1 If the design time database is different from run time database then change database location and execute 'verify database'
    2 Remove the subreport and test
    JG


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

  12. #12

    Re: CR8.5 - Error in Formula on 2nd time opening report from VB6 application

    Looks like it was the sub-report! I removed it since it doesn't dynamic content and it worked. Thanks!

  13. #13

    Re: [RESOLVED] CR8.5 - Error in Formula on 2nd time opening report from VB6 applicati

    I hate to resurrect this thread, but I'm still struggling with this issue. It appears that ANY report which has a sub-report is throwing this error. If I delete the sub-report then it works, but I need some of these sub-reports so I really need to make it work. I have saved off and re-imported the subreports and converted the DB drivers and everything and still no luck. The reports work without issue from the IDE but my application throws the error above after more than 1 open of the report.

  14. #14

    Re: [RESOLVED] CR8.5 - Error in Formula on 2nd time opening report from VB6 applicati

    Here's some more info on the error...

    Error # - 20515
    Error in File C:\report.rpt:
    Error in formula <Record_Selection>.
    '{report.sample_ref_no} = 32623'
    This field name is not known.

  15. #15
    Join Date
    Jul 2017
    Posts
    2

    Re: [RESOLVED] CR8.5 - Error in Formula on 2nd time opening report from VB6 applicati

    Hello fubak !!!

    type the following at the top of the code in the print button click event:

    crystalreport1.reset

    I am sorry if i am typing it in 2017. But i am happy coz someone may get benefit from this.
    Using CrystalReport 8.5 CrystalReport.OCX on vb6 form.
    Imaging that the main report also posses one or more sub reports.

    Cr.Reset
    Cr.Connect = "DSN=MyDataSource;UID=sa;PWD=MyPassword;DSQ=MyDatabaseName"
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Cr.ReportFileName = App.Path & "\Reports\crv.rpt"
    Cr.Destination = crptToWindow
    Cr.WindowState = crptMaximized
    Cr.WindowTitle = "Bank Payment Voucher"
    Cr.DiscardSavedData = True
    Cr.SelectionFormula = "{crv_main.sno}=" & txtID.Text

    ''''''''Also connect any subreports to the proper database'''''''''''''''''''''''''''''''''
    nRpt = Cr.GetNSubreports
    nCurr = 0
    While nCurr < nRpt
    szName = Cr.GetNthSubreportName(nCurr)
    Cr.SubreportToChange = szName
    Cr.Connect = "DSN=MyDataSource;UID=sa;PWD=MyPassword;DSQ=MyDatabaseName"
    nCurr = nCurr + 1
    Wend

    '''''''''End of Connect''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Cr.Action = 1

Page 1 of 2 12 LastLast

Tags for this Thread

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

Featured