VBA Help

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I am using VBA code to update an existing record in a
table from the data entered on a form.

The code used works fine in another database but in this
one the error message 'Compile Eror: Method or Data
Memeber Not Found' appears at the 'CurrTab.Edit' line in
the code below.

Any help would be much appreciated!!

Dim CurrDb As Database
Dim CurrTab As Recordset

Set CurrDb = CurrentDb
Set CurrTab = CurrDb.OpenRecordset("tblCases")

Do Until CurrTab.EOF
If CurrTab("CaseID") = Me!CaseID Then
CurrTab.Edit
CurrTab("ReasonUpheld") = Me!ReasonUpheld
CurrTab("Fund") = Me!Fund
CurrTab.Update 'Updates the record
 
Hi Ian,

Have you checked your references? Check for a missing reference in
comparison to a database that the code is working fine.

Hope this would help.

Alp
 
Thanks Alp - however a colleague of mine suggested
checking this and the references are the same.

Ian
 
Is the code failing, or are you getting a compile error (and not even
running the code). I suspect the latter, in which case it could that you've
got references set to both ADO and DAO, and that the ADO reference is higher
in the list. That means that Dim CurrTab As Recordset is defining CurrTab as
an ADO recordset, and ADO recordsets don't have an Edit method.

To ensure you're getting a DAO recordset, change that line to

Dim CurrTab As DAO.Recordset
 
Thanks Doug - that has solved the problem.

Would you advise changing any settings to avoid this kind
of problem in future?

Ian
 
Thanks Doug - that has solved the problem.

Would you advise changing any settings to avoid this kind
of problem in future?

Ian
 
*try to declare the variables with their library
(disambiguation)

like DAO.Recordset
like Word.Application
Dim m_ObjDoc As Word.Document

*be sure to declare every variable
put option explicit on the beginning of a module/form

*watch the order of the references
*When referencing public variables, refer to them with the
module name

sMyVar=mypublicvarsmodule.sLocation

*if possible compile your database in the vba-editor
a little check if everything is properly declared
 
With Access 2000 ansd 2002, declaring an object variable of type
"Recordset" gives you an ADO Recordset by default. ADO Recordsets do
not have an .Edit method - that's a method of a DAO Recordset.

If you want to work with a DAO Recordset, just make sure you have a
reference to Microsoft DAO 3.6 Object Library, and declare:

Dim CurrTab As DAO.Recordset
 
Well, my next suggestion was going to be the lineup of your references (via
first hand experience) but it has already been pointed out by Doug.

Glad you resolved it.

Alp
 
Back
Top