Copy record with multivalue field to normalize DB (1 Viewer)

[Michael Barkemeyer]

In the attached sample DB I would like to figure out how to replicate the data of any record that has more than one item selected as part of the FirstName multivalue field.
So if the record has three names chosen, I would like to create three new records with each of those names but keeping the rest of the data from the original record in tact.

While I am able to replicate each record (x) amount of times depending on how many names were chosen, I am not able to grab each individual name from the multivalue field and place into the newly created records.

Thank you.

 

[jdraw]

?? I'm not following the requirement, and I did look at the database.

 

[June7]

Open a recordset of the multi-value field and loop through that recordset. https://docs.microsoft.com/en-us/of...bjects/manipulate-multivalued-fields-with-dao

Field names are known, there is no need to loop fields and no need to test for name.

If you want to create multiple records from the multi-value field, what should happen to the original record - delete?

Really should not even have multi-value field in db design. Don't use this as part of user data entry. Just making life more difficult for everyone. Have a single-select combobox and if you want other data carried forward for each following record, use VBA in each control's AfterUpdate event that sets DefaultValue property.

 

[Michael Barkemeyer]

Open a recordset of the multi-value field and loop through that recordset. https://docs.microsoft.com/en-us/of...bjects/manipulate-multivalued-fields-with-dao

Field names are known, there is no need to loop fields and no need to test for name.

If you want to create multiple records from the multi-value field, what should happen to the original record - delete?

Really should not even have multi-value field in db design. Don't use this as part of user data entry. Just making life more difficult for everyone. Have a single-select combobox and if you want other data carried forward for each following record, use VBA in each control's AfterUpdate event that sets DefaultValue property.

Thank you for your information and feedback. I do appreciate it.
Not sure if I am allowed to ask on this thread, but it is as a result of your comments and is the basis of my original post. If I'm not allowed toask, please feel free to ignore.
Do you have a suggestion on how to facilitate data entry structure to replicate what it is I am trying to accomplish as I don't think a single-select combobox will do it.

Scenario:
If time is spent on a task that crosses multiple clients, the idea was to enter the time once, select the multiple clients it impacts and split the time appropriately. So in this case I am selecting one activity, assigning the total time, selecting the 30 or so clients (in the multivalue combobox) it impacts and having the database split those out for me. The alternative as I believe you are suggesting would be to enter the activity 30 times and select the appropriate client each time. Perhaps this will give you a better sense as to why I'm even considering using a mutivalue combobox.
Any suggestions are welcome

 

[Michael Barkemeyer]

?? I'm not following the requirement, and I did look at the database.

Hi, perhaps my response to "June7" may help you better understand what it is I am attempting to accomplish. Thank you

 

[June7]

Nothing wrong with asking but I already gave a suggestion. Use DefaultValue to carry forward data. Whether 30 clicks are made in multi-select combobox or once each in 30 records, still 30 clicks. How long is list? An advantage of single-select is don't have to scroll list to find item, just start typing and combobox will find match, press enter to accept. Code can even advance to next New Record after selection made.

Another alternative is unbound multi-select listbox on unbound form and vba loops through selected items to create individual records. This is a fairly common topic.

First thing need to do is get rid of multi-value field.

 

[isladogs]

@Michael Barkemeyer
As you will know from reading my website article about MVFs, the data is stored in a deep hidden system table to which you have no direct access.
If you have a lot of MVF records, it is likely to be impractical to recover these e.g. by looping through a recordset.
If that is the case, I may be able to recover the data to a standard table which you can then easily convert to normalised data.
However, doing that would be chargeable due to the time required,

If interested, please send me an email or a private message (conversation)

 

[Michael Barkemeyer]

Nothing wrong with asking but I already gave a suggestion. Use DefaultValue to carry forward data. Whether 30 clicks are made in multi-select combobox or once each in 30 records, still 30 clicks. How long is list? An advantage of single-select is don't have to scroll list to find item, just start typing and combobox will find match, press enter to accept. Code can even advance to next New Record after selection made.

Another alternative is unbound multi-select listbox on unbound form and vba loops through selected items to create individual records. This is a fairly common topic.

First thing need to do is get rid of multi-value field.

Thank you again.