Mulitple record saves using for next. HELP ME

[hrg]

In my database i have a form which when enetering data it is saved to the table. The problem i have is i want to make multiple saves for maybe 1 record. I am using a for to next statement and the code is below

Private Sub Text43_Change()

Dim i As Integer

If Not (IsNull(Text43)) Then
For i = 1 To Text43
DoCmd.Save frmorder
Next i
Else
If (IsNull(Text43)) Then
End If
End If

End Sub

The problem i am having is that if i enter 3 into TEXT43 then it does not save it 3 times in the table what am i doing wrong?

e.g.

Form Name :- frmorder

Name : John
Number : 9999
Number of orders (Text43) : 3

Table Name :- Order

(Primary key)
Autonumber Name Number NumberofOrders
1 John 9999 3
2 John 9999 3
3 John 9999 3

Why is the save not working i have tryed entering the Table name and form name still no luck? CAN ANYONE HELP.

 

[jebry]

Hi!

It's not working because when you do the DoCmd.Save you are just saving the same record again(like an edit). Try this:

Private Sub cmdSave_Click()

Dim i As Integer
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("YourTablename", dbOpenDynaset)

If Not (IsNull(Text43.Value)) Then
For i = 1 To Text43.Value
rst.AddNew
rst!Name = txtName.Value
rst!Number = txtNumber.Value
rst!NumberOfOrders = Text43.Value
rst.Update
Next i
End If

End Sub

As you can see I put this in a button click event so the user has more control over when the action happens. Since you are saving records this way, you may want to consider using an unbound form. Finally, it is a good idea to give your controls descriptive names.

hth Jeff Bridgham
bridgham@purdue.edu