Loop and Append Query until the Append query no longer produces results.

[Speiro1]

Hi All

I have an Append query “PerformAnalysis” which matches data from two tables and Append it another table. Table1 is made up of long equity positions and Table2 is made up of short equity positions.An investor may purchase multiple, long and short, lots of the same security in different quantities of shares.I need to match up the long and short positions in equal numbers of shares in the table we are appending to.
Table Long
100 Shares, IBM, Lot1

Table Short
50 Shares, IBM Lot2
25 Shares, IBM Lot3
20 Shares, IBM Lot4

Result
50 Shares, IBM, Lot1 50 Shares, IBM Lot2
25 Shares, IBM, Lot1 25 Shares, IBM Lot3
20 Shares, IBM, Lot1 20 Shares, IBM Lot4

This is the code I am using but when I run it I get an "Invalid Operation" error of the "Set rs" line.

Dim rs As Dao.Recordset
Dim strSQL As String

strSQL = "PerformAnalysis"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount > 0 Then

Do Until rs.EOF
DoCmd.OpenQuery "PerformAnalysis"
CurrentDb.Execute strSQL, dbFailOnError
rs.MoveNext
Loop
End If
rs.Close

 

[pbaldy]

There are a few things wrong there. I'd use a variable for the database, the Execute method and test RecordsAffected. Air code:

  Dim db as DAO.Database

  Set db = CurrentDb
RunItAgain:
  db.Execute "PerformAnalysis"

  If db.RecordsAffected > 0 Then
    GoTo RunItAgain
  Else
    'all done
  End if

 

[Speiro1]

You're amazing!! Thank you for the help.

 

[pbaldy]

Happy to help!