Multi value table field, a real problem for duplicating 1

[lamarw]

Hi Everyone,
MSAccess 2010 db with linked tables. I am trying to resolve an issue with a table. I don't know if it's a table issue or a code issue but I thought I would start here. I recently had a wiz build a macro that would duplicate a record, seemed simple enough. All the columns dup'd except one. After wasting an hour or so, I went to the back end db and, in the table, manually selected the row then ctrl-c, then at the new record row at the bottom of the table, selected the entire row and ctrl-v. This pasted the entire row in with the previously missing column, hmmm. So, to make a long story short I noticed the only thing different about the missing column was it had been changed to a 'multi-value' text field. When I was in the actual table and manually selected the field and then pasted it in a new record it appears to do so at the 'text' (vs value) level. When I use the macro it seems to want to do it at the 'value' level. After some research I stumbled across a single sentence stating that a multi-value field can't be dup'd in a linked table. Okay, so what if I just use the dao method and open the table for read and also for write then select the record I need to dup. and just AddNew back into the table. That worked with the exception of the multi-value field, foiled again!
So, any suggestions? I thought of a stored procedure but after many attempts I just can't figure out how to do it!

Any assistance is greatly appreciated.

Lamar

 

[dhookom]

My suggestion is to never use multi-value fields.

Duane
Hook'D on Access
MS Access MVP

 

[lamarw]

Well, in retrospect, I agree. Any other suggestions?

 

[MajP]

I think you would have to do this in dao or ado. You would have to loop the fields in the record you want to duplicate and write them to the new record. When you get to your multivalue field you need to return a recordset from the multivalue field. The value property of a multivalue field returns a recordset. Then you have to loop the MV field recordset and write those to a fourth recordset which is the MV field recordset.

 

[lamarw]

Thanks MajP. I will look at that.
=============================================================
You said: The value property of a multivalue field returns a recordset. Then you have to loop the MV field recordset and write those to a fourth recordset which is the MV field recordset.
=============================================================
The 'forth recordset which is the MV field recordset' above I assume to be the target field?

 

[MajP]

Actually what I probably would do is a simple insert query to insert a new ("duplicate") record that includes all fields except the MV field. Then use a recordset to loop the multivalue field, and like you said the target recordset would be the MV field within the new record.

 

[lamarw]

Thanks again MajP. Actually, that's what I'm doing. I am at this point because of the MV field. However, I still don't see a way of accessing the 'recordset' for the field. Could you point me in the right direction? (A snip of code would be very much appreciated.)

Lamar

 

[MajP]

http://msdn.microsoft.com/en-us/library/bb258183(v=office.12).aspx

See if this helps. The key line is
Set childRS = rs!AssignedTo.Value
where AssignedTo is the MV field.

 

[lamarw]

Thank you MajP! This is exactly what I was looking for. How did you find it? You deserve this star!

Lamar

 

[lamarw]

Hi MajP,

Many thanks. This is what solved my problem. Would you have done things differently? I'm looking for input.​

Dim db As database
Dim rsParent1 As dao.Recordset
Dim rsParent2 As dao.Recordset
Dim childRSread As dao.Recordset
Dim childRSwrite As dao.Recordset
Dim strSQLread As String
Dim strSQLwrite As String

strSQLread = "SELECT Amenities FROM RoomSetup WHERE ID=" & FromID
strSQLwrite = "SELECT Amenities FROM RoomSetup WHERE ID=" & ToID
Set db = CurrentDb()
 
Set rsParent1 = db.OpenRecordset(strSQLread)
Set rsParent2 = db.OpenRecordset(strSQLwrite)
Set childRSread = rsParent1!Amenities.value
Set childRSwrite = rsParent2!Amenities.value

'Target Parent (Parent2) needs to be in edit mode
rsParent2.Edit
Do Until childRSread.EOF
    childRSread.MoveFirst
    ' Loop through the records in the child recordset.
    Do Until childRSread.EOF
        With childRSwrite
            .AddNew
            !value = childRSread!value.value
            .Update
        End With
        childRSread.MoveNext
    Loop
Loop
rsParent2.Update

Set childRSread = Nothing
Set childRSwrite = Nothing
Set rsParent1 = Nothing
Set rsParent2 = Nothing
Set db = Nothing

 

[MajP]

Would you have done things differently? I'm looking for input.

Maybe. MV fields can be time savers, but they have limitations and you need to understand how they work. They basically allow you to drop a child table and it is done behind the scenes. But this time savings on the front end may require major workarounds later, as you saw. This probably is a lot easier to do if you had a traditional child table to support the MV field because it is just two sql append queries; one for the main and one for the child. I only use MV fields if working with Sharepoint or absolutely sure there will be limited use of these fields.

 

[lamarw]

Okay, excellent comment MajP, I will look into that. I liked the multivalued field because it automatically generates a form too, in this case a list with checkboxes for multiple selection.

Thanks again.

 

[Darrylles]

Following DHookum's 'retrospective' advice above, I'd like to add another:

"NEVER USE MACRO'S - EVER!"

Darrylle ;-)

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.