| | |
|
|
|
How do I list values in a text box.... |
|---|
| message from Dawn Pensiero on 11 May 2004 |
I need to list all values from 1 field in a text box with commas separating
the values. Can anyone help?
Dawn Pensiero
Dallas Texas
|
| =?Utf-8?B?Q29ycmluYQ==?= replied to Dawn Pensiero on 11 May 2004 |
I wrote a function years ago to do this. It is based on the format of the DLookup function. Now that I look at it, I think the arrays were overkill and the function could be simplified quite a bit. But it does work. To use it, copy the function into a module (I put it in a general module not attached to a specific form), then in the control source for the field you want to use it in, enter it like you would a DLookup function (i.e. =ListMultRecords("(field)","(table)","(criteria)")).
Public Function ListMultRecords(strField As String, strDomain As String, strCriteria As String) As String
On Error GoTo Err_ListMultRecords
Dim numRecords As Integer
Dim strPubArray() As String
Dim i As Integer
Dim strPubArrayPrev() As String
Dim iPrev As Integer
Dim strCriteria2 As String
Dim strMultPubs As String
'Define numRecords to count the number of records returned.
numRecords = DCount(strField, strDomain, strCriteria)
If IsNull(numRecords) Or numRecords = 0 Then
'Define strMultPubs as an empty string if there are no records returned.
strMultPubs = ""
'Set the number of rows in strPubArray to one less than numRecords, since an array starts numbering at 0 instead of 1
Else: ReDim strPubArray(numRecords - 1)
'Set first row in strPubArray to the first record returned
strPubArray(0) = DFirst(strField, strDomain, strCriteria)
For i = 1 To numRecords - 1
'Set the number of rows in strPubArrayPrev to one less than numRecords, since an array starts numbering at 0 instead of 1
ReDim strPubArrayPrev(i - 1)
For iPrev = 0 To i - 1
'Set each row in strPubArrayPrev equal to a row in strPubArray
strPubArrayPrev(iPrev) = strPubArray(iPrev)
Next iPrev
'List strPubArrayPrev in a single string to duplicate an SQL criteria
strCriteria2 = strCriteria & " And " & strField & "<>" & "'" & Join(strPubArrayPrev, "' And " & strField & "<>'") & "'"
'For each row in in the records, find the first row that is not equal to a row already listed.
strPubArray(i) = DFirst(strField, strDomain, strCriteria2)
Next i
'List the entries in strPubArray in a single, comma-separated string
strMultPubs = Join(strPubArray, ", ")
End If
'Return strMultPubs as the result of the function ListMultRecords
ListMultRecords = strMultPubs
Exit_ListMultRecords:
Exit Function
Err_ListMultRecords:
MsgBox Err.Description
Resume Exit_ListMultRecords
End Function
|
| Lynn Trapp replied to Dawn Pensiero on 11 May 2004 |
Dawn,
Can you post an example of what you are talking about doing?
|
| Dawn Pensiero replied to Lynn Trapp on 11 May 2004 |
I have a field in a table with values
record1
record2
record3
I want to display all records in the table on a report as "record1, record2,
record3"
"Lynn Trapp" <ltrappNoSpam@ltcomputerdesigns.com> wrote in message
news:%23ye7hg4NEHA.556@tk2msftngp13.phx.gbl...
|
|
Archived message: How do I list values in a text box.... (Microsoft Access Database)
