p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/)
-   VB Databases Basics (http://p2p.wrox.com/vb-databases-basics-82/)
-   -   Error 461 (http://p2p.wrox.com/vb-databases-basics/83853-error-461-a.html)

Garouda June 2nd, 2011 03:22 AM

Error 461
 
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.[:)]


All times are GMT -4. The time now is 08:32 AM.

Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.