Solved

DataReport does not pick up updated values

Posted on 2002-05-19
4
175 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

792 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