Hello:

I'm trying to find an efficient solution to search multiple fields in 

several tables to replace a value.  Currently, I have a dirty piece of 

VB code that searches the three fields MetaboliteID, ObjectID, and 

PrecipitantID in a table as follows:



Set RS =3D db.OpenRecordset("tblInVitroActivators", dbOpenDynaset)

    RS.MoveFirst

    Do While Not RS.EOF

        With RS

            .Edit

            If !ObjectID =3D OldName Then

                !ObjectID =3D NewName

                nChanges =3D nChanges + 1

            End If

            If !PrecipitantID =3D OldName Then

                !PrecipitantID =3D NewName

                nChanges =3D nChanges + 1

            End If

            If !MetaboliteID =3D OldName Then

                !MetaboliteID =3D NewName

                nChanges =3D nChanges + 1

            End If

            .Update

        End With

        RS.MoveNext

    Loop

    RS.Close



I have 8 tables to search in this way.  I can improve it with a case 

statement, but I'm wondering if there is a better way?  Am I making this 

much more difficult than it needs to be?



Thanks!

Sonia

Can you do this through SQL?



Ie:



strSQL = "UPDATE 'MyTable' Set 'MyField' = 'NewValue' WHERE  'MyField' 

'OldValue'"

Db.Execute(strSQL)





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

From: Sonia Carlson [mailto:soniac@u...] 

Sent: Wednesday, 18 July 2001 6:53 AM

To: Access

Subject: [access] replace function



Hello:

I'm trying to find an efficient solution to search multiple fields in

several tables to replace a value.  Currently, I have a dirty piece of

VB code that searches the three fields MetaboliteID, ObjectID, and

PrecipitantID in a table as follows:



Set RS = db.OpenRecordset("tblInVitroActivators", dbOpenDynaset)

    RS.MoveFirst

    Do While Not RS.EOF

        With RS

            .Edit

            If !ObjectID = OldName Then

                !ObjectID = NewName

                nChanges = nChanges + 1

            End If

            If !PrecipitantID = OldName Then

                !PrecipitantID = NewName

                nChanges = nChanges + 1

            End If

            If !MetaboliteID = OldName Then

                !MetaboliteID = NewName

                nChanges = nChanges + 1

            End If

            .Update

        End With

        RS.MoveNext

    Loop

    RS.Close



I have 8 tables to search in this way.  I can improve it with a case

statement, but I'm wondering if there is a better way?  Am I making this

much more difficult than it needs to be?



Thanks!

Sonia