qty in table1 minus qty in table2 equal to result column in table2

[Guest]

Any can help me to correct my coding ?
**********************************************
Dim dbs As Database, rst As Recordset
Dim strSelect As String
Dim strSelect2 As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM table1"
strSelect2 = "Select * FROM table2"


Set rst = dbs.OpenRecordset(strSelect)
Set rst2 = dbs.OpenRecordset(strSelect2)

Do While Not rst.EOF
rst.Edit
rst2.Edit
If rst![Item] = rst2![Item] Then
rst2![Result] = rst2![P1ST] - rst![Qty]
Else
rst2![Result] = "NOT OK"
End If
rst.Update
rst2.Update

rst.MoveNext
rst2.MoveNext

Loop
rst.close
rst2.close

MsgBox (" compare Done!!!")
End Sub
***************************************
Pls help me

 

[Alex Dybenko]

Hi,
Well, coding looks ok, besides that there is no need for rst.Edit and
rst.Update.
but not clear what are you doing, looks very strange, perhaps you need to
rethink the logic

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John Spencer]

I might use
Dim dbs as DAO.Database, rst as DAO.Recordset


Logic of what you are doing is suspect.
-- You are assuming that table1 and table2 have the same number of records
-- You are assuming that your queries will return those records in the same
order. Unless you impose as order that is going to be true only by
coincidence. - sometimes it will work and other times it will fail
-- You are updating rst for no reason as you are not changing anything in
rst.
-- You seem to be trying to store two types of data - numbers and text in
the Result field. So either you need to force the number value to a string
or you need to store the result in two fields or you need to have an
additional field for Ok/Not Ok

It seems to me that you should be able to do all this in a query and do it
much more efficiently.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..