Copying a Record contain Multi-Value field (1 Viewer)

Given the amount of research and then trial and error, I thought I would post my soloution to what has been a tricky problem. No website gave me the simple answer or at least an answer that would actually run. Anyway here is the background.

One table has (say) 6 separate records all with a same Project Reference field. There is also a multi-value field too.

What I wanted to do was copy the records from one Project Reference to another in the same table. Sounds simple but needed to not only cope with the multi-value field but also change the Project Reference value to the new one.

Here is is:

Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
"WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
"ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)

Set rstTo = dbs.OpenRecordset("Risk Assessment and Area Classification", dbOpenDynaset)

rstFrom.MoveFirst
Do Until rstFrom.EOF

rstTo.AddNew
For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber

If rstFrom.Fields(iFor).Type <> 104 And rstFrom.Fields(iFor).Type <> 109 Then ' types 104 and 109 are Multi-Value field

If rstTo.Fields(iFor).Name = "ProjectRef" Then
rstTo.Fields(iFor).Value = myProjectRef
Else
rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value
End If

Else
Set rstMVFrom = rstFrom.Fields(iFor).Value

If rstMVFrom.RecordCount > 0 Then
Set rstMVTo = rstTo.Fields(iFor).Value

Do While rstMVFrom.EOF = False
rstMVTo.AddNew
rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
rstMVTo.Update
rstMVFrom.MoveNext
Loop
rstMVFrom.Close
rstMVTo.Close
End If ' rstFrom

End If ' iFor
Next iFor

rstTo.Update

rstFrom.MoveNext
Loop ' rstFrom

rst2.Close
Set rst2 = Nothing

Hope this helps someone.

Ted demonstrates one of the good reasons that many developers avoid using multivalue fields. Although they may look like an attractive option at first (if you read Microsoft's promotional blurb) they are much more difficult to work with.

The same process could be achieved by holding the multiple values in a related table data structure and using simple insert (append) queries.

Multivalue fields are also an Access-only feature and present a real impediment to upsizing a database later.

Indeed and thanks for your comments. The only reason I used a Multi-Value field was because the data was usually a simple choice of 2 or 3 items, like High/Medium/Low as as such I did not want to use a subform. Anyway, it was a bit complicated but worked in the end.

ted, thank you soo much for sharing this code...
I have tried all resources and wont/could not make it work transfer data with multiple fields....

!!!

'm still not able to do it
Coud you please attach on database for someone like me [including me begginer] to debug and learn....

I have attachment fields in table which is transfered from form..now i want to move all the records in table1 to table2 in database which is different database without linking these tables....


I'd reall appriciate your help...

ted.martin said:

Given the amount of research and then trial and error, I thought I would post my soloution to what has been a tricky problem. No website gave me the simple answer or at least an answer that would actually run. Anyway here is the background.

Click to expand...

ted.martin said:

One table has (say) 6 separate records all with a same Project Reference field. There is also a multi-value field too.

What I wanted to do was copy the records from one Project Reference to another in the same table. Sounds simple but needed to not only cope with the multi-value field but also change the Project Reference value to the new one.

Here is is:

Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
"WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
"ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)

Set rstTo = dbs.OpenRecordset("Risk Assessment and Area Classification", dbOpenDynaset)

rstFrom.MoveFirst
Do Until rstFrom.EOF

rstTo.AddNew
For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber

If rstFrom.Fields(iFor).Type <> 104 And rstFrom.Fields(iFor).Type <> 109 Then ' types 104 and 109 are Multi-Value field

If rstTo.Fields(iFor).Name = "ProjectRef" Then
rstTo.Fields(iFor).Value = myProjectRef
Else
rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value
End If

Else
Set rstMVFrom = rstFrom.Fields(iFor).Value

If rstMVFrom.RecordCount > 0 Then
Set rstMVTo = rstTo.Fields(iFor).Value

Do While rstMVFrom.EOF = False
rstMVTo.AddNew
rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
rstMVTo.Update
rstMVFrom.MoveNext
Loop
rstMVFrom.Close
rstMVTo.Close
End If ' rstFrom

End If ' iFor
Next iFor

rstTo.Update

rstFrom.MoveNext
Loop ' rstFrom

rst2.Close
Set rst2 = Nothing

Hope this helps someone.

Click to expand...

Thank you so much for this code, it was a big lifesaver, after spending lots of time looking around for a solution.
It did get stuck on my table trying to assign a value to a calculated field. I couldn't figure out what member of Field to check to see if a field is calculated, but I managed to get by with an On Error Resume Next.

Thanks, Ted.

Your code worked beautifully!

Ted, I'm curious, if knowing what you know now, whether you would have gone for a multi value field.

Like Galaxiom, I steer clear of multi value fields. But odds are one day, I'll have a request to work on an existing system with them.