HELP! data report is not updating

• • Reply

  15 years ago

  by novavb6

  Barry Fensom

  Bundaberg QLD, Australia

  Joined 15 years ago

  Hi, My data report is not updating until I restart the program. I have tried dataReport.Refresh, but this doesn't work. Do I need to refresh the data environment first or something (Complete Newbie with Designers)

  I have 2 designers added to my project: 1 Data Environment, 1 Data Report

  the report get it's info from the data environment

  Do I need to refresh the data environment somehow first?

  My code creates the tblReport Table (Deleting it first if it exists)

  Adds Fields to the table

  Adds data to the table.

  Displays the Report.

  First time time report data is correct

  after that, running cmdCalculate_Click() code again, Table gets updated with info ok, but report displays the first lot of data.

   

  Below is my code:

  Private Sub cmdCalculate_Click()

  
  
  For Index = 1 To Text1.Count - 1
  Text1(Index).Locked = True
  Next Index
  
  

  y = 0
  z = 0
  For y = 1 To txtOutputFormula.Count - 1
      For z = 1 To Text1.Count - 1
      startpos = InStr(1, txtOutputFormula(y).Text, "[" & Text1(z).Tag & "]")
      If startpos > 0 Then
       tempstr = Replace(txtOutputFormula(y).Text, "[" & Text1(z).Tag & "]", Text1(z).Text)
       'MsgBox tempstr
       txtOutputFormula(y).Text = tempstr
      End If
     
     
      'MsgBox "[" & Text1(z).Tag & "]"
  
      Next z
  Next y
  
  
  
  
  
  
  
  
  
  
  
  
  

  For p = 1 To txtResult.Count - 1
  txtResult(p).Text = ScriptControl1.Eval(txtOutputFormula(p))
  txtResult(p).Visible = True
  
  

  Next p

  For Index = 1 To Text1.Count - 1
  Text1(Index).Enabled = False
  Next Index
  cmdCalculate.Enabled = False
  
  
  

  ' Creates the tblReport Table, Deleting it first if it already exists code for this is below
  
  MakeReportTable

   

  ' this section adds data to the tblReport Table
  
  Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\products.mdb")
  Set rstrecordset = db.OpenRecordset("SELECT * FROM tblReport", dbOpenDynaset)
  

  
  For loopy = 1 To txtOutputNumberOfCuts.Count - 1
  rstrecordset.AddNew
  rstrecordset.Fields(0) = txtOutputName(loopy)
  rstrecordset.Fields(1) = txtOutputNumberOfCuts(loopy)
  rstrecordset.Fields(2) = txtResult(loopy)
  rstrecordset.Update
  Next loopy
  
  
  
  
  
  
  

  rstrecordset.Close
  Set rstrecordset = Nothing
  

  db.Close
  Set db = Nothing
  
  
  
  rptCutting.Refresh
  

  'Display the Report
  
  rptCutting.Show vbModal
  'rptCutting.Refresh
  End Sub
  
  

  code for MakeReportTable (Just in case it is important) In module1

  Sub MakeReportTable()

  Dim MyDatabase As Database
  Dim NewTable As TableDef
  

  Set MyDatabase = OpenDatabase(App.Path & "\products.mdb")

  'create the table
  Set NewTable = MyDatabase.CreateTableDef("tblReport")
  On Error Resume Next
  'delete the table if it already exists
  MyDatabase.TableDefs.Delete NewTable.Name
  
  
  
  

  
  'add the fields in the table
  With NewTable
    .Fields.Append .CreateField("Material", dbText, 50)
    .Fields.Append .CreateField("Quantity To Cut", dbInteger)
    .Fields.Append .CreateField("Size To Cut", dbInteger)
   
  End With
  
  
  
  
  
  
  

  MyDatabase.TableDefs.Append NewTable

  
  'close database
  MyDatabase.Close
  Set NewTable = Nothing
  Set MyDatabase = Nothing
  
  
  
  

  End Sub
  
  

  PS. My program is still running for some reason after all forms have been closed.

  Thanks.

  Report abuse
• • Reply

  15 years ago

  by Pyash

  Pyash

  NY, United States

  Joined 16 years ago

  Before opening the report try using

  if DataEnvironment1.rsCommand1.State = adStateOpen then Dataenvironment1.rsCommand1.close

  rptCutting.Show

  I am just assuming that DataEnvironment1 is the name of your Data environment and Report is using the command named Command1. That's the reason why I used DataEnvironment1.rsCommand1. Every command in the Data environment use a recordset in the background and the recordset name will be rs + Command name. So if your command name is cmdCutting, then the recordset name will be rscmdCutting. So modify the data environment and recordset name in the above code if necessary.

  regards,

  Pyash

   

  Report abuse
• • Reply

  15 years ago

  by novavb6

  Barry Fensom

  Bundaberg QLD, Australia

  Joined 15 years ago

  Thanks Pyash, You are a life saver.

  I had to place it just before I called the MakeReportTable Sub

  I'll shout you are virtual beer, here you are:

  Report abuse
• • Reply

  15 years ago

  by Pyash

  Pyash

  NY, United States

  Joined 16 years ago
  Glad to hear that it helped and thanx for the beer :)
  Report abuse
• • Reply

  13 years ago

  by razoreyez

  Oliver W. Pagao

  , Philippines

  Joined 13 years ago

  Good to have you here. Thank you very much.

  Report abuse
• • Reply

  13 years ago

  by phoenixS

  phoenix s

  , India

  Joined 13 years ago
  thanks to you guys....it was life saving for me too....cheers  
  Report abuse