Loop through a recordset while adding new records to another?

[aron.ridgway]

Is there a way of looping through a form record set, while adding new records to a different form record set? using some data from the 1st record set in the new records? any pointers would be great!

thanks

 

[BlueIshDan]

Do you want it to be a continuous thing that happens while the user is using this form, or will it all be in one event?

 

[aron.ridgway]

It will only need to be done once when the form loads.

thanks

 

[BlueIshDan]

How familiar with vba are you?

 

[aron.ridgway]

I'm pretty familiar i have the following code which loops through my 1st recordset, i need help adding new records to another recordset?

Private Sub Form_Load()

Dim rstSubForm As Recordset
Set rstSubForm = Forms!frmReceive!sfrmReceiveDetail.Form.Recordset


Do While Not rstSubForm.EOF

   rstSubForm.MoveNext
Loop

End Sub

 

[BlueIshDan]

Private Sub Form_Load()

    Dim rstSubForm As Recordset: Set rstSubForm = Me.Recordset
    Dim rs As Recordset: Set rs = CurrentDb.OpenRecordset("table_name_here")

    With rs
    
        While Not rstSubForm.EOF
        
            .AddNew
            
            
            'EXAMPLE
            !field1 = rstSubForm!field1
            !Field2 = rstSubForm!Field2
            !field3 = rstSubForm!field3
            
            .Update
       
            rstSubForm.MoveNext
       
        Wend

        .Close
    End With
    
    
End Sub

try that

 

[aron.ridgway]

thank you for your reply, am i referring to the fields or should i refer to the forms textbox names that hold the data?

thanks

 

[BlueIshDan]

Refer to the fields.

 

[aron.ridgway]

thank you for the help, i've managed to get the code working, i want to display only the new records in the continuous 2nd subform? do you have any ideas how i can achieve this?

thanks

finished code

Private Sub Form_Load()

Dim rstSubForm As Recordset
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("tblreceivedetail")
Set rstSubForm = Forms!frmReceive!sfrmReceiveDetail.Form.Recordset

With rs
While Not rstSubForm.EOF

.AddNew
.Fields("OrderDetailFK") = rstSubForm.Fields("OrderDetailPK")
.Fields("UserFK") = rstSubForm.Fields("UserFK")
.Fields("ReceiveFK") = rstSubForm.Fields("ReceivePK")

.Update

   rstSubForm.MoveNext
   
   Wend
   .Close
End With
Me.Requery
End Sub