VB6 module that hangs upon reentry

LCEE
LCEE used Ask the Experts™
on
Problem with a VB6 app using an ACCESS DB, which references a Foxpro table via a link.  

The app includes a Report module that uses a CR8.5 report.  On the first entry in the Report module it works as expected.  I can successfully leave and reenter the module with no problem.  The problem...  If I terminate the VB app, again invoke the VB app, then invoke the Report module, the VB app task hangs and has to be terminated.  The VB app always works until I get to the Report module.  If I restart the computer the cycle starts again -- the Report module works the first time only.  

This even occurs in debug mode.  However, if I set a breakpoint in debug mode and step through the Report module it works fine.  If I remove the breakpoints and invoke the Report module it hangs again.

I suspect that a resource is left open when the VB app terminates but I cannot identify the source.

Please, can someone shed light on this problem?

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I assume you're dim'ing a report like DIM MyReport as CRAXDRT.Report etc, etc?
After the report runs, set all those variables = NOTHING.

Author

Commented:
My code was missing:

    Dim crptApp As New CRAXDDRT.Application
    Dim crptReport As CRAXDDRT.Report

Following your advise, I attempted to set the variable "crptReport = Nothing" (see the code below).  When I enter this code I get the following error message:

Run-time error "91":
Object variable With block variable not set!

I included the entire Sub-routine, so if my attempt at following your suggestion was not correct, please let me know.
Though your answer was not exact it was sufficient to lead me to the solution.  You were the only one that responded.  Kudos to you!
*********************************
Private Sub buildReport(strReportName As String)
    Dim crptApp As New CRAXDDRT.Application
    Dim crptReport As CRAXDDRT.Report
    Dim strSONO As String
    Dim strSpace As Integer
    Dim crRptDate As String
    Dim strSelectionFormula  ' FORMULA TO selection criteria for REPORT
    Me.MousePointer = vbHourglass
    On Error GoTo Err_CoPrt
   
    Me.crptReport.ReportFileName = App.Path & "\" & strReportName
    crRptDate = Format$(DTReportDate, "yyyy-mm-dd")
    strSpace = 10 - Len(Trim(cboSONO))
    strSONO = Space(strSpace) + cboSONO

    strSelectionFormula = "{somast01.sono} = '" & strSONO & "' and {ictran01.tdate} = Date ('" & crRptDate & "')"

    Me.crptReport.ReplaceSelectionFormula (strSelectionFormula)
    Me.crptReport.Destination = crptToPrinter
   
    Me.crptReport.PrinterStartPage = 2
    Me.crptReport.PrinterStopPage = 999
     
    Me.crptReport.Action = 1
    ' crptReport = Nothing       ' If I leave this code in an error message is generated.
    '  If I test the value of crptReport here without the code, I l find that crptReport is already set to Nothing
    strSONO = ""
    crRptDate = ""
    Me.MousePointer = vbDefault
    Exit Sub
Err_CoPrt:
    Call Err_Handler
    Me.MousePointer = vbDefault
    Exit Sub
End Sub

Commented:
Replace "crptReport = Nothing"  with "Set crptReport = Nothing"

In addition, you should perhaps add "Set crptApp = Nothing" after the previous line.

However, your next comment, " ... crptReport is already set to Nothing" is puzzling.  Why should it already be set to nothing?  I don't know.  How do you determine it is Nothing? By setting a breakpoint and hovering the mouse over "crptReport" ?  If so, where is your breakpoint?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I set a breakpoint on  the line:
   Me.crptReport.ReportFileName = App.Path & "\" & strReportName
Then I set a Quick Watch via Debug for crptReport.
The I set another breakpoint on the line strSONO = "" .  

Of major interest, as I recreated this scenario I stepped through each line of code in the sub.  The "Value" for crptReport stayed constant -- "Nothing".  In my previous reference I was checking the Value at the second breakpoint.

As I'm writing this note I'm walking through the scenario and I'm adding a Quick Watch on "Me.crptReport" and can view a tree structure of its values.  However, if I use the code:
"Set crptReport = Nothing"  I do not see a change.  If I change the line to "Set Me.crptReport = Nothing" the code branches to the error routine, but the error message is not trapped.  By the way, if you know of good all encompassing error-handling routine I'd appreciate a 411.

Commented:
When I first saw your reference to Me.crptReport, I thought "I've never done it that way, but I guess it'll work."  However, after experimenting, I think the "Me" is part of your problem.  If that line doesn't give you an error message, it appears to be referencing something elsewhere in your program.  Remove the "Me." and just use:

