VB6 little problem with subtraction

[hallianonline]

Hello,
I am facing a very little problem but I am totally confused how to resolve it
I have two tables in my access database which have similar items in it from the first table i.e tbl_reci_inds
I have selected reci_prod_code which is a product code, also in this table this product code is used multiple times for different items e.g.

+----+----------------+-----------+----------+
| ID | reci_prod_code | reci_inde | reci_qty |
+----+----------------+-----------+----------+
| 74 |      123518233 | hello     |       15 |
| 75 |      123518233 | New Item  |       80 |
| 77 |      123518233 | Egg       |        1 |
+----+----------------+-----------+----------+

Now I want to select each item from this table having same prod_code and remove its quantity from the second table which is basically used for stocks the stock table is tblCafe

+---------+-----------+----------+
| Item_id | Item_name | Item_qty |
+---------+-----------+----------+
|       1 | hello     |      425 |
|       2 | New Item  |      500 |
|       3 | Egg       |     1000 |
+---------+-----------+----------+

Now the thing is when I select product number 1001 and put its quantity e.g. 2
It should select every item from table 1 and multiple the usage which makes 2xqty and then subtract the answer from table 2 which is stock
My code looks fine but the only problem I am facing is it only subtracts the first item from the table2 It may be because of query statements and close connections but please and verify what I am doing wrong and how can I resolve this
Here is my code

If Text3.Text = "Café Items" Then
If rs.State = 1 Then rs.Close
sql = "SELECT * FROM tbl_reci_inds WHERE reci_prod_code = '" & Text1.Text & "'"
rs.Open sql, cn
With rs
Dim my_prod As String
myprod = !reci_prod_code
Do Until .EOF
If Text1.Text = myprod Then
            Dim final_prod As String
            Dim final_qty As String
            final_prod = !reci_inde
            final_qty = Val(Text5.Text) * Val(!reci_qty)
            sql = "SELECT * FROM tblCafe WHERE Item_name = '" & final_prod & "'"
            rs.Execute sql

            With rs
            .Update
            !Item_qty = Val(!Item_qty) - final_qty
            .Update
            End With
End If
                .MoveNext
            Loop
End With
End If

[Santanu.Das]

You have to write the code line no 7 I.E. myprod = !reci_prod_code within do loops before comparison with Text1.Text.

Hope it can help you.

[ez4u2c]

You can simplify the task by writing the code in SQL instead of manipulating each record in DAO or ADO. The following code presumes you have a button called "btnSubtInv" on your form, and that this form also has a text box "Text1" containing product code 123518233 and text box "Text3" containing "Café Items".
When you click the button the quantities of matching items with specified product code are subtracted from the inventory table, executing SQL for an update query.

Private Sub btnSubtInv_Click()
On Error GoTo Err_btnSubtInv_Click

If Me.Text3 = "Café Items" Then
    SQL = "UPDATE tbl_reci_inds INNER JOIN tblCafe ON tbl_reci_inds.reci_inde = tblCafe.Item_name " _
            & "SET tblCafe.Item_qty = [Item_qty]-[reci_qty] " _
            & "WHERE ([reci_prod_code]=" & Me.Text1 & ");"
    DoCmd.SetWarnings 0
    DoCmd.RunSQL SQL
    DoCmd.SetWarnings -1
End If

Exit_btnSubtInv_Click:
    Exit Sub

Err_btnSubtInv_Click:
    MsgBox Err.Description
    Resume Exit_btnSubtInv_Click

End Sub