• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

VB6 Report Refresh Problem URGENT

Hi

I need a bit of help

I'm using VB6 with Access tables, I have a problem refreshing report
data using the VB6 report designer component.

I've created a data environment with two connections which both take
data from a 'template' table, the table uses the 'DELETE' and 'INSERT'
methods so that I can run a number of queries from the application's forms
making the results available from this table.

I've created two reports each one attached to one of the data environments
connections. When I run each report for the first time, both reports work
fine, report query 1 deletes and inserts the template table then the report
displays the results etc.

If I try to run another report query say using different values the new results
do work on the table but unfortunatly the report data is not updated.

The obvious report.Refresh method should work but does not, has anyone came
across this problem ? and recognise something that I've missed or something
I'm doing wrong.

Also am I doing this the right way as far as the data environment and reports,
i.e. one data environment, two connections, two reports ?

All help appreciated.

Thanks in advance

tam97
0
tam97
Asked:
tam97
  • 5
  • 4
  • 3
  • +3
1 Solution
 
wsh2Commented:
While perhaps not specifically your problem.. the Workaround may help alleviate the problem.. ie..

  Dim de As DataEnvironment1
  Set de = New DataEnvironment1
  Set DataReport1.DataSource = de
  DataReport1.Show
  Set de = Nothing

From MSDN:
--------------------------------------
PRB: DataEnvironment: DataReport Moves Form To EOF

Last reviewed: January 20, 1999
Article ID: Q190607
 
The information in this article applies to:

Microsoft Visual Basic Enterprise Edition for Windows, version 6.0


SYMPTOMS
When you have a DataReport and Form with controls bound to the same DataEnvironment, opening the report causes the form to be blank.


CAUSE
When the report steps through the records, it is moving the current position for the DataEnvironment and stops when it reaches EOF (end-of- file). This becomes the current position for the form, which displays blank fields.


RESOLUTION
Open a second copy of the DataEnvironment in code and set the DataReport's DataSource property to this variable instead of the global DataEnvironment object.
-or-

Reset the record pointer from a timer event.

STATUS
This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new Visual Basic EXE project and add a form (Form1), DataEnvironment (DataEnvironment1), and DataReport (DataReport1).

In the DataEnvironment designer, add a connection (Connection1) to a database, such as Microsoft SQL Server.

Off the connection, create a command (Command1) that selects records from a table, such as SELECT * FROM Authors.

Save the DataEnvironment.

In the DataReport designer, set the following properties:

      DataSource: DataEnvironment1
      DataMember: Command1

Add some fields from the table and save the report.

Add some textboxes to the form and set the following properties:

      DataSource: DataEnvironment1
      DataMember: Command1
      DataField: choose from the fields in the table

Run the project. The form opens and displays the first record in the table.

Close the form and add the following code to it:

      Option Explicit

      Sub Form_Load ()
        DataReport1.Show
      End Sub

Run the project again. The DataReport opens and shows the records. When you switch to the form, it is blank. The DataReport has moved the cursor to the EOF position, so the form has not record to display.

Workaround 1

Modify the form code as follows:

      Option Explicit
     
      Sub Form_Load ()
        Dim de As DataEnvironment1
        Set de = New DataEnvironment1
        Set DataReport1.DataSource = de
         DataReport1.Show
      End Sub

Run the project. The form is now correctly positioned to the first record because the DataReport is using a different instance of the DataEnvironment.

Workaround 2

Add a Timer (Timer1) to the form and set the Interval property to 0.

Modify the form code as follows:

      Option Explicit

      Sub Form_Load ()
        DataReport1.Show
        Timer1.Interval = 1
      End Sub
   
      Sub Timer1_Timer ()
        DataEnvironment1.rsCommand1.MoveFirst
        Timer1.Interval = 0
      End Sub

Run the project. The form is now correctly positioned to the first record. The timer event does not fire until the DataReport has finished stepping through the records and so safely resets the record pointer. This does not affect the DataReport.
0
 
