Cascot are you out there??

[2176]

How I would love to give you this program and let you do it?? The module you created worked great, but it's not quite what they want now They want something a little more extensive and complicated, and I'm just a beginner trying my feet, but I thought you could look at my problem and make a suggestion...Don't give yourself a headache in the process though. The thread is located in the module forum, here it is...thread705-366769 under my user name 2176 I believe the subject is tying two functions together.
Thank you,
Toya

 

[pgk]

Hi,
Check out the following piece of code. It will copy the specified number of records from the first (Table14) to the second table( Table15). The records are selected using the Rnd() function.

The tables have an identical structure with only fields, name and id. Here id is a primary key that I use to ensure that the same record is not copied to the second table twice.

The user enters the number of records required in a text box, Text2 and then clicks on a command button, Command1 to copy the records.

Private Sub Command1_Click()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim i As Integer
Dim rc As Integer
Dim rand As Double
Set db = CurrentDb
Set rs1 = db.OpenRecordset("Select * from Table14", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Table15", dbOpenDynaset)
If rs1.RecordCount > 0 Then
rs1.MoveLast
rs1.MoveFirst
rc = rs1.RecordCount
Else
MsgBox "No records in your tabel"
rs1.Close
rs2.Close
db.Close
Exit Sub
End If
If rs1.RecordCount <= Val(Me.Text2) Then
MsgBox &quot;There are only &quot; & rs1.RecordCount & &quot; records in your table. All these will be copied to the second table.&quot;
Do While Not rs1.EOF
rs2.AddNew
rs2.Fields(&quot;name&quot = rs1.Fields(&quot;name&quot
rs2.Fields(&quot;id&quot = rs1.Fields(&quot;id&quot
rs2.Update
rs1.MoveNext
Loop
rs1.Close
rs2.Close
db.Close
Exit Sub
End If

i = 0
Do While i < Val(Me.Text2)
tryAgain:
rand = Rnd()
If rand <= rc Then
rs1.Move rand
rs2.FindFirst &quot;id=&quot; & rs1.Fields(&quot;id&quot
If rs2.NoMatch Then
rs2.AddNew
rs2.Fields(&quot;name&quot = rs1.Fields(&quot;name&quot
rs2.Fields(&quot;id&quot = rs1.Fields(&quot;id&quot
rs2.Update
i = i + 1
Else
GoTo tryAgain
End If
Else
GoTo tryAgain
End If
Loop
End Sub
Hope it helps. Let me know what happens.
With regards,
PGK