I am trying to implement a update field in SQL run:



Located below is the code I am using:



To summarize this program if BatchID is equal to RowID then the value of 

Branches will be the same as to Address.

The error that I am encounter in this program. "Update or Cancelupdate 

without Addnew or Edit" 







Dim SQLline As String

Dim Db As Database

Dim Results

Set Db = CurrentDb

    SQLline = "SELECT tbltemp.*" & _

              "FROM tbltemp;"

    Set Results = CurrentDb.OpenRecordset(SQLline, dbOpenDynaset)

        Do While Not Results.EOF

            If Results("BatchID") = Results("RowID") Then

               Results("Address") = Results("Branches")

            End If

            Results.MoveNext

        Loop

End Sub









Thanks,



Elmer

If you use a DAO recordset (and you are) you've got to precede data changes

with a call to the recordset's .Edit method.



Gratuitous extra advice: this sort of task is usually more efficiently done

with SQL.  Something like

  UPDATE tblTemp

  SET tblTemp.Address = tblTemp.Branches

  WHERE tblTemp.BatchID = tblTemp.RowID ;



Should serve you.  Throw that in the SQL view of a query & hit the

exclamation point button on the toolbar, or pass it as a string to the

DoCmd.RunSQL method & you'll be set.



Cheers,



-Roy



-----Original Message-----

From: Espinosa, Elmer L. [mailto:elmerespinosa@y...]

Sent: Tuesday, August 14, 2001 8:03 AM

To: Access

Subject: [access] Update fields in SQL





I am trying to implement a update field in SQL run:



Located below is the code I am using:



To summarize this program if BatchID is equal to RowID then the value of

Branches will be the same as to Address.

The error that I am encounter in this program. "Update or Cancelupdate

without Addnew or Edit"







Dim SQLline As String

Dim Db As Database

Dim Results

Set Db  CurrentDb

    SQLline  "SELECT tbltemp.*" & _

              "FROM tbltemp;"

    Set Results  CurrentDb.OpenRecordset(SQLline, dbOpenDynaset)

        Do While Not Results.EOF

            If Results("BatchID")  Results("RowID") Then

               Results("Address")  Results("Branches")

            End If

            Results.MoveNext

        Loop

End Sub









Thanks,



Elmer

dim strSql as string

dim db as database

dim rec as recordset



strSql =3D tbltemp



set db =3D currentdb()

set rec =3D db.OpenRecorset(strSql)

do until rec.EOF

       if rec("BatchID") =3D rec("RowID") then

               rec.Edit

               rec("Address") =3D rec("Branches")

               rec.Update

       end if

       rec.MoveNext

loop

rec.close

db.close





>>> "Espinosa, Elmer L." <elmerespinosa@y...> 08/14 10:03 AM >>>

I am trying to implement a update field in SQL run:



Located below is the code I am using:



To summarize this program if BatchID is equal to RowID then the value of

Branches will be the same as to Address.

The error that I am encounter in this program. "Update or Cancelupdate

without Addnew or Edit"







Dim SQLline As String

Dim Db As Database

Dim Results

Set Db =3D CurrentDb

    SQLline =3D "SELECT tbltemp.*" & _

              "FROM tbltemp;"

    Set Results =3D CurrentDb.OpenRecordset(SQLline, dbOpenDynaset)

        Do While Not Results.EOF

            If Results("BatchID") =3D Results("RowID") Then

               Results("Address") =3D Results("Branches")

            End If

            Results.MoveNext

        Loop

End Sub









Thanks,



Elmer