Solved

DataReport does not pick up updated values

Posted on 2002-05-19
4
174 Views
Last Modified: 2010-05-02
I have an application that uses ADO to connect to an Access database and there are a number of forms which are used to update the database. The application also has a reporting subsystem which uses a DataEnviromnent and a number of DataReport objects. The problem is, my reports do not reflect the changes that are made in the updating forms until the application i closed and restarted. I create an ADO connection before each SQL statement in the forms and close it and set it to Nothing imediately afterwards to ensure that my udpates are being flushed to the database but I don't seem to have the same level of control over the DataEnvironments connection. I'm assuming that the problem is cache related, so I'm trying the flash the connections cache by using the JetEngine.refreshCache method. This works until I unload the form which calls the reports and then go back in to it at which point I get a runtime error "3709 - The connection cannot be used to perform this operation. It is either closed or invalid in this context" as I try to refresh the cache.

The relevant bits of code are as follows :
the load procedure of the entire application:
Private Sub Form_Load()
    Dim db As String
       
    On Error GoTo frmTopLevel_formload_error
   
    Dim ini As New ProfileReader
    'Dim sDbPath As String
    Dim sDefaultDbPath As String
    sDefaultDbPath = App.Path + "\data\saltcam.mdb"

   
    ini.ProfileSection = "General"
    ini.ProfileKeyword = "DBLocation"
    ini.ProfileDefault = sDefaultDbPath
    ini.IniFileName = App.Path & "\saltcam.ini"
   
    gdb = ini.GetPropertyString
   
    ini.ProfileKeyword = "HelpPath"
    sHelpPath = ini.GetPropertyString
   
    DataEnvironment1.Connection1 = gdb
   
   
Exit Sub

The load proc of the report form :
Private Sub Form_Load()
Dim i As Integer
Dim fred As New JRO.JetEngine

    If DataEnvironment1.Connection1.State = adStateOpen Then
       fred.RefreshCache DataEnvironment1.Connection1
    End If
    optDestination(0).Value = True
    optDestination(1).Value = False
   
    ReportArray(0).ReportLiteral = "Prioritised Waiting List"
    ReportArray(1).ReportLiteral = "Review List"
    ReportArray(2).ReportLiteral = "Caseload Totals"
    ReportArray(3).ReportLiteral = "Analysis of Waiting Times"
    ReportArray(4).ReportLiteral = "Active Caseload sorted by Status"
    ReportArray(5).ReportLiteral = "School Age children on caseload sorted by school"
    ReportArray(6).ReportLiteral = "Active Caseload sorted alphabetically"
    ReportArray(7).ReportLiteral = "Waiting List sorted by Primary Need"
    ReportArray(8).ReportLiteral = "Waiting List sorted by Time Waited"
   
    For i = 0 To ReportArrayLength - 1
       optReport(i).Caption = ReportArray(i).ReportLiteral
    Next
   
    Set fred = Nothing
   
End Sub

The code which submits the report :
Private Sub cmdReport_Click()
Dim i As Integer
Dim s As String
Dim bcontinue As Boolean
   
   On Error GoTo cmdReport_Error
   i = 0
   
   While optReport(i).Value = False
        i = i + 1
   Wend

   If optDestination(0).Value Then
     Select Case i
     Case cnstPrioritisedWaitingList
         SetUpPrioritisedWaitingList PrioritisedWaitingList
         Set frmPrintScheduler.rep = PrioritisedWaitingList
     Case cnstreview_list
        Set frmPrintScheduler.rep = review_list
     Case cnstStatusTotals
        Set frmPrintScheduler.rep = StatusTotals
     Case cnstMonthlySummary
        bcontinue = SetUpMonthlySummaryOfWaitingTimeCall(MonthlySummaryofwaitingtime)
        If bcontinue Then
           Set frmPrintScheduler.rep = MonthlySummaryofwaitingtime
        Else
           Exit Sub
        End If
     Case cnstcaseload_status
        Set frmPrintScheduler.rep = caseload_status
     Case cnstcaseloadbyschool
        Set frmPrintScheduler.rep = caseloadbyschool
     Case cnstalphabetical_caseload
        Set frmPrintScheduler.rep = alphabetical_caseload
     Case cnstWaitingListByNeed
        Set frmPrintScheduler.rep = WaitingListByNeed
     Case cnstWaitingListByTimeWaited
        Set frmPrintScheduler.rep = WaitingListByTimeWaited
     Case Else
        Err.Raise 51001, "Reports Engine", "Unidentified Report Requested - Report Array Member " & i
     End Select
     
     frmPrintScheduler.Show vbModal
         
  Else
     Select Case i
     Case cnstPrioritisedWaitingList
        SetUpPrioritisedWaitingList PrioritisedWaitingList
        PrioritisedWaitingList.Show vbModal
     Case cnstreview_list
        review_list.Show vbModal
     Case cnstStatusTotals
        StatusTotals.Show vbModal
     Case cnstMonthlySummary
        bcontinue = SetUpMonthlySummaryOfWaitingTimeCall(MonthlySummaryofwaitingtime)
        If bcontinue Then
           MonthlySummaryofwaitingtime.Show vbModal
        Else
           Exit Sub
        End If
     Case cnstcaseload_status
        caseload_status.Show vbModal
     Case cnstcaseloadbyschool
        caseloadbyschool.Show vbModal
     Case cnstalphabetical_caseload
        alphabetical_caseload.Show vbModal
     Case cnstWaitingListByNeed
        WaitingListByNeed.Show vbModal
     Case cnstWaitingListByTimeWaited
        WaitingListByTimeWaited.Show vbModal
     Case Else
          Err.Raise 51001, "Reports Engine", "Unidentified Report Requested - Report Array Member " & i
     End Select


  End If
 
  Exit Sub

cmdReport_Error:
  FatalError
End Sub


The code which actually runs the report

Private dd As DataReport
Private bPrintingFinished As Boolean
Property Set rep(d As DataReport)
   Set dd = d
End Property

Private Sub Command1_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub Timer2_Timer()
   dd.PrintReport False, rptRangeAllPages
   Timer2.Interval = 0
   
   While dd.AsyncCount > 0
     DoEvents
   Wend

   Unload dd
   Set dd = Nothing
   Unload Me


I am absolutely stuck with this - I have a suspicion that if I were to get rid of the data environment altogether and use ADO recordsets then my problem would go away, but if there is an easy way to get it to work without doing all that work, I'd prefer to do it.

Thanks in advance

Dave
0
Comment
Question by:howesd
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7020543
Try refreshing the Data Report prior to printing.  In your case:
dd.Refresh
dd.PrintReport False, rptRangeAllPages

Anthony
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 300 total points
ID: 7021553
You need to refresh your command before refreshing the report

See this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244779
0
 
LVL 2

Expert Comment

by:ventond
ID: 7022789
Usually when things like this happen, it means that your form/report is loading before you think it is. Put a breakpoint on the form_load of the report. I bet you will find the report is loaded before you make any changes to the data. Consequently locking in the data at the time of the report.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7025673
Why the "B" grade?  Was the answer not fully satisfactory?

Anthony
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question