Error 461

Garouda · June 2nd, 2011, 03:22 AM

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

gbianchi · June 2nd, 2011, 07:53 AM

HI there.. although the field is there.. maybe the value is null and can't be passed??

anyway, to do what you need, probably it's easier and update query that Do all the transform inside the database directly... if that's the only you are doing with that Recordset...

Garouda · June 2nd, 2011, 09:34 AM

Thank you for replying, but that's not the issue.
You can see in the For... Next loop that I checked both the name and the value and both are correct, null value are not possible for that field.

gbianchi · June 2nd, 2011, 10:06 AM

AHhhhh.. maybe, and probably, the recorset is not updatable.. Try to run it in your database and check if you can update it.. probably not, because the fields you are returning..

Garouda · June 2nd, 2011, 10:32 AM

Thanks for trying, but apparently, you don't know.
I'll find out another way.