INSERT INTO posts array item name not value into table.

[sjb67]

Dear all,

I am trying to update a table with fields from an array. The array works fine, I can test it with msgboxes. However when I try to then insert the array values into a table the array the field names get pasted in instead. My code is below - any thoughts where I have gone wrong anyone?

[

Private Sub cmdSplit_Click()
    Dim MyString, MyArray, Msg
        MyString = Me!txtSearch
        MyArray = Split(MyString, , -1, 1)
                       
    Dim mySQL As String
        mySQL = "insert into tblSearchStock (text1, text2, text3)"
        mySQL = mySQL + "values ( 'MyArray(0)', 'MyArray(1)', 'MyArray(2)')"
        DoCmd.RunSQL mySQL
        
   
End Sub

]
If I remove the ' from the MyArray(0) or use (MyArray(0)) I get an error message: 'Run time error 3085. Undefined function 'MyArray' in expression.

If I change to MyArray.Fields( 0)again the field name is pasted not the field value.

Any help appreciated.
Thanks

[jforbes]

You'll want to concatenate the values of the Array into the SQL String before sending the SQL String off to the DB Engine:

mySQL = mySQL & " values ( '" & MyArray(0) & "', '" & MyArray(1) & "', '" & MyArray(2) & "')"

[NeoPa]

Exactly.

You are working in VBA to create a string (SQL Instruction String) which is then passed on to SQL to interpret. If you bear all that in mind it's much easier to understand what should go where.

[sjb67]

Many thanks JForbes - Fantastic.

I just couldn't get that syntax right!!