Updating tables

[UCLA Lorna]

I have a button on a form that queries my table for observations that meet a certain set of conditions, and then updates a field to mark these observations. But I keep getting the error message 'Compile error: Method or data member not found.' I have used the code successfully in a previous database and cannot figure out why it won't work here. Thanks for any suggestions. Here's the code:

Private Sub OK_Click()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Appointment Letter JW Q")

With rst
.MoveFirst
Do While Not .EOF
.Edit
![Status] = "08"
![DateMail] = Date
.Update
.MoveNext
Loop
End With
End Sub

 

[adevera]

Did you checked your Reference table ? Maybe you do have any reference to Microsoft DAO/ADO Object..

 

[UCLA Lorna]

I just checked - I have references to VBA, Access 9.0 object library, ActiveX object library, DAO 3.6 object library, and OLE automation. Should I have reference to anything else?

 

[adevera]

How about your fields Status and datemail.. are these fields have the same name on your field the way yo type it (except for the brackets)...

 

[UCLA Lorna]

Yes, it is the same. When I run the code, it stops at the .edit line and produces the error code.

 

[adevera]

I figure out your problem..

Your Code:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Appointment Letter JW Q")

With rst
.MoveFirst
Do While Not .EOF
.Edit
![Status] = "08"
![DateMail] = Date
.Update
.MoveNext
Loop
End With

My Code:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)

With rst
.MoveFirst
Do While Not .EOF
.Edit
![Status] = "08"
![DateMail] = Date
.Update
.MoveNext
Loop
End With

1. you are using access 2000, you need to reference your database and recordset to either DAO or ADO
2. Your setting of RST missed one parameter (see above).

My code works when I tried it. I am using A2000

 

[adevera]

Check my early post....

 

[UCLA Lorna]

Thank you! Thank you! It worked. Now I can die peacefully.

 

[adevera]

You are welcome....But can you invite us on your funeral...

 

[UCLA Lorna]

Same problem

OK - so I want to do the same thing in a new database, but the exact same code won't work here. It's been awhile, but how do I check for the references again (particularly DAO)? Thanks.

 

[WayneRyan]

Lorna,

While editing your code, select: Tools --> References

Wayne

 

[UCLA Lorna]

Thank you! I works lovely now.