Appending one row at a time

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
 
G

Guest

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
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Appending one row at a time 4
Displaying records in a form 10
run time error 6 overflow 5
Appending one row at a time 5
Iterate/loop through a form to copy value from one control to anot 2
Garbage in a string variable loaded from a field 2
comparing the date entered to a form and the date field in a table 2
Cycling Through Records in a Table 4

Top