QUOTE(masipro @ 12 Jul, 2008 - 11:34 PM)
I am using MS Access as back end with Front end VB6. In my Access Table i made a query which exactly run what i wish; when i used to copy the data from this querty to another table using vb statements, it shows wrong information (rarely shows correct information for the same for some times) i.e the query has 21 rows, and when i use rs1.recordcount (where rs1 is new ADODB.Recordset)in vb it often shows 20 records found (i used msgbox for no. or records to show whether it is correct) i surprised to see, also it rarely shows as 21 record found after some times. Please could you help me in this regard.
Though i have not attached any database file or vb codings i have executed, i hope you would find what mistake i have done. waiting for reply
Regards
Masipro
CODE
Ok, the code is as follows, also i have attached *.mdb zipped file for your reference.
Set rs8 = New ADODB.Recordset
Set rs8 = GetTable("SELECT a.Buyer_ID, a.Dept, a.Season, a.Order_No, a.Plan_No, a.Style, IIf([b].[opening]<>0,[b].[opening],0) AS Opening, IIf([c].[totarr]<>0,[c].[totarr],0) AS TotArr, IIf([c].[totdesp]<>0,[c].[totdesp],0) AS TotDesp FROM (UnionBankstock as a LEFT JOIN Bankstocktemp as b ON (a.Style = b.Style) AND (a.Plan_No = b.Plan_No) AND (a.Order_No = b.Order_No) AND (a.Order_No = b.Order_No) AND (a.Season = b.Season) AND (a.Dept = b.Dept) AND (a.Buyer_ID = b.Buyer_ID)) LEFT JOIN MonthlyProduction as C ON (a.Style = c.Style) AND (a.Plan_No = c.Plan_No) AND (a.Order_No = c.Order_No) AND (a.Season = c.Season) AND (a.Dept = c.Dept) AND (a.Buyer_ID = c.Buyer_ID)")
If rs8.RecordCount > 0 Then
MsgBox rs8.RecordCount & " found"
rs8.MoveFirst
While Not rs8.EOF() = True
Set rs9 = New Recordset
Set rs9 = GetTable("MyBankStock")
rs9.AddNew
rs9(0) = rs8(0)
rs9(1) = mnth
rs9(2) = rs8(1)
rs9(3) = rs8(2)
rs9(4) = rs8(3)
rs9(5) = rs8(4)
rs9(6) = rs8(5)
rs9(7) = rs8(6)
rs9(8) = rs8(7)
rs9(9) = rs8(8)
rs9(10) = rs8(6) + rs8(7) - rs8(8)
rs9(11) = prod
rs9.Update
rs8.MoveNext
Wend
End If
MsgBox "over"
End If
Thank you,
Masi
12 Jul, 2008 - 10:34 PM
| 