Stock Take Counting

[nylex]

I have a file with the stock in it set at zero
I have a file with all the stock as it was counted - some items can be in the counted file more than once as they may have been in different places. I have started to write my code to update but have got lost in the middle
I can collect all the records one at a time but then how do i add them this total to the total of the original
....
Public Function CountStock()
Dim CurrBC As String
Dim CurrCount As Double
Dim OldCount As Double
Dim NewCount As Double
Dim lacDB As Database
Dim lacRS As DAO.Recordset, lacRS2 As DAO.Recordset
Set lacDB = CurrentDb()
Set lacRS = lacDB.OpenRecordset("StockTake")
Set lacRS2 = lacDB.OpenRecordset("StocktakeQuantity")
Do Until lacRS.EOF
If lacRS![Updated] = "0" Then
'Do Until lacRS2.EOF
'If lacRS2![BarCode] = lacRS![MasterBC] Then
'lacRS2.Edit
'lacRS2![qtyzero] = lacRS2![qtyzero] + lacRS![ExtQty]
'lacRS2.Update
'End If
End If
lacRS.MoveNext
Loop
lacRS.Close
lacRS2.Close
lacDB.Close
Set lacRS = Nothing
Set lacRS2 = Nothing
Set lacDB = Nothing
End Function

 

[jdraw]

What exactly does this mean?

some items can be in the counted file more than once

It would seem that you don't or can't have an accurate total.

 

[nylex]

If they scan and count the stock on the shelf the total is in the file then when they get to the warehouse they can come accross the same item again so it is scanned and entered. The stock can be in up to three places.

 

[pbaldy]

What are you trying to accomplish? The recordsets appear to be doing things the hard way. You can get the total of each item with a query:

SELECT BarCode, Sum(ExtQty) As TotalQty
FROM StockTake
GROUP BY BarCode

 

[nylex]

If I get the totals counted by a query how do I then add the totals to the master

 

[pbaldy]

I personally wouldn't save quantity on hand, but you could use the query in your recordset or an update query.

 

[nylex]

I thought this was a better way but it is obvious the wrong way - I have written an update query and it works
Tks