Insert Into

[paulcraigdainty]

This one is seriously confusing me - I am using the following code to append a table in my db. The user enters a number into a text box and the code is designed to append records to the table based on the figure entered. The code doesn't produce any errors when executed however each time Access starts to append my table but doesn't stop and I end up with thousands of records. For some reason the number of records appended doesn't match the number entered into the text box.?? Has anyone any ideas?? Thanx

Private Sub cmdSubmit_Click()
Dim a, b As Integer
a = txtNumber

If a > 1 Then
b = 1
Do

DoCmd.RunSQL "INSERT INTO tblLog " _
& "(Team, AgentName, Activity, RefNo) VALUES " _
& "(forms!frm1!cboTeam, forms!frm1!cboName, forms!frm1!cboActivity, forms!frm1!txtRefno) ;"

b = b + 1
Loop
While b < a



Wend
End If
End Sub

 

[MarkK]

If you know in advance how many iterations of a loop you will perform, I'd consider using a For...Next loop.

Private Sub cmdSubmit_Click()
  Dim i As Integer

  For i = 1 to txtNumber
    DoCmd.RunSQL "INSERT INTO tblLog " & _
    "(Team, AgentName, Activity, RefNo) VALUES " & _
    "(forms!frm1!cboTeam, forms!frm1!cboName, forms!frm1!cboActivity, forms!frm1!txtRefno) ;"
  Next i

End Sub

But this adds txtNumber new records all with exactly the same data, and this doesn't seem that useful.