Multi value table field, a real problem for duplicating 1

lamarw · Sep 13, 2014

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 · Sep 13, 2014

My suggestion is to never use multi-value fields.

Duane
Hook'D on Access
MS Access MVP

lamarw · Sep 13, 2014

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

MajP · Sep 14, 2014

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 · Sep 15, 2014

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 · Sep 15, 2014

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 · Sep 15, 2014

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 · Sep 17, 2014

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 · Sep 18, 2014

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

Lamar

lamarw · Sep 18, 2014

Hi MajP,

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

Code:

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 · Sep 23, 2014

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 · Sep 25, 2014

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 · Oct 8, 2014

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.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Multi value table field, a real problem for duplicating 1

lamarw

MIS

dhookom

Programmer

lamarw

MIS

MajP

Technical User

lamarw

MIS

MajP

Technical User

lamarw

MIS

MajP

Technical User

lamarw

MIS

lamarw

MIS

MajP

Technical User

lamarw

MIS

Darrylles

Programmer

Similar threads

Part and Inventory Search

Sponsor