Access VBA SQL command INSERT for multiple value

Question

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.

Answer 1

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