Access Code does not run

[jex57]

Hi
Purpose behind the code is to do the following:

1 - check to see if test exists in mark table
2 - if test does not exist - populate mark table with pupils names
3 - if test does not exist - then populate test in the mark table.


Code is as follows:

Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset


Set rs = CurrentDb.OpenRecordset("SELECT tblTests.Test_ID, tblTests.Test_Description, tblMarks.Test_ID FROM tblTests LEFT JOIN tblMarks ON tblTests.Test_ID = tblMarks.Test_ID WHERE (((tblMarks.Test_ID) Is Null));")

Set rs2 = CurrentDb.OpenRecordset("UPDATE tblMarks RIGHT JOIN tblClass ON tblMarks.Class_ID = tblClass.Class_ID SET tblMarks.Class_ID = [tblclass].[Class_ID];")

Set rs3 = CurrentDb.OpenRecordset("UPDATE tblMarks RIGHT JOIN tblTests ON tblMarks.Test_ID = tblTests.Test_ID SET tblMarks.Test_ID = [tblTests].[Test_ID] WHERE (((tblMarks.Test_ID) Is Null));")


If rs.EOF = False Then

Do While Not rs.EOF
'MsgBox (rs.Fields("tbltests.Test_ID"))
DoCmd.RunSQL (rs2)
DoCmd.RunSQL (rs3)

rs.MoveNext
Loop

End If


Error message I get is:
Invalid operation when trying to set rs2.

I haven't been able to test rs3

Any assistance would be appreciated.

Thank you



End Sub

Private Sub TestLookup_Change()
Call CheckFiltermarks
End Sub

 

[xenou]

From MSDN:

It is not a good idea to use the Source argument of the Open method to perform an action query that does not return records because there is no easy way to determine whether the call succeeded. The Recordset returned by such a query will be closed. To perform a query that does not return records, such as a SQL INSERT statement, call the Execute method of a Command object or the Execute method of a Connection object instead.
See: https://msdn.microsoft.com/en-us/library/ms675544(v=vs.85).aspx

You should use a connection execute method or a command object method to run your action queries (updates). The right joins don't make sense here. Also DoCmd doesn't take ADO recordsets as parameters (that I am aware of).

 

[jex57]

Thank you for the advice.