Stock update cul-de-sac

[jisque]

Help would be greatly appreciated from anyone who can me solve this problem.
I have an Issues form and an Issues details subform in a Stock Control application. On the Issues subform the user can enter several items to issued. I would like to update the Stock table with the quantity issued of each item on the subform with the click of a button on the Issues form.
Here's the code in a module I have used

Sub UpdateStock()
Dim db As Database
Dim rstIssues As Recordset
Dim rstStock As Recordset

strSQL1 = "Select IssueID, StockID, Quantity FROM tblIssueDetails WHERE ((([tblIssueDetails]![IssueID])=[Forms]![frmIssues]![IssueID]));"

strSQL2 = "Select StockID , UnitsInStock FROM tblStock;"

Set db = CurrentDb()
Set rstIssues = db.OpenRecordset(strSQL1, dbOpenDynaset)
Set rstStock = db.OpenRecordset(strSQL2, dbOpenDynaset)

Do While Not rstIssues.EOF
rstStock.Edit
If rstPurchases![StockID] = rstStock![StockID] Then
rstStock![UnitsInStock] = rstStock![UnitsInStock] + rstPurchases![Quantity]
End If
rstStock.Update
rstIssues.MoveNext
Loop

End Sub

I get an error message saying “… not enough arguments. 1 Expected”. Also only the first item on the sub form is updated.

 

[PaulBricker]

I don't see anything for rstPurchases until it appears in this line.

If rstPurchases![StockID] = rstStock![StockID] Then

That's about the only thing that was obvious.

Paul

 

[jisque]

Yes, thanks. The rstPurchases bit was a typing gaffe, but the problem's that it updates only the first record in the recordset and ignores the rest - say if I have 4 items in the issues subform. The code should realy be as follows:

Sub UpdateStock()
Dim db As Database
Dim rstIssues As Recordset
Dim rstStock As Recordset

strSQL1 = "Select IssueID, StockID, Quantity FROM tblIssueDetails WHERE ((([tblIssueDetails]![IssueID])=[Forms]![frmIssues]![IssueID]));"

strSQL2 = "Select StockID , UnitsInStock FROM tblStock"

Set db = CurrentDb()
Set rstIssues = db.OpenRecordset(strSQL1, dbOpenDynaset)
Set rstStock = db.OpenRecordset(strSQL2, dbOpenDynaset)

Do While Not rstIssues.EOF
rstStock.Edit
If rstIssues![StockID] = rstStock![StockID] Then
rstStock![UnitsInStock] = rstStock![UnitsInStock] + rstIssues![Quantity]
End If
rstStock.Update
rstIssues.MoveNext
Loop

End Sub

 

[Eupher]

Does the rstStock recordset return more than one record? If so, don't you also need a MoveNext with that recordset?

Ken S.

 

[PaulBricker]

OK, I think I understand a little better. The problem is you run this code from the click event of a button so the pointer in the subform is only going to look at one record. That's the quantity that will get updated. What you might want to do is move the code to the afterupdate event for one of the fields in the subform (probably quaitity) so that as each record is entered into the subform, the value from that record is evaluated in the procedure and the quantity is updated in the table.

Let me know if that is closer.

Paul

 

[jisque]

Finally been able to sort it out - and it works! Thanks to all who replied. The code is as below:



Sub UpdateStock()
Dim intCounter As Integer
Dim intTop As Integer
Dim db As Database
Dim rstPurchases As Recordset
Dim rstStock As Recordset

strSQL1 = "SELECT [tblPurchaseDetails].[PurchaseOrderID], [tblPurchaseDetails].[StockID], [tblPurchaseDetails].[Quantity] FROM tblPurchaseDetails WHERE [tblPurchaseDetails]![PurchaseOrderID]= " & [Forms]![frmReceivables]![PurchaseOrderID] & ";"
strSQL2 = "SELECT tblPurchaseDetails.PurchaseOrderID, tblPurchaseDetails.StockID, tblStock.P_Name, tblStock.UnitsInStock FROM tblStock INNER JOIN tblPurchaseDetails ON tblStock.StockID = tblPurchaseDetails.StockID WHERE tblPurchaseDetails.PurchaseOrderID = " & Me!PurchaseOrderID & ";"

Set db = CurrentDb()

Set rstPurchases = db.OpenRecordset(strSQL1, dbOpenDynaset)
Set rstStock = db.OpenRecordset(strSQL2, dbOpenDynaset)

rstPurchases.MoveLast
rstStock.MoveLast
intTop = rstPurchases.RecordCount

rstPurchases.MoveFirst
rstStock.MoveFirst

For intCounter = 1 To intTop
rstStock.Edit
rstStock![UnitsInStock] = rstStock![UnitsInStock] + rstPurchases![Quantity]
rstStock.Update
rstPurchases.MoveNext
rstStock.MoveNext
Next intCounter
rstPurchases.Close
rstStock.Close
End Sub