Concatenating a series of rows in a table

[nouveauricheinvestments]

Hi,

I have the following function which I am using to create a string of a
series of values in my table.

Function Concatenate(pstrSQL As String, Optional pstrDelim As String)
As String

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset

Rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Dim strConcat As String
With Rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set Rs = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat


My control record source on my form is the following:

=Concatenate("SELECT Order_Details.TicketNumber FROM Order_Details
WHERE Order_Details.PTID = " & [Description])

Whenever I try to load the form, I get the following runtime error:

No value given for one or more required parameters

Any idea what I'm doing wrong?

 

[Beetle]

Is [Description] a field in your form's recordsource?

If so, is it the PK field and/or is it related to Order_Details.PTID?

Is your form based on the parent table (i.e. Orders) and you are
trying to concantenate records from the child table?

BTW - That is Duane Hookom's code. Perhaps you are not aware, but
if you are going to post someone else' code in a public newsgroup,
you should leave the author's statement intact, or at least give
the author credit. In some cases you may not know where the code
came from. However, in this case, in a recent thread you were
directed
to a website where Duane's example database is located.

 

[Gina Whipp]

NouveauRicheInvestments,

First, let me say it is kinda rude to take out the line that indicates where
the code came from. If you received the code like you might want to stick
those lines back in there! Now, I am noticing the very first line, Function
Concatenate..., is your problem, see below:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ")
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing this statement
is left intact

Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, adOpenKeyset ,
adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & .Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing

If Len(strConcat) > 0 Then
strConcat = Left(strConcat, Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function