Problem with some replace code

[Guest]

I'm trying to weed out Middle names out of first names in a db

Here's the code:
Private Sub Command0_Click()
Dim rstEmployees As ADODB.Recordset
Dim strSQL As String

Set conDatabase = CurrentProject.Connection
strSQL = "SELECT * FROM Contacts WHERE firstname LIKE '% " & txtReplace
& ".'"

Set rstEmployees = New Recordset
rstEmployees.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

With rstEmployees
Do While Not .EOF
!MiddleName = txtReplace & "."
.Update
.MoveNext
Loop
End With

MsgBox "The Middle Initial" & txtNewMinSalary & "has been moved to the
MiddleName Field"

rstEmployees.Close
conDatabase.Close
Set rstEmployees = Nothing
Set conDatabase = Nothing
End Sub

Says "Invalid use of "new" keyword"

Anyone?
--
Chris Hayes
Still a beginner (only 12 years)

[fyi, you can email me by getting rid of the British Comedian''s name who
wrote that song about "Spam"]

 

[Guest]

Hi Chris

Try changing the line:
Dim rstEmployees As ADODB.Recordset
to
Dim rstEmployees As New ADODB.Recordset

and getting rid of the line:
Set rstEmployees = New Recordset

Cheers.

BW

 

[Guest]

Actually...

I changed the code to

Set rstEmployees = New ADODB.Recordset

which solved the problem.

Thanks for your help and your suggestion is obvioulsy shorter code.