×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multi value table field, a real problem for duplicating

thread700-1737587

Multi value table field, a real problem for duplicating

Multi value table field, a real problem for duplicating

(OP)
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

RE: Multi value table field, a real problem for duplicating

My suggestion is to never use multi-value fields.

Duane
Hook'D on Access
MS Access MVP

RE: Multi value table field, a real problem for duplicating

(OP)
Well, in retrospect, I agree. Any other suggestions?

RE: Multi value table field, a real problem for duplicating

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.

RE: Multi value table field, a real problem for duplicating

(OP)
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?

RE: Multi value table field, a real problem for duplicating

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.

RE: Multi value table field, a real problem for duplicating

(OP)
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

RE: Multi value table field, a real problem for duplicating

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

Lamar

RE: Multi value table field, a real problem for duplicating

(OP)
Hi MajP,
Many thanks. This is what solved my problem. Would you have done things differently? I'm looking for input.

CODE --> VBA

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

RE: Multi value table field, a real problem for duplicating

Quote:

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.

RE: Multi value table field, a real problem for duplicating

(OP)
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.

RE: Multi value table field, a real problem for duplicating

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

"NEVER USE MACRO'S - EVER!"

Darrylle winky smile

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close