Bruce, Techy...its that darn crystal ? again...

Finally got around to writing some code to view my crystal reports..

All is working well EXCEPT when I specify criteria on my form. Meaning I have a form with 4 combo boxes at the top. Users can select a customer followed by a site, followed by a commission, etc. But whenever I specify any criteria I get the following error when I try to open the report:

Login Failed For User SA
blah blah blah....
....


My code is as follows:

VB Code:

Private Sub Form_Load()
On Error GoTo Err_Handler
 
Dim strReportPath As String
 
Select Case (frmReports.SSTab1.Tab)
    'project
    Case 0:
        If frmReports.optRptEnc3.Value = True Then
            SetRptName ("rptEnclosure3.rpt")
        End If
        
    'mgmt
    Case 1:
    
    'misc
    Case 2:
    
    'admin
    Case 3:
    
End Select
    
strReportPath = App.Path & "\Reports\"
Set objCrystalApp = New CRAXDRT.Application
Set objReport = objCrystalApp.OpenReport(strReportPath & GetRptName, 0)
 
'now check for criteria
    If frmReports.cboRptQuoteNumbers.Text = "" Then
        'no quote
    Else
        objReport.RecordSelectionFormula = "{Proposals.ProposalID} = " & frmReports.cboRptQuoteNumbers.ItemData(frmReports.cboRptQuoteNumbers.ListIndex)
        GoTo LabelViewReport
    End If
    
    If frmReports.cboRptCustomers.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = "{Proposals.CustomerID} = " & frmReports.cboRptCustomers.ItemData(frmReports.cboRptCustomers.ListIndex)
    End If
    
    If frmReports.cboRptSites.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = "And {Proposals.SiteID} = " & frmReports.cboRptSites.ItemData(frmReports.cboRptSites.ListIndex)
    End If
    
    If frmReports.cboRptComms.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = "And {Proposals.CommissionID} = " & frmReports.cboRptComms.ItemData(frmReports.cboRptComms.ListIndex)
    End If
    
LabelViewReport:
frmCRViewer.CRViewer91.ReportSource = objReport
frmCRViewer.CRViewer91.ViewReport
 
Done:
Set objReport = Nothing
Set objCrystalApp = Nothing
Exit Sub
 
Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done
 
End Sub

First I check which tab the user is on to determin the report. For now I only have 1 report. Then I set the report name. Then I finally check for criteria and then open the report. It always works with no criteria but right when I have criteria it fails.

Jon

I had found I was using some wrong fields...

But now Im getting an error when a second piece of criteria is selected it says:

The remaining text does not appear to be part of the formula.


It happens when again I select another criteria and it hits the And portion of the code:

VB Code:

On Error GoTo Err_Handler
 
Dim strReportPath As String
 
Select Case (frmReports.SSTab1.Tab)
    'project
    Case 0:
        If frmReports.optRptEnc3.Value = True Then
            SetRptName ("rptEnclosure3.rpt")
        End If
        
    'mgmt
    Case 1:
    
    'misc
    Case 2:
    
    'admin
    Case 3:
    
End Select
    
strReportPath = App.Path & "\Reports\"
Set objCrystalApp = New CRAXDRT.Application
Set objReport = objCrystalApp.OpenReport(strReportPath & GetRptName, 0)
 
'now check for criteria
    If frmReports.cboRptQuoteNumbers.Text = "" Then
        'no quote
    Else
        objReport.RecordSelectionFormula = "{Proposals.ProposalID} = " & frmReports.cboRptQuoteNumbers.ItemData(frmReports.cboRptQuoteNumbers.ListIndex)
        GoTo LabelViewReport
    End If
    
    If frmReports.cboRptCustomers.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = "{Customers.Name} = '" & frmReports.cboRptCustomers.Text & "'"
    End If
    
    If frmReports.cboRptSites.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = " And {Sites.Name} = '" & frmReports.cboRptSites.Text & "'"
    End If
    
    If frmReports.cboRptComms.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        objReport.RecordSelectionFormula = " And {Commissions.Commission} = '" & frmReports.cboRptComms.Text & "'"
    End If
    