rovermCommented:
This is what I allways do:

If DataEnvironment1.Connection1.State = adStateOpen Then DataEnvironment1.Connection1.Close
DataEnvironment1.Connection1.Open
DataEnvironment1.Command1 param1, param2
DataReport1.Show vbModal
Unload DataReport1

Just close the Connection and re-open it, after that fire the command (including the required parameters), then invoke the DataReport.
Don't forget to unload it else it will show up in the taskmanager when your application has closed !

D'Mzzl!
RoverM
0
 
tam97Author Commented:
To roverm

I'm trying to get your solution to work but its
not quite there.

It seems to close then open the data environment
but your Command1 param1, param2 line does not work in my code, could you explain what you mean by this line.

If I miss this line out and invoke the report the following message comes up:

"Invalid SQL Statement:expected INSERT, DELETE
PROCEDURE, SELECT or UPDATE"

Any further assistance appreciated.

wsh2

Thanks for your input but it's not really the problem I have, my form's OK and the report is OK first time around, it seems that I somehow need to "refresh" the connection.

Rgds tam97
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
buemCommented:
This is what I allways do:
1.make connection to database file which use in report by
       DATAreport1.datafiles(0) = app.path & "\" & data_path
       if you use many database , you vary by array.
2.set property of report file in menu file -> option ( -> report ) to refresh data every print data.
0
 
rovermCommented:
The problem is that your environment is not refreshed and so the datareport isn't either !
Closing it and re-opening it will work!
As I said, I do this all the time.

The Command1 param1, param2 is a command build in the dataenvironment, using 2 parameters: param1 and param2.
Command1 is a SQL stored procedure or statement like "SELECT * FROM MyTable WHERE MyID = ? OR MyName = ?".

If you don't use parameters then just call the command (DataEnv.Command1) before invoking the report. This will ensure that all data is current.

Another way to ensure the latest data is to add a dummy record to the database, call the update statement, invoke the datareport and remove the dummy record again.
You just havbe to filter to dummy record.

D'Mzzl!
RoverM
0
 
jrspanoCommented:
the problem is that access and jet use a lazy write cache if you put a break after the info gathering and before the report.refresh and wait about 5 seconds and then push f5 to continue it will work every time.  to code around this add

DBEngine.BeginTrans
DBEngine.CommitTrans dbForceOSFlush

right before the dreport.refresh statement.  this will flush the cache to disk and then run the report that is now updated.  before it was pulling the info from the table that had the old info in it because the new had not been written yet.
0
 
tam97Author Commented:
RoverM

Thanks for your help got it working, could you send a proposed answer so that I can award you the points for this question.

Rgds Tam97

jrspano

Thanks for your input, it got me looking in another direction that will assist me in the future, so could you answer a dummy question so that I can give you some points for your help.

Rgds Tam97
0
 
jrspanoCommented:
ok here you go
0
 
jrspanoCommented:
jrspano changed the proposed answer to a comment
0
 
jrspanoCommented:
if this doesn't get it tellme.  there are also several more way to to this also.  you can use the form
wks.BeginTrans
wks.CommitTrans dbForceOSFlush
where wks is the name of a dim'd workspace.  if you use data controls the datacontrol.refresh is supposed to write all the data also but i have hade problems with this working somtimes.
0
 
rovermCommented:
You can accept my comment as an answer !

Thanks!

D'Mzzl!
RoverM
0
 
ahmedaliCommented:
in the begining i use Crystal report ,but  i faced with some problems like yours and date problem , so now i am using Active Report it is very easy and free from problems , please upgrade to it , you will find it in this link :
http://www.datadynamics.com/download 

thanks

0
 
tam97Author Commented:
Comment accepted as answer
0
 
tam97Author Commented:
Sorry for delay, I thought I had already accepted your answer, must be old age creeping in.

Rgds tam97
0
 
tam97Author Commented:
ahmedali

Thanks for you comment will have a look at the site you quoted

rgds tam97
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now