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.p...t=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?