Solved

DataReport does not pick up updated values

Posted on 2002-05-19
4
172 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
Comment Utility
Try refreshing the Data Report prior to printing.  In your case:
dd.Refresh
dd.PrintReport False, rptRangeAllPages

Anthony
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Why the "B" grade?  Was the answer not fully satisfactory?

Anthony
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now