Filter or select data

TestID	TestName	Pass	DateTaken
20	PS3 Games	-1	10/2/2009 2:32:41 PM
23	IT dept	0	
28	Fixing test bugs	0	10/2/2009 2:24:18 PM
28	Fixing test bugs	0	10/2/2009 2:30:44 PM
28	Fixing test bugs	-1	10/2/2009 3:24:14 PM

                                              

1:
2:
3:
4:
5:
6:

    'append data to a temp table.
    '1st recordset to select * From Tests Where Pass = true
    'do until rs.eof
    'rs.movefirst
        '2nd recordset to select * from tests
        'rs2.movefirst
        'delete record where rs2.testID = rs.testID
        'rs2.movenext
    'rs.movenext
    'Loop
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Private Sub cmdViewOpen_Click()
 
    Dim strCommand As String
    Dim lngField As Long
    Dim db As Database, rs As Recordset
    Set db = CurrentDb()
 
    EmptyTable "locTestsAvailable"
    
    'copy all test data
    strCommand = "INSERT INTO locTestsAvailable ( Pass, DateTaken, Score ) "
    strCommand = strCommand & "SELECT qTakeTestsAvailableTest.*, TestScores.Pass, TestScores.DateTaken, TestScores.Score "
    strCommand = strCommand & "FROM TestScores RIGHT JOIN qTakeTestsAvailableTest ON (TestScores.TestID = qTakeTestsAvailableTest.TestID) AND (TestScores.UserName = qTakeTestsAvailableTest.UserName)"
    DoCmd.RunSQL strCommand
    
    DoCmd.SetWarnings False
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM locTestsAvailable WHERE ((Pass) = True)")
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            lngField = rs.Fields("TestID")
            
            DoCmd.RunSQL "DELETE * FROM locTestsAvailable WHERE ((TestID) = " & lngField & ")"
        
            rs.MoveNext
        Loop
    Else
        'If no test have passed, select groupable data
        EmptyTable "locTestsAvailable"
        
        strCommand = "INSERT INTO locTestsAvailable "
        strCommand = strCommand & "SELECT qTakeTestsAvailableTest.* "
        strCommand = strCommand & "FROM TestScores RIGHT JOIN qTakeTestsAvailableTest ON (TestScores.UserName = qTakeTestsAvailableTest.UserName) AND (TestScores.TestID = qTakeTestsAvailableTest.TestID)"
        DoCmd.RunSQL strCommand
        
        
    End If
    
    Me.Filter = ""
    Me.FilterOn = True
    cmdTakeTest.Visible = True
    cmdViewPresentation.Visible = True
    
    Me.Requery
    
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46: