p2p.wrox.com Forums

Hello,
It's my first post, so hello to everybody. I'vebeen living and working in Thailand for + than 7 years now as Deputy Principal of a school. I wrote a comprehensive school management application and I am adding new features. (VB6+ADO+SQL+MS Jet)
This code gives me an error 461 I translated from French: Compilation error Method or element cannot be found.
The element that cannot be found is rs.s_SatitID, altough I specified it in the query. I wrote a For... Next loop to see whether the field names and field values are there and they are... so I do not understand where the error comes from???

Code:

Sub ReportSatitIDToOtherTables() 



Dim strSQL As String

Dim strYear As String

Dim rs As New ADODB.Recordset

Dim adoABSMACon As ADODB.Connection

Dim Field As Object

    strYear = Right$(ThaiYear, 2)

    Set adoABSMACon = New ADODB.Connection

    Set adoABSMACon = MakeADODBConnection(adUseClient)

    adoABSMACon.Open

    strSQL = "SELECT d_SatitID, g_SatitID, s_SatitID FROM (Students INNER JOIN Students_Details ON Students.s_ASUID = Students_Details.d_SatitID) INNER JOIN Guardians ON Students_Details.d_SatitID = Guardians.g_SatitID WHERE Students.s_ASUID = Students_Details.d_SatitID AND left$(s_ASUID,2)='" & strYear & "'"    'strSQL = "SELECT s_SatitID,s_ASUID,g_SatitID,d_SatitID FROM Students, Students_Details, Guardians WHERE s_ASUID = d_SatitID AND s_ASUID=g_SatitID"

    Set rs = New ADODB.Recordset

    Set rs = MakeRecordset(adoABSMACon, adCmdText, strSQL, adOpenDynamic, adLockOptimistic)

    rs.Open

    While Not rs.EOF

'here comes the For Next I mentioned and which gives me the expected 'values

    'For Each Field In rs.Fields

        'MsgBox Field.Name

        'MsgBox Field.Value

    'Next

        rs.d_SatitID = rs.s_SatitID.Value 

'The program stops above and highlights   rs.s_SatitID[:confused:]

        rs.g_SatitID = rs.s_SatitID.Value

        rs.Update

        rs.MoveNext

    Wend

    rs.Close

    adoABSMACon.Close

    Set rs = Nothing

    Set adoABSMACon = Nothing

    

End Sub