How to add lines from textbox to table 1

[btamsgn]

Hi everyone,
- I have a form that contains one button and one textbox
- At present, the textbox included a paragraph and lines
- Now, I want to click on button "add lines" to add lines from the textbox on form to "Sent" column in table "Tsplit".
- Pls find the attachment for your ref.

https://files.engineering.com/getfi...73-c84b-4d88-b61e-df7829f71c48&file=db1.accdb

Looking forwarding to receiving your support.
Thank you.

 

[dhookom]

So, you want to split up the paragraph based on line feed, carriage return and insert them individually into the Sentces field in the Tsplit table? Are you familiar with using (or willing to learn) VBA?

A function that would work with your data is as follows. I might need modification if your sentences contain double quotes.

Public Sub AddPartsToTable(strText As String, strDelimiter As String, strTable As String, strField As String)
    Dim arSplit As Variant
    Dim strSQL As String
    Dim i As Integer             'numbering of the parts
    arSplit = Split(strText, strDelimiter)
    Debug.Print UBound(arSplit)  'how many parts zero based
    For i = 0 To UBound(arSplit)
        strSQL = "INSERT INTO [" & strTable & "] ([" & strField & "] ) Values (""" & arSplit(i) & """)"
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    Next i
    
End Sub

The button code would be:

Private Sub Command2_Click()
    AddPartsToTable Me.Text0, Chr(13) & Chr(10), "tSplit", "Sentces"
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

sentences contain double quotes.

I usually worry about single quotes in the data, so I do:

...Values ('" & Replace(arSplit(i), "'", "''") & "')"

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[btamsgn]

Dear dhookom,
Pls find the attachment for your ref.

https://files.engineering.com/getfi...79-3dfd-4b49-8970-5ff9bf34a8a6&file=db1.accdb

I'd like to output: belong to condition colon position
1. Emma: Where do you think people will live in the future?
Lots of people will live in a space.
2. Mark: I think a lot of people will live in megacities.
3. Emma: Anywhere else?
......
Pls guide and advise how to update?
Thanks for your support.

 

[dhookom]

I'm sorry, but I don't understand. Did you try my suggestion? In your example, are you showing 3 records or 4?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[btamsgn]

Dear dhookom,
Pls find the attachment for your ref.

https://files.engineering.com/getfi...79-3dfd-4b49-8970-5ff9bf34a8a6&file=db1.accdb

I'd like to output: belong to condition colon position
1. Emma: Where do you think people will live in the future?
Lots of people will live in a space.
2. Mark: I think a lot of people will live in megacities.
3. Emma: Anywhere else?
......
Pls guide and advise how to update?
Thanks for your support.

 

[dhookom]

So it looks like if a line doesn't include a colon, it should be included in the previous record. Will there ever be two or more consecutive lines without a colon like:
1. Emma: Where do you think people will live in the future?
Lots of people will live in a space.
There may be people living in inner earth.
2. Mark: I think a lot of people will live in megacities.
3. Emma: Anywhere else?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[dhookom]

Does this work for you? You need to call this sub like I suggested previously. If not, please reply with any error messages or incorrect records/values.

Public Sub AddPartsToTable(strText As String, strDelimiter As String, strTable As String, strField As String)
    Dim arSplit As Variant
    Dim strSQL As String
    Dim strTextNew As String        [highlight #FCE94F]'for building the text to insert as a new record into table _
                                  lines without a colon will be included with the line(s) above[/highlight]
    Dim strTextOld As String
                                  
    Dim i As Integer             [highlight #FCE94F]'numbering of the parts[/highlight]
    arSplit = Split(strText, strDelimiter)
    Debug.Print UBound(arSplit)  [highlight #FCE94F]'how many parts zero based[/highlight]
    
    strTextOld = arSplit(0)
    For i = 1 To UBound(arSplit)
        If InStr(1, arSplit(i), ":") > 0 Then [highlight #FCE94F]'this begins a new record[/highlight]
            strSQL = "INSERT INTO [" & strTable & "] ([" & strField & "] ) Values (""" & strTextOld & """)"
            Debug.Print strSQL
            CurrentDb.Execute strSQL, dbFailOnError
            strTextOld = arSplit(i)
         Else   [highlight #FCE94F]'since no colon, add this to the string to insert[/highlight]
            strTextOld = strTextOld & Chr(13) & Chr(10) & arSplit(i)
        End If
        
    Next i
   [highlight #FCE94F] 'need to insert the final strTextOld[/highlight]
    strSQL = "INSERT INTO [" & strTable & "] ([" & strField & "] ) Values (""" & strTextOld & """)"
            Debug.Print strSQL
            CurrentDb.Execute strSQL, dbFailOnError
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

I usually worry about single quotes in the data

Me too, that's why I just use CHR(34) in place of any quotes.

Thanks!!


Matt