Continuous Form

[Cozzy]

I have a continuous form that shows all the stock from stock table with + - command to add or subtract the quantity when stock is being used or replenished with messages to inform about things, the problem that I am having it that it only looks at the first recordset for any change so if I have 4 toners in the first and 10 toners in the second recordset and press - it goes down to 3??
what am i doing wrong or am i over complicating things?

here is the code

Private Sub Command12_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL As String
Dim answer As Long

Set dbs = CurrentDb

strSQL = "select * from [tbl stock list]"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
i = rst![Quantity]
answer = MsgBox("Are you Sure if so please take a " & vbNewLine & [Toner Ref Number] & vbNewLine & "from the stock room", vbYesNo, "New Toner Required?")


If answer = vbNo Then
MsgBox "Toner request cancelled!", vbOKOnly, "CANCELLED"
ElseIf answer = vbYes Then

Forms![frm stock list]!tbquantity = i - 1


End If
Set rst = Nothing
Set dbs = Nothing


Requery
If i <= 1 Then
MsgBox "Have these Toners Been Self Ordered?" & vbNewLine & "If not please order some", vbCritical, "LOW TONER STOCK"
Else
End If
End Sub

any help would be most greatful

 

[Cozzy]

Sorry People, I really did over complicated this didn't I lol

here is the new code I dumbed myself down and it worked first time

Private Sub Command12_Click()

Dim i As Integer
Dim answer As Long

i = 1
answer = MsgBox("Are you Sure if so please take a " & vbNewLine & [Toner Ref Number] & vbNewLine & "from the stock room", vbYesNo, "New Toner Required?")


If answer = vbNo Then
MsgBox "Toner request cancelled!", vbOKOnly, "CANCELLED"
ElseIf answer = vbYes Then

Forms![frm stock list]!tbquantity = Forms![frm stock list]!tbquantity - i



End If
If Forms![frm stock list]!tbquantity <= 1 Then
MsgBox "Have these Toners Been Self Ordered?" & vbNewLine & "If not please order some", vbCritical, "LOW TONER STOCK"
Else
End If



End Sub