Appending one row at a time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table containing nine part numbers called TestParts, and an empty
table called TestOutput. I need a loop that takes the first part number from
TestParts and puts it in TestOutput, then moves to the next part number in
TestParts and does the same thing. The loop continues while Not
TestParts.EOF This is what I have so far, but obviously it doesn't quite
work. I'm new at this so any help would be greatly appreciated!

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Dim rstTestOutput As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Set rstTestOutput = CurrentDb().OpenRecordset("TestOutput")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process"
Else
DoCmd.OpenQuery ("TestQueryDelete")
rstTestParts.MoveFirst
While Not rstTestParts.EOF
rstTestParts.Edit
rstTestParts![PartNumber] = g_strPartNumber
rstTestOutput.AddNew
rstTestOutput.Update
rstTestParts.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing

End Function
 
Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Dim rstTestOutput As Recordset

Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Set rstTestOutput = CurrentDb().OpenRecordset("TestOutput")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process"
Else
rstTestParts.MoveLast '<--To fully populate the recordset
rstTestParts.MoveFirst
While Not rstTestParts.EOF
rstTestOutput.AddNew
rstTestOutput![PartNumber] = rstTestParts![PartNumber]
'Repeat the above line for every field you want to populate in rstTestOutput
rstTestOutput.Update
rstTestParts.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing
Set rstTestOutput = Nothing
End Function
 
works like a charm...thanks a bunch

Klatuu said:
Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Dim rstTestOutput As Recordset

Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Set rstTestOutput = CurrentDb().OpenRecordset("TestOutput")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process"
Else
rstTestParts.MoveLast '<--To fully populate the recordset
rstTestParts.MoveFirst
While Not rstTestParts.EOF
rstTestOutput.AddNew
rstTestOutput![PartNumber] = rstTestParts![PartNumber]
'Repeat the above line for every field you want to populate in rstTestOutput
rstTestOutput.Update
rstTestParts.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing
Set rstTestOutput = Nothing
End Function

BK said:
I have a table containing nine part numbers called TestParts, and an empty
table called TestOutput. I need a loop that takes the first part number from
TestParts and puts it in TestOutput, then moves to the next part number in
TestParts and does the same thing. The loop continues while Not
TestParts.EOF This is what I have so far, but obviously it doesn't quite
work. I'm new at this so any help would be greatly appreciated!

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Dim rstTestOutput As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Set rstTestOutput = CurrentDb().OpenRecordset("TestOutput")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process"
Else
DoCmd.OpenQuery ("TestQueryDelete")
rstTestParts.MoveFirst
While Not rstTestParts.EOF
rstTestParts.Edit
rstTestParts![PartNumber] = g_strPartNumber
rstTestOutput.AddNew
rstTestOutput.Update
rstTestParts.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing

End Function
Click to expand...
Click to expand...
 
Back
Top