This looks like a bug in Access 2007 / DAO 12

Have tried to run the following but this gives error '3824' INSERY INTO
query cannot contain multi-valued field.

INSERT INTO [;database=C:\Data\DBexchange.accd].MyTable (ID, Field1, Field2,
Field3)
SELECT ID, Field1, Field2, Field3
FROM MyTable
WHERE ID NOT IN (SELECT DISTINCT ID FROM
[;database=C:\Data\DBexchange.accd].MyTable)

So have resorted to using VBA DAO Recordsets

The current code listed below is working fine for all field types except the
fields which are using multi-valued fields.

Am receiving error 'object not available' when doing RS_S("Field1") =
RS_C("Field1")

All four Databases are identical apart from the data.

Table: [Register]
Field: [Field1]
Type: [Text; Lookup ComboBox; Source is Table [Medical Conditions]
Bound to Column 2; Width 0cm,1cm;]

[Medical Conditions] has two fields,
Field 1: [MedCondID] [autonumber] Key
Field 2: [Medical Condition] [Text]

The actual VBA Code part to copy RecordSets is as follows:-

[code]
   Dim dbsOutgoing As Database
   Dim strDBName as String
   strDBName = "Databases/DBexchange"
 
   Dim RS_C As Recordset2
   Dim RS_S As Recordset2
 
   Dim strSQL_C0, strSQL_S1 As String
             
   Dim errLoop As Error
   On Error GoTo Err_Execute
   DoCmd.Hourglass True
   Beep  
 
   If (boolDbIsServer = True) Then GoTo Server_Side

Client_Side:

   strSQL_C0 = "SELECT * FROM [Register] WHERE ([ReportStatus] = '1');"
   Set RS_C = CurrentDb.OpenRecordset(strSQL_C0, , dbOpenDynamic)
   'Open up the Reports DataBase
   Set dbsOutgoing = OpenDatabase(strDBName)
   strSQL_S1 = "SELECT * FROM [Register];"
   Set RS_S = dbsOutgoing.OpenRecordset(strSQL_S1, , dbOpenDynamic)
     
       While Not RS_C.EOF
           RS_S.AddNew
               RS_S("GuardianID") = RS_C("GuardianID")
           'Copies over all fields types ok.
           'Fails on the following.
               RS_S("Field1") =  RS_C("Field1")
           RS_S.Update
               RS_C.Edit
               RS_C("ReportStatus") = "0"
               RS_C.Update
           RS_C.MoveNext
       Wend
       RS_S.Close
       RS_C.Close
     
       DoEvents          
'Continues for the other tables..

Server_Side:

'Identical but RS_C = RS_S..

DoEvents

Both_Continue_FromHere:
   dbsOutgoing.Close
End_Here:
On Error Resume Next
   Set RS1 = Nothing
   Set RSo = Nothing
   Set RS_C = Nothing
   Set RS_S = Nothing
   Set dbsOutgoing = Nothing
   DoCmd.Hourglass False
Exit Sub

Err_Execute:
  ' Notify user of any errors that result from
  ' executing the query.
  If DBEngine.Errors.Count > 0 Then
     For Each errLoop In DBEngine.Errors
        MsgBox "Error number: " & errLoop.Number & vbCr & _
           errLoop.Description
     Next errLoop
  End If
  GoTo End_Here
End Sub
[/code]

Any suggestions would be grateful.