Solved

VB6 Report Refresh Problem URGENT

Posted on 2000-05-16
15
818 Views
Last Modified: 2013-12-25
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
Comment
Question by:tam97
  • 5
  • 4
  • 3
  • +3
15 Comments
 
LVL 14

Expert Comment

by:wsh2
ID: 2815940
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
 
LVL 12

Expert Comment

by:roverm
ID: 2816176
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
 

Author Comment

by:tam97
ID: 2816460
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
 

Expert Comment

by:buem
ID: 2816495
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
 
LVL 12

Expert Comment

by:roverm
ID: 2822357
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
 
LVL 3

Expert Comment

by:jrspano
ID: 2823343
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
 

Author Comment

by:tam97
ID: 2823725
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 3

Expert Comment

by:jrspano
ID: 2825183
ok here you go
0
 
LVL 3

Expert Comment

by:jrspano
ID: 2825184
jrspano changed the proposed answer to a comment
0
 
LVL 3

Expert Comment

by:jrspano
ID: 2825198
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
 
LVL 12

Accepted Solution

by:
roverm earned 100 total points
ID: 2826165
You can accept my comment as an answer !

Thanks!

D'Mzzl!
RoverM
0
 

Expert Comment

by:ahmedali
ID: 2890233
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
 

Author Comment

by:tam97
ID: 2922733
Comment accepted as answer
0
 

Author Comment

by:tam97
ID: 2922734
Sorry for delay, I thought I had already accepted your answer, must be old age creeping in.

Rgds tam97
0
 

Author Comment

by:tam97
ID: 2922768
ahmedali

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

rgds tam97
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

15 Experts available now in Live!

Get 1:1 Help Now