Appending one row at a time

[Guest]

I'm working on a VBA runs a query in a loop, and I need it to append one row
at a time from one table to another. There are nine items in the table, and
the loop executes nine times, as it should, but each of those times it
appends all nine of the items for a total of 81 items in the output table.
How can I change the loop so that it only does one row each time through, if
this is possible? Thanks

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
g_strPartNumber = rstTestParts![PartNumber]
DoCmd.OpenQuery ("TestQueryDelete")
While Not rstTestParts.EOF
DoCmd.OpenQuery ("TestQuery2")

rstTestParts.MoveNext

Wend
rstTestParts.Close
Set rstTestParts = Nothing
Set g_strPartNumber = Nothing

End Function

 

[Guest]

You are mixing two different ways of doing things here. Either you don't
need the loop, just the query or you need to drop the query and use recordset
handling.

 

[Guest]

Would you be able to give me an example of how to use recordset handling.
I'm a beginner in access.

You are mixing two different ways of doing things here. Either you don't
need the loop, just the query or you need to drop the query and use recordset
handling.

I'm working on a VBA runs a query in a loop, and I need it to append one row
at a time from one table to another. There are nine items in the table, and
the loop executes nine times, as it should, but each of those times it
appends all nine of the items for a total of 81 items in the output table.
How can I change the loop so that it only does one row each time through, if
this is possible? Thanks

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
g_strPartNumber = rstTestParts![PartNumber]
DoCmd.OpenQuery ("TestQueryDelete")
While Not rstTestParts.EOF
DoCmd.OpenQuery ("TestQuery2")

rstTestParts.MoveNext

Wend
rstTestParts.Close
Set rstTestParts = Nothing
Set g_strPartNumber = Nothing

End Function

 

[Guest]

Since I don't know what your query is doing, I put together this example to
show the main actions. This routine will update the part number and a
description in the table to what is on the form. Absolutely useless, of
course, but it shows looping through a recordset.

If you want to post a description of what exactly you are wanting to do, I
will be happy to have a look and perhaps offer some suggestions.

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process
Else
.MoveLast
.MoveFirst
While Not rst .EOF
.Edit
![PartNumber] = Me.txtPartNumber
![Description] = Me.txtDescription
.Update
.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing

End Function

Would you be able to give me an example of how to use recordset handling.
I'm a beginner in access.

You are mixing two different ways of doing things here. Either you don't
need the loop, just the query or you need to drop the query and use recordset
handling.

I'm working on a VBA runs a query in a loop, and I need it to append one row
at a time from one table to another. There are nine items in the table, and
the loop executes nine times, as it should, but each of those times it
appends all nine of the items for a total of 81 items in the output table.
How can I change the loop so that it only does one row each time through, if
this is possible? Thanks

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
g_strPartNumber = rstTestParts![PartNumber]
DoCmd.OpenQuery ("TestQueryDelete")
While Not rstTestParts.EOF
DoCmd.OpenQuery ("TestQuery2")

rstTestParts.MoveNext

Wend
rstTestParts.Close
Set rstTestParts = Nothing
Set g_strPartNumber = Nothing

End Function

 

[Guest]

My supervisor gave me the TestParts table with nine part numbers, and he also
gave me the TestQuery. He wants to be able to take the first part number
from TestParts and append it to the TestOutput table, then append the second
part number, and then the third and so on. (This process is setting us up
for the next step in the project) When the TestQuery runs, it asks the user
to input the parameter value each time, but this isn't what we want. We want
it to just take the first record, move to the next record and take that one
until the end of TestParts is reached. I don't think it's working with the
TestQuery, and I think it would be better to try it without using the query
in the loop. Thanks!

Since I don't know what your query is doing, I put together this example to
show the main actions. This routine will update the part number and a
description in the table to what is on the form. Absolutely useless, of
course, but it shows looping through a recordset.

If you want to post a description of what exactly you are wanting to do, I
will be happy to have a look and perhaps offer some suggestions.

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
With rstTestParts
If .RecordCount = 0 Then
MsgBox "No Records To Process
Else
.MoveLast
.MoveFirst
While Not rst .EOF
.Edit
![PartNumber] = Me.txtPartNumber
![Description] = Me.txtDescription
.Update
.MoveNext
Wend
.Close
End If
End With
Set rstTestParts = Nothing

End Function

Would you be able to give me an example of how to use recordset handling.
I'm a beginner in access.

You are mixing two different ways of doing things here. Either you don't
need the loop, just the query or you need to drop the query and use recordset
handling.

:

I'm working on a VBA runs a query in a loop, and I need it to append one row
at a time from one table to another. There are nine items in the table, and
the loop executes nine times, as it should, but each of those times it
appends all nine of the items for a total of 81 items in the output table.
How can I change the loop so that it only does one row each time through, if
this is possible? Thanks

Public Function vAppendPartNumbers()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
g_strPartNumber = rstTestParts![PartNumber]
DoCmd.OpenQuery ("TestQueryDelete")
While Not rstTestParts.EOF
DoCmd.OpenQuery ("TestQuery2")

rstTestParts.MoveNext

Wend
rstTestParts.Close
Set rstTestParts = Nothing
Set g_strPartNumber = Nothing

End Function