loop adding extra record

[waubain]

This is my first post at Tek-tips. I am a beginner in vba and this is my first attempt at a loop. My end product needs to be more complex, but starting with a simple version to learn what happens in a loop. My goal is to add multiple records at once to tblBottle.

tblBottle has 3 fields:
AutoID (primary key)
KitNum
BottleNum

In frmAddBottles there are 2 textfields and one btn
KitNum
QtyReceived

When I click the button it mostly works, but I get an extra record in tblButton with the KitNum but a blank BottleNum. If my KitNum was 123 and QtyReceived was 3 then tblBottle recordset looks like this:

AutoID, KitNum, BottleNum
1, 123,
2, 123, 1
3, 123, 2
4, 123, 3


Here is my code

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

Private Sub btnAddMultiRecords_Click()

Dim rs As DAO.Recordset
Dim i As Integer
Dim regNumber As Integer
On Error GoTo HandleError

regNumber = Me.txtBottlesReceived

Set rs = CurrentDb.OpenRecordset("tblBottle")
With rs
For i = 1 To regNumber
.AddNew
![KitNum] = Me.KitNum
![BottleNum] = i
.Update
Next
End With
rs.Close
Set rs = Nothing

ExitHere:
Exit Sub

HandleError:
MsgBox Err.Description
Resume ExitHere

End Sub

Thanks for any help

 

[SkipVought]

hi,

Welcome to Tek-Tips.

Please post MA Access questions in one of the many MS Access forums like forum702 or forum705, where you will have a better change to get an good answer.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?

Private Sub btnAddMultiRecords_Click()
Dim i As Integer
For i = 1 To Me!txtBottlesReceived
  CurrentDb.Execute "INSERT INTO tblBottle(KitNum,BottleNum) VALUES(" & Me!KitNum & "," & i & ")")
Next
End Sub

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

 

[MajP]

I would suggest PHVs solution, because SQL is usually faster and more efficient than using a recordset.

I do not see a problem, but I would block it differently. You have a With block and a for loop inside the block. To me everything in a with block should be a property or a method of the object. The addnew/update is not really a block, but I always write it as if it is one so that I close the addnew/edit with an update.

For i = 1 To regNumber
With rs
.AddNew
![KitNum] = Me.KitNum
![BottleNum] = i
.Update
End With
Next i
rs.Close
Set rs = Nothing

 

[waubain]

I got it working. This probably was a newbie error, but both the form and KitNum in the form were bound to tblBottles the KitNum in tblBottles. I think somehow that created a record since the incomplete record was always the first record. When I deleted the control source for both, it works perfectly.

I rearranged as MajorP suggested, and left it that way for now since this was only part one. I file the SQL away for later use.

Thank you everyone for your suggestions. I will post the next part to this questoin in one of the access forums next time.