Code or Query Help

[mapat]

Hello,

I have 2 tables that have matching studentIDs. TableA and TableB have a 1 to many relationship from TableA to TableB (with studentID). My idea is that for all the records in TableA, I have to save the studentID for that record and go to TableB with that ID and count the number of records on TableB (with that same ID) or do something else. So that's why I have 2 loops. This is my code and for some reason it gives me a message: "Run-time error '3021' No current Record"
Is there anything wrong with syntax, or something else, or if someone else knows how to solve this problem just by writing different queries I would really appreciate it.

Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM AllAmountPaidNull;")
rst.MoveFirst
I = 0
Do Until rst.EOF
theID = rst!studentID
MsgBox theID
Set rst2 = DBEngine(0)(0).OpenRecordset("SELECT * FROM tVerification tv WHERE tv.StudentID = 'theID';")
'THIS IS WHERE IT FINDS NO MATCH
rst2.MoveFirst
Do Until rst2.EOF
temp = rst2!studentID
'MsgBox temp

Thank you very much

 

[pbaldy]

Don't understand what you're doing enough to say it's the best way, but:

Set rst2 = DBEngine(0)(0).OpenRecordset("SELECT * FROM tVerification tv WHERE tv.StudentID = " & theID)

presuming StudentID is numeric.

 

[scalextric59]

If the student ID is not on the verification table, then you have no records. You cannot "MoveFirst"

 

[mapat]

Don't understand what you're doing enough to say it's the best way, but:

Set rst2 = DBEngine(0)(0).OpenRecordset("SELECT * FROM tVerification tv WHERE tv.StudentID = " & theID)

presuming StudentID is numeric.

Actually theID is a string of 10 characters, would the syntax change?
Thanks again

 

[pbaldy]

For text:

Set rst2 = DBEngine(0)(0).OpenRecordset("SELECT * FROM tVerification tv WHERE tv.StudentID = '" & theID & "'")

 

[mapat]

It is now doing what I was looking for.

Thank you very much