Loop adds same record from 1 to var 1

[waubain]

I am trying to add multiple records to tblBottle in an unbound form. The records are added at one time when btnAddMultiRecords_Click(). I am very new to loops.

I receive one kit which can contain 1 to 12 bottles with randon non-sequential numbers. I created 12 unbound fields with a name of txtBottle1 ... txtBottle12. For this example: I received Kit# 22, and it contains 3 bottles with numbers: 11, 78, 39. I entered 22 in KitNum, 3 in txtBottlesReceived, and 11,78, 39 in txtBottle1, 2, and 3

This is what tblBottle looked like after running my code

AutoID, KitNum, BottleNum
1, 22, 39
2, 22, 39
3, 22, 39

It looped the correct number of times, but I do not see what I did wrong in the loop so that the last entry is what always appears in BottleNum.

Here is my code

Option Compare Database

'------------------------------------------------------------
' Add Multi Bottles on click
'
'------------------------------------------------------------

Private Sub btnAddMultiRecords_Click()

Dim rs As DAO.Recordset
Dim i As Integer
Dim regNumber As Integer
Dim strControlName As String

On Error GoTo HandleError

regNumber = Me.txtBottlesReceived
strControlName = "txtBottle"

Set rs = CurrentDb.OpenRecordset("tblBottle")

For i = 1 To regNumber
  With rs
   .AddNew
     ![KitNum] = Me.KitNum
     ![BottleNum] = Me.Controls(strControlName & regNumber).Value
   .Update
  End With
 Next i
rs.Close
Set rs = Nothing

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere

End Sub

Thank you for any advice.

 

[PHV]

![BottleNum] = Me.Controls(strControlName & [!]i[/!]).Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

PH,

Works perfectly!

I was trying to mash 2 previous posts together that each did some of what I wanted. I now understand the why of my mistake. Thanks for you advice here and on the other post.

Waubain