copying data from one table to another

[macsta]

I am trying to copy email data from one table (exchange import) to my main table for each record I am using a system I set up for puting page numbers from the report into a table however for some reason I get the following error:

The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)

<name> being the actural name of the indervidual in the table. i.e "Abbott"

here is the code I am using

Private Sub Detail_Format(Cancel As Integer, FormatCount As _ Integer)

Dim dbs As Database
Dim rst As Recordset
Dim Mylast As String
Dim Myint As String
Dim MyEmail As String
Dim Myrank As String

Mylast = Me!Last

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tb_main1", dbOpenDynaset)

With rst
.MoveLast
.FindFirst "surname = " & Mylast & ""
.Edit
!Email = MyEmail
.Update
End With

please note on the find first line I have also tried "surname = '" & MyLast & "'"

but that came up as a santex error

it might also be worth noting that the code is writing what I want to some of the fields in the tb_main1 how ever it is not writing to all the records only about 50 of 900

and the name in the error is the first name in the table.

thanks for any help you can offer and sugestions of a easier way are greatfully recieved.

Mac

 

[Mile-O]

.FindFirst "[Surname] = '" & MyLast & "'"

 

[macsta]

tried that and got runtime error :

3077 syntex error