CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Macellaria
Macellaria

Reputation: 71

How do I turn several records into textbox in Access report?

I have a simple table that simply has one field in it. Looks like this:

PROJECT
Project1
Project2
Project3

I want to transform this to look like this automatically in a text box in an Access report

Project1, Project2, Project3

This will have to work if there's 3 records, or 5 records, to the nth record.

Upvotes: 0

Views: 156

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Here's a function where you can supply a SQL statement and it will return a comma separated list of whatever is in that field.

Public Function FieldString(ByVal sSql As String) As String

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open GetConnectionString

    Set rs = cn.Execute(sSql)

    FieldString = Replace(rs.GetString, vbCr, ", ")

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

End Function

It doesn't check the SQL statement to make sure it's only one field or that it doesn't return a string that's too long for your control. But it should give you the basics.

It uses GetString and replaces the carriage return with a comma/space combination.

Here's how I tested it with my data in the Immedate Window.

?FieldString("SELECT LocationName FROM Locations WHERE LocationCode > 140")
Lemars, Norfolk, Shenandoah, Harrisonville, DEF Production,

Hmmm, I guess there's a trailing carriage return.

Upvotes: 0

Steve W
Steve W

Reputation: 476

You can loop through the recordset using VBA.

Private Sub cmdProjects_Click()
    'loop through each record, adding the results to the text string
    Dim rs As Recordset
    Dim sql As String

    sql = "SELECT * FROM tblProjects"

    Set rs = CurrentDb.OpenRecordset(sql)

    With rs
        If Not .EOF And Not .BOF Then
            Dim i As Integer
            Dim strOutput As String

            .MoveLast
            .MoveFirst

            For i = 0 To rs.RecordCount - 1
                If i = 0 Then
                    strOutput = !Project
                Else
                    strOutput = strOutput & ", " & !Project
                End If
                .MoveNext
            Next

            Me.txtOutput.Value = strOutput
        End If
    End With

    rs.Close
    Set rs = Nothing
End Sub

Upvotes: 0

Related Questions