How to Split a text string into multiple table fields

Question

I would first like to thank you for attempting to help me with my problem. I am attempting to store information entered into a text box into the following fields [ProjectName], [ProjectDate], [ProjectLeader] within my Project table. The textbox information will be separated by comma's. I would like the following text to enter into the appropriate fields "25 May 2015,Wildlife Strategy,John Doe".

Here is the code I have so far:

Private Sub Submit_Click()

Dim textPhrase As String
Dim words() As String
Dim i As Integer
Dim Query As QueryDefs



textPhrase = phrase
words = Split(textPhrase, ",")

SQL = "parameters P1 text;INSERT INTO [Project] (ProjectDate, ProjectName, ProjectLeader) VALUES ([P1])"

Set Query = CurrentDb.CreateQueryDef("FsInsert", SQL)

For i = LBound(words) To UBound(words)
  qdf.Parameters("P1").Value = words(i)
  qdf.Execute
    Next i

CurrentDb.QueryDefs.Delete ("FsInsert")


End Sub

I keep getting the error code, "Number of query values and destination fields are not the same".

Any help is appreciated.

As the error message says, you are declaring 3 columns on insert but only provide 1 value. — Jules
– Jules, Commented Jan 4, 2016 at 23:13

AlphaKevy · Accepted Answer · 2016-01-04 23:33:30Z

1

Private Sub Submit_Click()

    Dim MyConnection As ADODB.Connection
    Set MyConnection = CurrentProject.Connection
    Dim rsDataEntry As ADODB.Recordset
    Set rsDataEntry = New ADODB.Recordset

            Dim words() As String
            Dim i As Integer
            words = Split(phrase, ",")

    rsDataEntry.Open "select * from Project where ProjectName=''", MyConnection, adOpenDynamic, adLockOptimistic
            With rsDataEntry

        .AddNew
        !ProjectDate = words(0)
        !ProjectName = words(1)
        !ProjectLeader = words(2)

        .Update

            End With

    rsDataEntry.Close
    Set rsDataEntry = Nothing

    MyConnection.Close
    Set MyConnection = Nothing
    MsgBox "Done!"
End Sub

answered Jan 4, 2016 at 23:33

AlphaKevy

1972 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jules Over a year ago

another alternative is to use "select * from project where 1=0", just in case there is a blank project name.

Collectives™ on Stack Overflow

How to Split a text string into multiple table fields

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related