sqlvbams-access-2016

Access VBA SQL command INSERT for multiple value


I need to add values to column in table in VBA Access. But column is number and lookup (from other table) and multiple value

I tried many commands:

 Private Sub buttRec_Click()
   Dim db As DAO.Database
 
   strSQL = "INSERT INTO TABB (BNUM) VAULE (3)"
   Set db = CurrentDb
   db.Execute strSQL
   Set db = Nothing
 End Sub

options for strSQL which I tried:

strSQL = "INSERT INTO TABB (BNUM) VALUES (1), (2), (3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES (1,2,3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES ('1,2,3');"
strSQL = "INSERT INTO TABB (BNUM) VALUES (1;2;3);"
strSQL = "INSERT INTO TABB (BNUM) VALUES ('1;2;3');"
strSQL = "INSERT INTO TABB (BNUM) SELECT '1' UNION SELECT '3'"
strSQL = "INSERT INTO TABB (BNUM) SELECT 1 UNION SELECT 3"

How to do this?
Thanks for help.


Solution

  • Some examples for MultiValue fields.
    INSERT and DELETE

    Test data:
    table TABB with columns Id - primary key, RowName - text,BNUM - multivalued field.

    Insert row to table

    INSERT INTO TABB ( RowName )
    VALUES ("New row 1");
    
    

    Insert new value to multivalued field

    INSERT INTO TABB (BNUM.[Value])
    VALUES (11)
    WHERE TABB.RowName="New row 1";
    

    Insert second value to multivalued field

    INSERT INTO TABB (BNUM.[Value])
    VALUES (22)
    WHERE TABB.RowName="New row 1";
    

    Insert next value to multivalued field

    INSERT INTO TABB (BNUM.[Value])
    VALUES (1)
    WHERE TABB.Id=1;
    

    Values in BNUM mast be unique.

    Insert from subquery

    INSERT INTO TABB ( MField.Value )
    SELECT  MainValue *10 from (select DISTINCT MainValue FROM Head)
    WHERE ((([RowName])="New row 1"));
    

    Multiple value fields operations thru Recordset.
    Examples for Add and Delete operations

    Public Function TestMulti() As String
    Dim rsTab As Recordset
    Dim rsM As Recordset
        Set rsTab = CurrentDb.OpenRecordset("SELECT * FROM tbTestMulti WHERE MultiName='New row 1'", dbOpenDynaset)
        If Not (rsTab.EOF And rsTab.BOF) Then
            rsTab.Edit
            ' access to multivalued field members thru recordset !
            Set rsM = rsTab!MField.Value
    
            With rsM
                .AddNew
                !Value = "New prog value1"
                .Update
                
                .AddNew
                !Value = "New prog value2"
                .Update
            End With
            rsTab.Update
        End If
        TestMulti="Ok"
    End Function
    
    Public Function TestMulti2() As String
    Dim rsTab As Recordset
    Dim rsM As Recordset
        Set rsTab = CurrentDb.OpenRecordset("SELECT * FROM tbTestMulti WHERE MultiName='New row 1'", dbOpenDynaset)
        If Not (rsTab.EOF And rsTab.BOF) Then
            rsTab.Edit
            Set rsM = rsTab!MField.Value
            rsM.FindFirst "Value = 'New prog value1'"
            If Not rsM.NoMatch Then
                With rsM
                    .Delete
                End With
            End If
            rsTab.Update
        End If
        TestMulti2="Ok"
    End Function