VB6 Report Refresh Problem URGENT

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.

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

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

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.

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

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.

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

ok here you go

jrspano changed the proposed answer to a comment

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.

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

Comment accepted as answer

Sorry for delay, I thought I had already accepted your answer, must be old age creeping in.

Rgds tam97

ahmedali

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

rgds tam97