Error message on recordset .Edit: Method or data member not found

[SashaBuilder3]

Hi,

Here is a simple VBA code I'm trying to use to update a table (Access 2000):

*****************
Dim dbs
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection

Set dbs = CurrentDb()

Set cnn = CurrentProject.Connection
rst.Open "tbl2", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

rst.MoveFirst

With rst

Do
.Edit
!Data2 = Data2 & "_a"
.Update
.MoveNext
Loop While rst.EOF = False

End With

rst.Close

*******************

When I run it an error message comes up: 'Method or data member not found'


What's wrong with this code?


Thanks,

Alexandre

 

[Guest_imported]

Try this. You don't need an obj.Edit statement at all. It's implicit. Worked fine for me...

******************************************************
Using ActiveX Data Objects 2.0 library
Create the following...

TABLE Test
TestID AutoNumber
TestFld Text

********************************************************
Function Test()
Dim rs As New ADODB.Recordset
rs.Open "Test", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Dim cnt As Integer

Do While cnt < 50
rs.AddNew
rs!TestFld = &quot;Adding&quot;
rs.Update
cnt = cnt + 1
Loop

rs.MoveFirst

Do While Not rs.EOF
rs!TestFld = &quot;Editing&quot;
rs.Update
rs.MoveNext
Loop

End Function

 

[Guest_imported]

That would be ActiveX Data Objects 2.1 reference not 2.0

 

[SashaBuilder3]

cmmrfrds,

What a great link!

There I found the syntax:

rst.Fields(&quot;data2&quot.Value = rst.Fields(&quot;data2&quot.Value & &quot;_a&quot;
rst.Update


Robert,

I also tried your way and it works as well!

No more Edit methods(?)


Thank you all, guys, so much!


Alexandre

 

[cmmrfrds]

Alexandre, I am glad you got it working. The way you have it set up is using ADO objects - which is good.

To have Access recognize these objects a reference to the DAO library would need to be set.
something like DAO object library 3.6 then,
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
would be recognized. I would just stick with the ADO objects you have set up and working.

Happy coding!!!

 

[Guest_imported]

Glad I could assist... CMMRFRDS was very enlightning also for me...

Enjoy!...