insert CRLF in table field being populated by vb

ChristineMD

Board Regular
Joined
Jul 29, 2008
Messages
53
I have a little piece of VB code that populates a table with some customer data: email, name, phone, and order details

There might be multiple records of order details for each email address, so I'm combining those all into the detail field, so there is one record per email address

Ultimately this data will be used as a feed for a word email merge. All I want to do is add a line feed in between the details in the detail field.

this first piece of code works fine, I have all the detail records for customers with multiple details, mashed into the "ln" field.

Code:
Public Function mbo() As Boolean
    On Error Resume Next
 
    Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
    Dim strem As String, strnm As String, strph As String, strln As String
 
    Set db = CurrentDb()
    'Call RecreateTables(db)
    sSQL = "DELETE FROM tMBOs"
    db.Execute sSQL
 
    sSQL = "SELECT em, nm, ph, ln FROM ManualBOs ORDER BY em ASC"
    Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
    If Not rst.BOF And Not rst.EOF Then
      rst.MoveFirst
      strem = rst!em
      strnm = rst!nm
      strph = rst!ph
      strln = rst!ln
 
      rst.MoveNext
      Do Until rst.EOF
        If strem = rst!em Then
          strln = strln & rst!ln
 
        Else
          sSQL = "INSERT INTO tMBOs (em, nm, ph, ln) VALUES('" & strem & "','" & strnm & "','" & strph & "','" & strln & "')"
          db.Execute sSQL
            strem = rst!em
            strnm = rst!nm
            strph = rst!ph
            strln = rst!ln
        End If
        rst.MoveNext
      Loop
 
      ' Insert Last Record
      sSQL = "INSERT INTO tMBOs (em, nm, ph, ln) VALUES('" & strem & "','" & strnm & "','" & strph & "','" & strln & "')"
      db.Execute sSQL
    End If
 
    Set rst = Nothing
    Set db = Nothing
End Function

This however does not update the database at all! No records, not even a partial record. I also tried the CHR(13)+CHR(10) and just CHR(10) alone but also got nothing. Seems like it s/b easy enough!! Below is snip of only the area that changed.

Code:
...
        Else
          sSQL = "INSERT INTO tMBOEXP (em, nm, ph, ln) VALUES('" & strem & "','" & strnm & "','" & strph & "','" & strln & "','" & vbCrLf & "')"
          db.Execute sSQL
            strem = rst!em
            strnm = rst!nm
            strph = rst!ph
            strln = rst!ln
        End If
        rst.MoveNext
      Loop
 
      ' Insert Last Record
      sSQL = "INSERT INTO tMBOEXP (em, nm, ph, ln) VALUES('" & strem & "','" & strnm & "','" & strph & "','" & strln & "','" & vbCrLf & "')"
      db.Execute sSQL
    End If
...


Any help would be GREATLY appreciated!

Thanks in Advance.
 
Hi, the problem is with your Values clause.
You're trying to insert the crlf into a non existing field. And you don't receive any error message because you've set on error resume next.

From what I understand from reading you code, you need to add the linefeed to the LN value, not trying to insert it as a field.

something like:

Code:
strln = rst!ln & vbCrLf
 
Upvote 0
I *JUST* figured this out myself. This is a piece of code someone else wrote that I have modified a number of times to suit different purposes.

I came to see if I could delete this thread before anyone spent any time on it.

I do appreciate your response. It's nice to know I came up with the right answer, too. :cool:

Thanks again.


Hi, the problem is with your Values clause.
You're trying to insert the crlf into a non existing field. And you don't receive any error message because you've set on error resume next.

From what I understand from reading you code, you need to add the linefeed to the LN value, not trying to insert it as a field.

something like:

Code:
strln = rst!ln & vbCrLf
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top