Putting A Hard Return In To A Field In Access 1

[DataChick]

I am attempting to create a list of items from a table.

I have one user who has multiple unique records and I want to create one row with the user name and a list of the records.

I have done this in the past putting commas between the unique items (ex. Item1, Item2, Item3...) but what I want to do is create a list like the following

Item1
Item2
Item3

(within the same field)

My ultimate goal is to be able to send an automatic email to the user (using the email address from within the database) and include the list of unique items.

Here is the code I have been using to create the lists with the commas (the combo field is the item plus the comma):

Private Sub Command0_Click()

    Dim db As DAO.database
    
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim varSiteCode, varCombo As Variant
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(">>SourceTable")
    Set rst2 = db.OpenRecordset("<<DestinationTable")
    
    varSiteCode = rst!SiteCode
    varCombo = ""
    
    Do While Not rst.EOF()
    
        If varSiteCode = rst!SiteCode Then
        
            varCombo = varCombo & rst!ComboField
            
        Else
        
            rst2.AddNew
            rst2!SiteCode = varSiteCode
            rst2!ComboField = varCombo
            rst2.Update
            
            varSiteCode = rst!SiteCode
            varCombo = rst!ComboField
            
        End If
        
        rst.MoveNext
        
    Loop
    
    rst.Close
    rst2.Close
    
    MsgBox "Complete", vbInformation, "Status"
        
End Sub

I want to put the hard return within the combo field where the comma used to be.

I don't even know if this is possible.

Thanks in advance for your help.


"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[Savil]

Private Sub Command0_Click()

Dim db As DAO.database

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim varSiteCode, varCombo As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset(">>SourceTable")
Set rst2 = db.OpenRecordset("<<DestinationTable")

varSiteCode = rst!SiteCode
varCombo = ""

Do While Not rst.EOF()

If varSiteCode = rst!SiteCode Then

varCombo = varCombo & rst!ComboField & vbcrlf

Else

rst2.AddNew
rst2!SiteCode = varSiteCode
rst2!ComboField = varCombo & vbcrlf
rst2.Update

varSiteCode = rst!SiteCode
varCombo = rst!ComboField

End If

rst.MoveNext

Loop

rst.Close
rst2.Close

MsgBox "Complete", vbInformation, "Status"

End Sub

 

[DataChick]

Is there a particular reference I have to have selected for this to work? It's not giving me any errors but it's not placing the hard return in (it's still just listing them one after the other).

Thank you for your help!!!

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[DataChick]

FOUND IT!!!

Thank you soooooo much for your help!!!!!!

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein