I have a multivalue field (I know they suck, but it is the only way I know how to make life easy for the client) which I need to copy values from one table to another multivalue field in a different table. Basically I am trying to setup a "template" of field choices for the record to be updated with.
I have tried the following code (it is the only way to update a multivalue field I hear):
Private Sub templ_but_Click() 'this is the button that executes the following
Set rstFrom = dbs.OpenRecordset("SELECT [Templates].* FROM [Templates] " & _
"WHERE ((([Templates].ID)= '" & Forms!Proposals!template & "'))" & _
"ORDER BY [Templates].[ID]", dbOpenSnapshot) ' this is the table1 multivalue field data that I want to put into table2
Set rstTo = dbs.OpenRecordset("Proposals", dbOpenDynaset) ' this is the table2 multivalue field table name
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 > 100 Then ' field type 100 or higher are mulitivalue fields
If rstTo.Fields(iFor).Name = "macros" Then ' the name of the muiltivalue field in table1 and table2 is macros with values stored in macros.value
rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value ' copy the values from table1 to table2 here
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
End Sub
But it just gives me errors. I am sure this problem is a bit hard and maybe something that many think is unneeded, but I have no other way to make these "template" recordsets that will make life easy for the client. I have tried an update query, but with the difficulty of using the multivalue fields it has proven beyond my skill-set.
I may just rewrite this whole database in MySQL and PHP... it is what I know best.
Any info on how to do this easier, or even a push in the right direction for some other way of accomplishing the goal of generating "templates" for recordsets would be greatly appreciated.
Thanks in advance!