CODEWITHSUNDEEP
sqlarraysstringvbams-access
AlphaKevy
AlphaKevy

Reputation: 197

How to Split a text string into multiple table fields

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.

Upvotes: 0

Views: 330

Answers (1)

AlphaKevy
AlphaKevy

Reputation: 197

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

Upvotes: 1

Related Questions