crptReport.ReportFileName = App.Path & "\" & strReportName
and
crptReport.ReplaceSelectionFormula (strSelectionFormula)
etc.

And, immediately following your On Error Goto line, insert the following line:
Set crptReport = New CRAXDDRT.Report
or else put the word "New" in your Dim statement like you did for crptApp.

What I think is happening is that it's using a "crptReport" you've defined elsewhere (perhaps as global or public?) instead of using the one you dimmed in this procedure.

To find out what the error is, just disable error checking by commenting out your On Error Goto line - Then VB will display the error message for you to see (Drawback: your error handler will not be called to handle it).  Once you determine what the error is, you can re-enable the error handler and add code to handle that specific error.

As far as an off-the-shelf error handler, I'm sure there are some out there, but I've never used any.  I always roll my own, because I often have specific actions to perform like clean up, closing files, etc.  If you want to create a generic handler for all your app's errors, check out this Microsoft article:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q191/4/74.asp&NoWebContent=1

There are a bunch of error handlers if you google for "Visual Basic Auto Error Handler" or similar search.


Author

Commented:
Hello TE,
Thanks for the assistance but I did not have the "Me." prefixes on the respective lines and I kept getting:
Run-time error '91':
Object variable or WITH block variable not set

I removed the "Me." from the following line to recreate the error message:
Me.crptReport.ReplaceSelectionFormula (strSelectionFormula)

Thanks for the other recommendations.

Commented:
You still got the error even after setting crptReport = New CRAXDDRT.Report?

Author

Commented:
Yes!

Private Sub buildReport(strReportName As String)
    Dim crptApp As New CRAXDDRT.Application
    Dim crptReport As New CRAXDDRT.Report
    Dim strSONO As String
    Dim strSpace As Integer
    Dim crRptDate As String
    Dim strSelectionFormula  ' FORMULA TO selection criteria for REPORT
    Me.MousePointer = vbHourglass
    ' On Error GoTo Err_CoPrt
   
    Me.crptReport.ReportFileName = App.Path & "\" & strReportName
    crRptDate = Format$(DTReportDate, "yyyy-mm-dd")
    strSpace = 10 - Len(Trim(cboSONO))
    strSONO = Space(strSpace) + cboSONO

    If txtReportScope = "I" Then
        strSelectionFormula = "{somast01.sono} = '" & strSONO & "' and {ictran01.tdate} = Date ('" & crRptDate & "')"
    Else
        strSelectionFormula = "{ictran01.tdate} = Date ('" & crRptDate & "')"
    End If
   
    Me.crptReport.ReplaceSelectionFormula (strSelectionFormula)

    Me.crptReport.Destination = crptToPrinter
    Me.crptReport.PrinterStartPage = 2
    Me.crptReport.PrinterStopPage = 999
     
    Me.crptReport.Action = 1
    ' Free memory space
    Set crptReport = Nothing          ' This works
    ' Set Me.crptApp = Nothing
    strSONO = ""
    crRptDate = ""
    Me.MousePointer = vbDefault
    Exit Sub
Err_CoPrt:
    Call Err_Handler
    Resume Next

    Me.MousePointer = vbDefault
    Exit Sub
End Sub

Commented:
I realize you said earlier that it works, and I don't want to beat a dead dog, but I've always puzzled over why it should have worked with those Me's in there.  It just now dawned on me (don't know why it took so long :-) ...

