Preserving String variable formatting in text box

[Monterey_Manzer]

Hello All,

I am having some difficulty with a text box in a form I am work on and I would appreciate any help anyone feels like offering. I am hopeful this will remain a fairly simple problem and won't require a total reworking of my approach. First, a little back story.

The Situation:
My office is tasked with maintaining data files in an online database that is used for reporting to program stakeholders. One of the tasks we are integrating into our regular processes is a regular audit of randomly selected records. Since I maintain a locally hosted Access dBase that uses these same records for tracking in house administrative tasks, I am in the process of making a form that will allow users to generate a list of random-ish selected records. This is done with the following code.

First I have a function that uses a DAO recordset and randomly generated number to select records.

Function RandRec(Num) As String
    Dim A As Integer, db As DAO.Database, rst As DAO.Recordset, strSQL As String, PartList As String, i As Integer, RecMax As Integer
    On Error GoTo ErrHandler
    
    strSQL = "SELECT [Participant Name], [Smart Id] FROM PartInfo"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveLast
    RecMax = rst.RecordCount ' Getting the total number of records for use with the random number generator
    rst.MoveFirst
    Do Until i = Num
        i = i + 1 ' counter that loops through a set number of times, allows the user to select how many records they want
        A = Int((RecMax - 1 + 1) * Rnd + 1) ' generates the random integer used to select record
        rst.Move (A)
        PartList = PartList & vbCr & vbCr & rst![Smart Id] & Chr(9) & rst![Participant Name]
        rst.MoveFirst
    Loop
    RandRec = PartList
    Exit Function
ErrHandler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error Occured"
    Call ErrorEmail(Err.Number, Err.Description, "Function", "RandomRecords")
End Function

This function is then called from the audit form, where the user selects the number of records they wish to audit. This code is shown below.

rivate Sub btnAudit_Click()
Dim RecCount As Integer, MyAudit As String
    On Error GoTo ErrHandler
    ' Getting the form name for the ErrorEmail function
    Object = Me.Label1.Caption
    
    ' Setting the number of records to be audited in SIMS
    RecCount = Me.txtNumRec.Value
    
    ' Calling the RandRec Function to generate a list of randomly selected participants
    MyAudit = RandRec(RecCount)
    Debug.Print MyAudit
    
    Me.txtRecToAudit.SetFocus
    Me.txtRecToAudit.Text = MyAudit
    Exit Sub
ErrHandler:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "Error Occurred"
    Call ErrorEmail(Err.Number, Err.Description, Object, "txtNumRec_AfterUpdate")
End Sub

The Problem:


When I print the variable MyAudit to the Immediate window I get a nice, well organized list of records to examine. However, when I set txtRecToAudt.Text (or .Value, doesn't matter) equal to the string MyAudit, I lose all the formatting (spaces, tabs, and carriage returns). I just want the text box to look like my Immediate window. I am really happy with how everything else is working. The output looks great until I try to put it in the textbox on my form. Do I have to break up the MyAudit string and put it together again with the Chr(9) and vbCr in there again in order to get the text to show correctly or is there a simple formatting trick to make the textbox show the records in the way I want?

Thanks!
Ryan

[pbaldy]

Untested shot in the dark, but try vbCrLf instead of vbCr.

[Monterey_Manzer]

Thanks Paul! That worked well for the carriage returns, now I just have to figure out the spaces between the ID# and the Name. It's not recognizing the Chr(9) as a Tab. Any thoughts there?

[pbaldy]

Does vbTab work?

[Monterey_Manzer]

Nope, not recognizing that one either.

[Monterey_Manzer]

Alright, well it's not a perfect solution and doesn't look as clean as I would like, but I've just swapped the Chr(9) for another vbCrLf so that each record I want to audit shows up on two consecutive lines. If I figure out how to get the tab to work inside the unbound textbox, I will post that solution here.