LabelViewReport:
frmCRViewer.CRViewer91.ReportSource = objReport
frmCRViewer.CRViewer91.ViewReport
 
Done:
Set objReport = Nothing
Set objCrystalApp = Nothing
Exit Sub
 
Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done
 
End Sub

Jon

Buddy ... just do me a little favour. I just sent you the APP i was talking about. Take it and run it and you will realize its not a good idea to embed your reports in the VB app. In the meantime i will look into this code of your.

Quote:

---

Originally posted by techyspecy
Buddy ... just do me a little favour. I just sent you the APP i was talking about. Take it and run it and you will realize its not a good idea to embed your reports in the VB app. In the meantime i will look into this code of your.

---

Who said my reports were embedded?

Jon

My reports are in there own directory totally seperate from the application itself. I have not imported or brought in any reports. I use the path to open them up.

I dont think what you sent me is really what Im looking for..I would really just like to fix the code that keeps throwing up an error saying the string is not part of the formula???

Thanks.. Jon

O think I know why now...duhh..I keep resetting the formula..and the AND kills it...

Ill come back with a response in a bit...

Jon

Ok I got it working with this...

VB Code:

Private Sub Form_Load()
On Error GoTo Err_Handler
 
Dim strReportPath As String
Dim strFormula As String
 
Select Case (frmReports.SSTab1.Tab)
    'project
    Case 0:
        If frmReports.optRptEnc3.Value = True Then
            SetRptName ("rptEnclosure3.rpt")
        End If
        
    'mgmt
    Case 1:
    
    'misc
    Case 2:
    
    'admin
    Case 3:
    
End Select
    
strReportPath = App.Path & "\Reports\"
Set objCrystalApp = New CRAXDRT.Application
Set objReport = objCrystalApp.OpenReport(strReportPath & GetRptName, 0)
 
'now check for criteria
    If frmReports.cboRptQuoteNumbers.Text = "" Then
        'no quote
    Else
        objReport.RecordSelectionFormula = "{Proposals.ProposalID} = " & frmReports.cboRptQuoteNumbers.ItemData(frmReports.cboRptQuoteNumbers.ListIndex)
        GoTo LabelViewReport
    End If
    
    If frmReports.cboRptCustomers.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        GoTo LabelViewReport
    Else
        strFormula = "{Customers.Name} = '" & frmReports.cboRptCustomers.Text & "'"
    End If
    
    If frmReports.cboRptSites.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        objReport.RecordSelectionFormula = strFormula
        GoTo LabelViewReport
    Else
        strFormula = strFormula & " And {Sites.Name} = '" & frmReports.cboRptSites.Text & "'"
    End If
    
    If frmReports.cboRptComms.Text = "" Then
        'no customer AND no quote...that means
        'NO criteria has been selected..so we simply view
        'the report
        objReport.RecordSelectionFormula = strFormula
        GoTo LabelViewReport
    Else
        strFormula = strFormula & " And {Commissions.Commission} = '" & frmReports.cboRptComms.Text & "'"
        objReport.RecordSelectionFormula = strFormula
    End If
    
LabelViewReport:
frmCRViewer.CRViewer91.ReportSource = objReport
frmCRViewer.CRViewer91.ViewReport
 
Done:
Set objReport = Nothing
Set objCrystalApp = Nothing
Exit Sub
 
Err_Handler:
MsgBox Err.Description, vbCritical, "Error #: " & Err.Number
Resume Done
 
End Sub

BUT there still is a problem for a record that does not exist in the database...The report still opens up with no header information but it still shows the subreports information...even though its child records for a totally different parent. Meaning the criteria I selected on my form is not even a record in the db. Then I click ok to open the report and it opens it...at the top of the report is NO info (which is right)...but it shows all the subreports with DATA...from another record. Is there a way to pop up a message saying the report doesnt exist for your data and then the app wont open the report?

Thanks,
Jon

Anyone with ideas :-(

Jon