Question Looping through txtbox problem

[Jia]

Hey there! I've been having problems with trying to loop through textboxes in my form.
My code will loop through all textboxes in Transaction Product where the SQL will update the Inventory table.
But whenever I run the code, it just loop through 1 row of textboxes only. I've really been trying for hours googling this but I can't really fix it. :/
This is what my form look like: jiajian.sg/test.png

Option Compare Database
Dim ctl As Control

Private Sub cmdSaveAndUpdate_Click()
    DoCmd.Save
        ' For each control (textbox, combo box, etc) in the subform,
    For Each ctl In Form_TransactionProductsSubform.Controls
        If TypeName(ctl) = "TextBox" Then
            MsgBox "First Loop"
            If ctl.Name = "txtProductID" Then
                productID = Form_TransactionProductsSubform.txtProductID.Value
            End If
            If ctl.Name = "txtQuantity" Then
                qtyPurchased = Form_TransactionProductsSubform.txtQuantity.Value
            End If
            
        End If
        If ctl.Name = "txtQuantity" Then
            MsgBox "qtyPurchased = " & qtyPurchased & " productID = " & productID
            strSql1 = "UPDATE [Products] SET [Current Stock] = [Current Stock] - " & qtyPurchased & " WHERE [Product ID] = " & productID
            
            DoCmd.SetWarnings (False)
            DoCmd.RunSQL (strSql1)
            DoCmd.SetWarnings (True)
            
            MsgBox "RunSQL done."
        End If
    Next ctl
     
End Sub

 

[WayneRyan]

Jia,

That code will not update your entire table.

It will just loop through the controls on the CURRENT RECORD and update that
particular ProductID.

You will have to apply a SQL command to the entire table.

Wayne

 

[DavidAtWork]

"But whenever I run the code, it just loop through 1 row of textboxes only"
What exactly do you mean by this statement, are you wanting to loop through more than just the 2 text boxes you list above
David

 

[Jia]

Yeah. Whenever I run the code, I want it to loop through row 1 (5, 10), row 2 (6, 3) and so on. But currently my code just loops through the very first row (5, 10).

 

[DavidAtWork]

Although your code will be looping through all text boxes, your code only performs any action if the text box name is either "txtProductID" or "txtQuantity", any other text boxes it does nothing
David

 

[Jia]

yeah, that's what I want. but it only loops the first row of txtProductID and txtQuantity, is there any way to make it loop through all the textboxes in the Transaction Products subform? (it's a datasheet form, the entire column of Product ID has the same name - txtProductID, same goes for the Quantity)

 

[DavidAtWork]

So you are wanting to update the values in the Inventory table using textboxes in the TransactionProductsSubform. Where does the TransactionProductsSubform get it's values from? If you are wanting to update all records in the Inventory table, I would use some update sql that updates Inventory table using the TransactionProductsSubform source
David

 

[Jia]

The user will have to input the values manually. Yeah I'm using SQL, but I don't really know how to update the Inventory table using the source. o.o

 

[DavidAtWork]

So you're using the values in text boxes to update the Inventory table. Do all the text boxes relate to fields in the Inventory table and do they all apply to the same Inventory table record, if so then yes that can be done from executing one sql statement, but if different text boxes apply to different Inventory table records, then you will need multiple sql statements (why don't you use a form bound to the Inventory table and records can be edited directly from the form)
David

 

[Jia]

Yes, both Product ID and Quantity in TransactionProductsSubform are related to the Inventory table and the Transaction Products table. But they don't apply to the same record. jiajian.sg/test.png
let's say I input
row 1 Product ID, Quantity: 5, 10
row 2 Product ID, Quantity: 6, 3
when I click save, it will be inserted into the Transaction Products table (so that I can generate the receipt) and the SQL will update the Inventory table and minus off the quantity purchased.
UPDATE [Products] SET [Current Stock] = [Current Stock] - " & qtyPurchased & " WHERE [Product ID] = " & productID
but is it possible to loop thru all the text boxes and the productID will change accordingly as it moves down the rows?

 

[DavidAtWork]

How many rows of text boxes are there and what are all their names, can you post a picture of this form
David

 

[Jia]

It's a datasheet form, there's only 1 text box but you can enter multiple rows of data.
jiajian.sg/access2.png

 

[Jia]

Still stuck... will anyone help me out? :X