Very Tough Question 1

[tylerwork]

This is a tough thing that I have to do. I am upgrading a FoxPro database into SQL tables/ Access Front End. I am using Access97.

We have one form where in FoxPro, when you entered a Purchase Order, there was a QUANTITY field, and when you entered "6" in the quantity, it would replicate the record 6 times. I completely understand this is against all database ideals, but it's the way that it has to work, since I am dealing with over 400,000 records right now and they do not want to "start over" or attempt to fix the current data to work in a new manner.

Anyway - How can I get the form/vba to replicate the record for the amount of times that is in teh Quantity field after I am done entering information into the record? I'd prefer to make this with a button, so the user has to be sure he's ready to replicate the data.

Thanks for the help!

 

[SeeJane]

Yikes - I agree this is a very scary way to do things. If you have absolutely no choice, you can use a For...Next loop like this

cmdYourButton_OnClick

Dim intReplicate As integer
Dim i As integer

'Get total times to repeat process
intReplicate = Me.<TextBox with number>.Value

'Add new record, repeat assigned # times
For i = 1 to intReplicate   
  DoCmd.GoToRecord , , acNewRec
Next i

Exit Sub

Good luck,
CJ

 

[tylerwork]

Thanks! I tried this out - I put everything in - I didn't seem to get any errors at all - it just doesn't seem to work.

Any more ideas? Here is my code:

----------------------------------
Private Sub Add_Button_Click()

Dim intReplicate As Integer
Dim i As Integer

intReplicate = Me.Quantity.Value

For i = 1 To intReplicate
DoCmd.GoToRecord , , acNewRec
Next i

End Sub

 

[jebry]

Hi!

You will need to actually add the records:

Private Sub Add_Button_Click()

Dim intReplicate As Integer
Dim i As Integer
Dim rst As DAO.Recordset

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

intReplicate = Me.Quantity.Value

For i = 1 To intReplicate
With rst
.AddNew
!FirstField = Me!FirstControl
!SecondField = Me!SecondControl
etc
.Update
End With
Next i

Set rst = Nothing

End Sub

Of course you will need to use the actual field names and control names to make it work. Finally, I wasn't sure so I didn't change it, but it seems to me that if you needed 6 of one record then you should only be adding 5, unless the value means 6 more records. If you only need the number of records that actually appears in the form then use this line instead of the line in the code:

intReplicate = Me.Quantity.Value - 1

hth
Jeff Bridgham
bridgham@purdue.edu