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.
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.
Any help would be GREATLY appreciated!
Thanks in Advance.
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.