[RESOLVED] Find and Replace Macro

[brjames32]

Hi all

I need to be able to loop through all the records in an Access table and replace certain values. I would like to be able to do this in a macro. I have got the following code in a module:
Option Compare Database

Sub ChangeLecturerIds()

DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='JLD' Where [lecturerid]= 'CMR' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='RLM' Where [lecturerid]= 'CS' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='VJH' Where [lecturerid]= 'DF' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='AL' Where [lecturerid]= 'DMJ' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='LN' Where [lecturerid]= 'HOB' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='DKO' Where [lecturerid]= 'MA2ND' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='EJH' Where [lecturerid]= 'MANQT' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='GRJ' Where [lecturerid]= 'NCY' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='SJM' Where [lecturerid]= 'SJD' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='SJR' Where [lecturerid]= 'SJE' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='DMR' Where [lecturerid]= 'TIT' ;"
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='LKM' Where [lecturerid]= 'TOB' ; "
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='LKM' Where [lecturerid]= 'TOB' ;"

End Sub

But it will oly do the first line i.e:
DoCmd.RunSQL "UPDATE Groups SET [lecturerid]='JLD' Where [lecturerid]= 'CMR' ;"

And doesn't get to the next line down. I obviously need to get it to loop through all the values. Anyone know how to do this?

Thanks

[brjames32]

I was in a rush so created a few Queries (12 of the buggers!) and am running them from a macro as follows:
Public Sub UpdateLecturerIds()

DoCmd.SetWarnings False
DoCmd.OpenQuery "JLD"
DoCmd.OpenQuery "RLM"
DoCmd.OpenQuery "VJH"
DoCmd.OpenQuery "AL"
DoCmd.OpenQuery "LN"
DoCmd.OpenQuery "DKO"
DoCmd.OpenQuery "EJH"
DoCmd.OpenQuery "GRJ"
DoCmd.OpenQuery "SJM"
DoCmd.OpenQuery "SJR"
DoCmd.OpenQuery "DMR"
DoCmd.OpenQuery "LKM"
DoCmd.SetWarnings True

End Sub

Cheers for looking anyway!