If you copy & paste your Sub buildReport into a blank new project, the Me.crptReport's cause errors - I assume they didn't for you because (I'll bet) you already have a control named crpReport, and THAT is what is being referenced in your code here.

If you have a control (maybe a crystal viewer?) on your form named crptReport, then all the Me.crptReport .... 's are actually referencing IT, the control ... NOT the variable you Dim'd.  Try this ... change the name of crptReport in your Sub to something else (like m_crReport), and get rid of the Me's ...

Dim m_crReport As New CRAXDDRT.Report
...

Me.MousePointer = vbHourglass         '  <-  This "Me." is OK
...
   
m_crReport.ReportFileName = App.Path & "\" & strReportName
...

m_crReport.ReplaceSelectionFormula (strSelectionFormula)
...

and so on ...

Author

Commented:
I did as u suggested and I get the following error message (at the place noted in the code below):
Run-time error '438':
Object doesn't support this property or method

Here is the sub with changes as prescribed...

Private Sub buildReport(strReportName As String)
    Dim my_crApp As New CRAXDDRT.Application
    Dim my_crReport As New CRAXDDRT.Report
    Dim strSONO As String
    Dim strSpace As Integer
    Dim crRptDate As String
    Dim strSelectionFormula  ' FORMULA TO selection criteria for REPORT
    MousePointer = vbHourglass
    ' On Error GoTo Err_CoPrt
   
    my_crReport.ReportFileName = App.Path & "\" & strReportName    <------  Error code occurs here
    crRptDate = Format$(DTReportDate, "yyyy-mm-dd")
    strSpace = 10 - Len(Trim(cboSONO))
    strSONO = Space(strSpace) + cboSONO

    If txtReportScope = "I" Then
        strSelectionFormula = "{somast01.sono} = '" & strSONO & "' and {ictran01.tdate} = Date ('" & crRptDate & "')"
    Else
        strSelectionFormula = "{ictran01.tdate} = Date ('" & crRptDate & "')"
    End If
   
    my_crReport.ReplaceSelectionFormula (strSelectionFormula)
    my_crReport.Destination = crptToPrinter
    my_crReport.PrinterStartPage = 2
    my_crReport.PrinterStopPage = 999
     
    my_crReport.Action = 1
    ' Free memory space
    Set my_crReport = Nothing
    Set my_crApp = Nothing
    strSONO = ""
    crRptDate = ""
    MousePointer = vbDefault
    Exit Sub
Err_CoPrt:
    ' Call Err_Handler
' ERR_Handler:
    If Left(Err.Description, 19) <> "Application-defined" Then
        Debug.Print Err.Number, Err.Description
    End If
    Resume Next

    MousePointer = vbDefault
    Exit Sub
End Sub

Commented:
What version CR are you using?  The methods "ReportFileName", "ReplaceSelectionFormula", "Destination", etc. are all part of the Crystal Reports Print Engine (crpe32.dll).  According to the CR Developer's Help file ...
"The Crystal Reports Print Engine is now considered a legacy API and no longer exposes calls for any of the new features included in Crystal Reports. The API is still available to developers. Help is located in Crystal Reports Legacy SDK Help (Legacy.chm)."

If you're using an older version of CR, then I don't think what I've suggested so far will be of any use to you and I'm not sure what additional help I may be, because I'm not familiar with the old API.  However, if you're using CR 8.5 or 9.0, look in the developer's help file for the Report Object and especially for its PrintOut Method.

The following example may help (It comes from the CR helpfile, and assumes an ADO database):

Dim Connection As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim CRXApplication As New Craxdrt.Application
Dim CRXReport As Craxdrt.Report
Dim CRXDatabase As Craxdrt.Database

Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;" & _
                                                  "Data Source=C:\Program Files\Crystal Decisions\Crystal Reports 9" & _
                                                  "\Samples\En\Databases\xtreme.mdb;Mode=Read"
Connection.Open
RS.Open "Select * From Customer", Connection, adOpenDynamic, adLockPessimistic, adCmdText
Set CRXReport = CRXApplication.OpenReport("c:\reports\Customer.rpt", 1)
Set CRXDatabase = CRXReport.Database
CRXDatabase.SetDataSource RS, 3, 1
CRXReport.PrintOut

I suppose you know how to open a connection & a recordset?  If not, let me know.  Your record selection formula would go in the RS.Open statement.  If you're not sure how to do this, let me know.
For examples of various connection strings, go to www.connectionstrings.com

Simply open yours as the example above does, then open your report:
Set CRXReport = CRXApplication.OpenReport(App.Path & "\" & strReportName, 1)

Set the database, then print it.  The PrintOut method has several optional parameters:
PrintOut ([promptUser], [numberOfCopy], [collated], [startPageN], [stopPageN])

So, in your case, you may use:
CRXReport.PrintOut False, 1, False, 2, 999

See the CR Developer's help and search for PrintOut.

I hope this helps!

Commented:
Oops!  That should have been:

Dim CRXApplication As New Craxddrt.Application
Dim CRXReport As Craxddrt.Report
Dim CRXDatabase As Craxddrt.Database

But, what I had would work if you referenced the CR ActiveX Designer Runtime library instead of the CR ActiveX Designer Design and Runtime library.

Author

Commented:
Hello Ted,
U said "I suppose you know how to open a connection & a recordset? "
I thought so but perhaps I'm usng an incorrect method.  By the way I'm using CR8.5 developer and I'm referencing CR ActiveX Designer Runtime library.  I've included a snippet of the relevent code.  Are my connections and DB/Table opens coded properly?

...  Variable set in MDIParent
    strDataBase = App.Path & "\ReportManager97.mdb"

...  Subs from shared routine Module
Public Sub OpenDB(dbVar, dbName, gReadOnly)
    Set dbVar = OpenDatabase(dbName, True)
End Sub

Public Sub OpenTable(rsName, dbName, tblName)
    Set rsName = dbName.OpenRecordset(tblName, dbOpenDynaset)  ' Ted, Is this openning these tables RO?
End Sub

....Report Module
Option Explicit
Dim MyDB As Database
Dim MyRSReports As Recordset
Dim rsSOMast As Recordset        ' these 4 tables are VFP tables that are linked via the MDB
Dim rsSOTran As Recordset
Dim rsSOAddr As Recordset
Dim rsARCust As Recordset
....
Private Sub Form_Load()
    If strDataBase <> "" Then
        OpenDB MyDB, strDataBase, True
    Else
        OpenDB MyDB, App.Path & "\ReportManager97.mdb", True
    End If
    ....    
    OpenTable MyRSReports, MyDB, "tReports"
    OpenTable rsSOMast, MyDB, "somast01"
    OpenTable rsSOTran, MyDB, "sotran01"
    OpenTable rsSOAddr, MyDB, "soaddr01"

Commented:
Set dbVar = OpenDatabase(dbName, True)   -   The "True" here opens the database in exclusive mode.  Only your program can access the database while it is open.  The gReadOnly variable is unused.  If you intend to use it to set the database to read-only, then you should use: Set dbVar = OpenDatabase(dbName, True, gReadOnly)

Set rsName = dbName.OpenRecordset(tblName, dbOpenDynaset)   -   This opens the table for read/write.  To make it read-only, use: Set rsName = dbName.OpenRecordset(tblName, dbOpenDynaset, , dbReadOnly)

You're using DAO here.  My previous examples used ADO.  I'm much more familiar with ADO then DAO.  However, I think the following will work for you:

Dim CRXApplication As New Craxddrt.Application
Dim CRXReport As Craxddrt.Report
Dim CRXDatabase As Craxddrt.Database
...

Set CRXReport = CRXApplication.OpenReport(App.Path & "\" & strReportName, 1)
Set CRXDatabase = CRXReport.Database
CRXDatabase.SetDataSource MyRSReports, 3, 1
CRXReport.PrintOut False, 1, False, 2, 999

This assumes this Sub (BuildReport as I recall) is in the report module where "MyRSReports" is defined.

Author

Commented:
Hello TE,

You've been real help and a trouper for indulging my questions.  I'm having trouble now with a locking problem but I'll open that as another thread.  I would like to convert from DAO to ADO, what is the best reference source for this task?

Thanks,
Lannie

Commented:
Take a look at this experts-exchange question/answer.  The answer is succinct and accurate (they discuss conversion from VB6 to VB.net also, but the sample code shows going from DAO to ADO):

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20673460.html

My primary source of answers for almost everything is the Microsoft Developers Network (MSDN) Library.  If you don't have it and you do a lot of visual programming, you should install it.  I think it comes with most visual studio products.  It is available on line at msdn.microsoft.com.  Use the search engine there.  The knowledgebase contains lots of articles.

My second source is the web, mostly this site, secondly, Google.  You'd be amazed what you can find by typing "visual basic convert DAO ADO" into Google.

Cheers!

Commented:
In addition to my last post, I came across Microsoft's KB article: 225048  Look for it on their website.

By the way, do you REALLY need to convert from DAO to ADO?  If it's working, why change?  There are some things DAO does that ADO doesn't.  For example, in DAO, you can modify a database - add tables and columns, etc.  In ADO you either can't or it's very difficult.  In my last app, I used ADO for all database access/queries, then used DAO to modify the databases.  So I had both ADO and DAO in one app.  (It was a BIG app).

Author

Commented:
Hello Ted,

No I don't really need to convert but I thought I would look at it as a possiblility of getting by the locking problem I'm now experiencing.  In fact I believe the locking dilemma is causing a problem with the stability of the app.

I placed a question on the Foxpro board and VB6 board regarding this matter -- search for "Locking problem: VFP6" and "Locking problem between VFP", respectively.  The only respondent is on the Foxpro board and he appears to be little light.   Perhaps you can resolve this matter also. :-)

Thanks,
Lannie

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial