Strange problem, report repeat same details

[nasreen]

In my projects in VB6 & database SQL2005, I use a MS Access File in all client computers for the purpose of some special reports. I use crystal report 9. All are fine and OK.
Now when the clients want to print out report that depend on the local MS Access file, mostly the first report repeat for the second one also.

An example: The user wants to check his stock balance, he puts the period (FromDate &ToDate) and select his user name from the combobox that I placed in the Form to print report and click print button. The report display correctly. Now if he want to check the balnce of another user or the balance of another period, only the criteria details (user name, FromDate, ToDate, type) come correctly which is passed to the parameters in the report, but the details report display the same details of the first report.
The strange is when I chcked the table in the MS Access file the data is new and correct. But in the report it come the old one. Mostly this come for the first and second time only. After this if we print report with different criteria all the reports come correctly. If we close the Form for report print and open again the same repeat for first two reports.

What I do in my project is I collect the data from the main server SQL 2005 and insert into the table in the MS Access table. I alway delete the existing data from the access table before I insert new data. Then I call the report. But for first two times it come repeat. Really this happened about after one year I installed my project. At the time I installed it was correct. About after one year this problem come. Now I face the same problem for my two projects and one of this project is in VB6, SQL 2000, Crystal 6

I don't know how it is happend? Any idea ?

With regards,
Nasreen

[wes4dbt]

MS Access has a delay of a few seconds before it actually updates the database, I've had the same issue and it drove me nuts. Now, instead of a temp table, I usually create an empty ADO recordset and load the report data into it, then I use the recordset as the reports data source (I don't know if you can do this with CR, I use DataReports). Also sometimes if you close the database connection that loads the temp table before running the report it will solve the problem.

Why not run the report directly from the SQL 2005 DB?

[nasreen]

Hi wes4dbt,
Thanks for your replay
I have already closed all the db connections before running the report, but the result is same.
And your suggession to use recordset instead of a temp Access file, I don't know if possible to connect a crystal report with a recordset and create reports. Any idea ?

With regards,
Nasreen

[jggtz]

Try closing your Access file and execute DoEvents before print

[wes4dbt]

If you post the code for loading the temp table and running the report, I'd be glad to take a look at it.

I don't know CR but I'd be suprised if you couldn't use a recordset as a data source. I would research that possibility.

[wes4dbt]

Here check this thread http://www.vbforums.com/showthread.php?t=515019&highlight=cr+recordset

Theres more threads about this subject. I did a forum search with "cr recordset"

Good luck

[nasreen]

If you post the code for loading the temp table and running the report, I'd be glad to take a look at it.

I don't know CR but I'd be suprised if you couldn't use a recordset as a data source. I would research that possibility.

Here is my code as it is.

First I retreive the data from more than 5 tables in SQL2005 database and store in a MSFlex Grid. Then I insert these data to my Local Access Database table.



'connection details of MS SQL 2005

Public Con As ADODB.Connection
Public Rst As ADODB.Recordset
Public Com As ADODB.Command

Set Con = New ADODB.Connection
Set Com = New ADODB.Command
Set Rst = New ADODB.Recordset
'----------

'connection details of MS Access

Dim PrntCon As ADODB.Connection
Dim PrntRst As ADODB.Recordset
Dim PrntCom As ADODB.Command

Set PrntCon = New ADODB.Connection
Set PrntRst = New ADODB.Recordset
Set PrntCom = New ADODB.Command
'----------

Dim i As Integer
Dim ConStr As String

'Start data retreive

ConStr = "Provider=MSDASQL.1;Password=pwd;Persist Security Info=True;User ID=usr;Data Source=MyODBC"

MsFlxGrd.Clear
MsFlxGrd.Rows = 0
Rst.open "SELECT TT_DATE,SUM(LC_TOT_AMT) as LcAmt FROM TT WHERE IS_EZRMT = 1 AND TT_DATE >= '" & FrmDate & "' AND TT_DATE <= '" & ToDate & "' AND USR_ID = " & Me.TxtUid.Text & " AND CNCL <> 1 GROUP BY TT_DATE", Con, adOpenKeyset
If Rst.RecordCount > 0 Then
If IsNull(Rst.Fields!LcAmt) = False Then
CurRow = MsFlxGrd.Rows
MsFlxGrd.Rows = MsFlxGrd.Rows + Rst.RecordCount
FMain.pbMain.Visible = True
FMain.pbMain.Max = MsFlxGrd.Rows
For i = CurRow To MsFlxGrd.Rows - 1
MsFlxGrd.TextMatrix(i, 0) = "EzRemit"
MsFlxGrd.TextMatrix(i, 1) = Format(Rst.Fields!TT_DATE, "dd/mm/yyyy")
MsFlxGrd.TextMatrix(i, 2) = "1"
MsFlxGrd.TextMatrix(i, 3) = Rst.Fields!LcAmt
MsFlxGrd.TextMatrix(i, 4) = "0"
Rst.MoveNext
FMain.pbMain.Value = i
Next i
FMain.pbMain.Value = MsFlxGrd.Rows
FMain.pbMain.Visible = False
End If
End If
Rst.Close

Rst.open "select * from InnerTrns where Tr_Date >= '" & FrmDate & "' AND Tr_Date <= '" & ToDate & "' AND ToUsrID = " & Me.TxtUid.Text, Con, adOpenKeyset
If Rst.RecordCount > 0 Then
CurRow = MsFlxGrd.Rows
MsFlxGrd.Rows = MsFlxGrd.Rows + Rst.RecordCount
FMain.pbMain.Visible = True
FMain.pbMain.Max = MsFlxGrd.Rows
For i = CurRow To MsFlxGrd.Rows - 1
MsFlxGrd.TextMatrix(i, 0) = "Transfer [" & Trim$(Rst.Fields!FrmUsr) & " >> " & Trim$(Rst.Fields!ToUsr) & "]"
MsFlxGrd.TextMatrix(i, 1) = Format(Rst.Fields!Tr_Date, "dd/mm/yyyy")
MsFlxGrd.TextMatrix(i, 2) = "1"
MsFlxGrd.TextMatrix(i, 3) = Rst.Fields!Tr_Amt
MsFlxGrd.TextMatrix(i, 4) = "0"
Rst.MoveNext
FMain.pbMain.Value = i
Next i
FMain.pbMain.Value = MsFlxGrd.Rows
FMain.pbMain.Visible = False
End If
Rst.Close

Rst.open "select * from InnerTrns where Tr_Date >= '" & FrmDate & "' AND Tr_Date <= '" & ToDate & "' AND FrmUsrID = " & Me.TxtUid.Text, Con, adOpenKeyset
If Rst.RecordCount > 0 Then
CurRow = MsFlxGrd.Rows
MsFlxGrd.Rows = MsFlxGrd.Rows + Rst.RecordCount
FMain.pbMain.Visible = True
FMain.pbMain.Max = MsFlxGrd.Rows
For i = CurRow To MsFlxGrd.Rows - 1
MsFlxGrd.TextMatrix(i, 0) = "Transfer [" & Trim$(Rst.Fields!FrmUsr) & " >> " & Trim$(Rst.Fields!ToUsr) & "]"
MsFlxGrd.TextMatrix(i, 1) = Format(Rst.Fields!Tr_Date, "dd/mm/yyyy")
MsFlxGrd.TextMatrix(i, 2) = "2"
MsFlxGrd.TextMatrix(i, 3) = "0"
MsFlxGrd.TextMatrix(i, 4) = Rst.Fields!Tr_Amt
Rst.MoveNext
FMain.pbMain.Value = i
Next i
FMain.pbMain.Value = MsFlxGrd.Rows
FMain.pbMain.Visible = False
End If
Rst.Close

Rst.open "SELECT PV_No, PV_Date, AMT ,PV_Type FROM EzPymnt WHERE PV_DATE >= '" & FrmDate & "' AND PV_DATE <= '" & ToDate & "' AND DEPT_ID = " & Trim(TxtUid.Text), Con, adOpenKeyset
If Rst.RecordCount > 0 Then
CurRow = MsFlxGrd.Rows
MsFlxGrd.Rows = MsFlxGrd.Rows + Rst.RecordCount
FMain.pbMain.Visible = True
FMain.pbMain.Max = MsFlxGrd.Rows
For i = CurRow To MsFlxGrd.Rows - 1
MsFlxGrd.TextMatrix(i, 0) = "PV" & Rst.Fields!PV_NO & " (" & Rst.Fields!PV_Type & ")"
MsFlxGrd.TextMatrix(i, 1) = Format(Rst.Fields!PV_DATE, "dd/mm/yyyy")
MsFlxGrd.TextMatrix(i, 2) = "2"
MsFlxGrd.TextMatrix(i, 3) = "0"
MsFlxGrd.TextMatrix(i, 4) = Rst.Fields!Amt
Rst.MoveNext
FMain.pbMain.Value = i
Next i
FMain.pbMain.Value = MsFlxGrd.Rows
FMain.pbMain.Visible = False
End If
Rst.Close
Rst.open "select C_Name, C_State, C_Cntry from EzRegistr", Con, adOpenKeyset

CName = Rst.Fields!C_name
Rst.Close
Rst.open "select * from EzAgBrnch where BrName = '" & UsrBrnch & "'", Con, adOpenKeyset
CAdd = Rst.Fields!BrAdd1 & ", " & Rst.Fields!BrAdd2 & ", " & Rst.Fields!BrTel

Con.Close

'end retreive

'Start insert to local MS Access

PrntCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Reports\Ez_rpt.mdb;Persist Security Info=False"
PrntCon.open
PrntCom.ActiveConnection = PrntCon
PrntCom.CommandText = "delete from LCStckRpt"
PrntCom.Execute

For i = 0 To MsFlxGrd.Rows - 1
PrntCom.CommandText = "insert into EzLCStckUnSort values ('" & Me.MsFlxGrd.TextMatrix(i, 0) & "','" & Me.MsFlxGrd.TextMatrix(i, 1) & "'," & Me.MsFlxGrd.TextMatrix(i, 2) & "," & Me.MsFlxGrd.TextMatrix(i, 3) & "," & Me.MsFlxGrd.TextMatrix(i, 4) & ")"
PrntCom.Execute
FMain.pbMain.Value = i
Next i

Set PrntCon = Nothing
Set PrntCom = Nothing

Dim CrystApp As CRAXDRT.Application
Dim CrystRpt As CRAXDRT.Report
Dim MyFrmDtlRpt As New FrmRptViewr
Set CrystApp = New CRAXDRT.Application
Set CrystRpt = CrystApp.OpenReport(App.Path & "\Reports\LCBalanceRpt.rpt")

CrystRpt.DiscardSavedData
CrystRpt.ParameterFields.GetItemByName("FrmDate").AddCurrentValue (FrmDate)
CrystRpt.ParameterFields.GetItemByName("ToDate").AddCurrentValue (ToDate)
CrystRpt.ParameterFields.GetItemByName("User").AddCurrentValue (CmbUser.Text)
CrystRpt.ParameterFields.GetItemByName("CName").AddCurrentValue (CName)
CrystRpt.ParameterFields.GetItemByName("CAdd").AddCurrentValue (CAdd)


MyFrmDtlRpt.CRViewer91.ReportSource = CrystRpt
MyFrmDtlRpt.CRViewer91.Refresh
MyFrmDtlRpt.CRViewer91.ViewReport

Do While MyFrmDtlRpt.CRViewer91.IsBusy
DoEvents
Loop
MyFrmDtlRpt.CRViewer91.Zoom 100

MyFrmDtlRpt.Caption = MyFrmDtlRpt.Caption & " (" & Me.Caption & ")"
MyFrmDtlRpt.Show

Set CrystApp = Nothing
Set CrystRpt = Nothing


I hope you may check these codes and suggest a solution for my problem

With regards,
Nasreen

[wes4dbt]

Instead of using a temp table for the report, load the data into a ADO Recordset.


Dim rsRpt as New Adodb.Recordset

With rsRpt
' Create Recordset Fields (Columns) here
.Fields.Append "Field1", adVarChar, 80
.Fields.Append "Field2", adInteger
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

For i = 0 To MsFlxGrd.Rows - 1
rsRpt.Addnew
.
.
.
rsRpt.Update
Next i



Then set the recordset as the reports data source, see Post #6

BTW, Rst.Fields!LcAmt can be shortened to Rst!LcAmt

Good Luck

[nasreen]

Hi Mr. wes4dbt

Thanks a lot for your valuable advices.

I used the method suggested by you and it works me fine.

And I have two simple doubts I faced in my work with this method. In my temp Access file table there is a field with datatype Memo. I didn't find any datatype in Adodb.Recordset that matches with Memo type of Access file. So What datatype I can use as most suitable for Memo in ADO Recordset ?
And in the code for extract a recordset to CR:

Rpt.Database.SetDataSource rs, 3, 1

What is the use of parameter [data Tag]? in this case (3)

And another doubt I want clarify is, how we can run a select query from this Recordset ?

Is it is possible to select from a recordset as we do from a table ?

Thanks once again

With regards,
Nasreen

[wes4dbt]

What is the use of parameter [data Tag]? in this case (3)

I don't understand what your asking.


And I have two simple doubts I faced in my work with this method. In my temp Access file table there is a field with datatype Memo. I didn't find any datatype in Adodb.Recordset that matches with Memo type of Access file. So What datatype I can use as most suitable for Memo in ADO Recordset ?


Try using ".Fields.Append "memofield", adVariant

Is it is possible to select from a recordset as we do from a table ?


ADO recordsets do have a "Filter" method. rs.Filter "field1 = 'abc'". But since your loading the recordset in a loop, why not filter the records then?