Question Update Query with multi-value field (1 Viewer)

[g28dman]

I currently have records in a table (tblMPCIPolicyDetail) that need to be updated every year. I have a field called CropYear that I need to change from 2015 to 2016 while still keeping the 2015 records.

When trying to add these new records I get an error that multi value fields can not be used. Which is referencing a PolOptions field. I don't wish to change these in mass by the query just the CropYear value.

What I have done so far....
Copied the tblMPCIPolicyDetail to tblMPCIPolicyDetail_Temp and deleted out everything that I don't want to change yet. I then changed the CropYear to 2016 for this group of records.

I found this using google - https://social.msdn.microsoft.com/F...e-multi-value-field-in-access?forum=accessdev

Substituting my field names came up with this SQL:
INSERT INTO tblMPCIPolicyDetail ( [PolOptions].Value)
SELECT [PolOptions].Value FROM (SELECT [PolOptions].Value, [tblMPCIPolicyID] FROM [tblMPCIPolicyDetail_Temp]) AS T1
WHERE [tblMPCIPolicyDetail].[tblMPCIPolicyID]=T1.[tblMPCIPolicyID];

Now while this worked to some extent it didn't do what I planned. It did change my PolOptions field but changed it to every option that was listed in the query and added those to options to every record in tblPolicyDetail.:banghead:

Any help would be appreciated...

 

[arnelgp]

through vba:

Public Sub sbMultiValueSave(ByVal strSourceTable As String, ByVal strTargetTable As String)


    Dim rsSourceParent As DAO.Recordset2
    Dim rsTargetParent As DAO.Recordset2
    Dim i As Integer
    
    Dim db As DAO.Database
    
    Set db = CurrentDb
    
    Set rsSourceParent = db.OpenRecordset(strSourceTable, dbOpenDynaset)
    Set rsTargetParent = db.OpenRecordset(strTargetTable, dbOpenDynaset)
    
    With rsSourceParent
        If .RecordCount > 0 Then .MoveFirst
        While Not .EOF
            rsTargetParent.AddNew
            For i = 0 To .Fields.Count - 1
                If .Fields(i).IsComplex Then
                    Call AddMultiValue(rsSourceParent, rsTargetParent, .Fields(i).Name)
                Else
                    rsTargetParent.Fields(i) = rsSourceParent.Fields(i)
                End If
            Next
            rsTargetParent.Update
            .MoveNext
        Wend
    End With
    
CleanUp:
    Set rsTargetParent = Nothing
    Set rsSourceParent = Nothing
            
End Sub

Private Sub AddMultiValue(ByRef rsSourceParent As DAO.Recordset2, _
            ByRef rsTargetParent As DAO.Recordset2, _
            ByVal strFieldName As String)

    Dim rsSourceChild As DAO.Recordset2
    Dim rsTargetChild As DAO.Recordset2

    Set rsSourceChild = rsSourceParent.Fields(strFieldName).Value
    
    With rsSourceChild
        
        If .RecordCount > 0 Then .MoveFirst
        
        While Not .EOF
        
            Set rsTargetChild = rsTargetParent.Fields(strFieldName).Value
            
            rsTargetChild.AddNew
            rsTargetChild.Fields(0) = .Fields(0)
            rsTargetChild.Update
            
            .MoveNext
        Wend
    
    End With

    Set rsSourceChild = Nothing
    Set rsTargetChild = Nothing
End Sub

 

[g28dman]

arnelgp, Thank you for your reply.

Sadly though I have no real working knowledge of VBA. I can sometimes piece things together to get what I am looking for but this isn't the case here. I will see what I can do.

Thanks again for the help.

 

[arnelgp]

i understand, but if you google for a solution, it will lead you back to vba 'coz there is no simple Insert query that can do the job.