Code to concatenate fields into string fails

[THWatson]

Using Access 2000

The code below is supposed to pull a field called [CreditTo], from a one-field query, [qryMemTest], and put the data together in a string.

Function Concat2(aRSet As String, _
     aField As String) As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strRes As String
    
    strSQL = "SELECT [" & aField & "] FROM [" & aRSet & "]"
    Set dbs = CurrentDb
    [b]Set rst = dbs.OpenRecordset(strSQL)[/b]
    While Not rst.EOF
        strRes = strRes & ", " & rst(aField)
        rst.MoveNext
    Wend
    If strRes <> "" Then
        strRes = Mid$(strRes, 3)
    End If
    Concat2 = strRes
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Say there are 3 records in the query - Jack Apple, Sam Bright and Sarah Candle. I want to put them in a report so that they will appear like this...
Jack Apple, Sam Bright, Sarah Candle

So I put an unbound text box in the Detail section of the report that has the expression...

=Concat2("qryMemTest","CreditTo")

The function always produces an error on the line
Set rst = dbs.OpenRecordset(strSQL)
even though when I put the cursor on strSQL it shows the correct result.

Obviously, I am doing something wrong.

Any suggestions as to how to fix this would be appreciated.

Thanks.

Tom

 

[PHV]

The function always produces an error
Any chance you could post the whole error message ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gol4]

looks like you are reversing the two


=Concat2("CreditTo","qryMemTest")

its looking for fields then recordsource

 

[THWatson]

PHV
The error message is
Run-time error 3061. Too few parameters. Expected 1.

gol4
Nope. Don't think the fields are reversed. I tried changing them but that isn't it.

Tom

 

[PHV]

Seems that qryMemTest is a parametized query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

PHV
Yes, the user inputs the Year through which to search for the appropriate records.

Tom

 

[THWatson]

The parameter in the query is Forms!frmYearSelect!txtYearPicker. But if the report is driven from a form, in which the year is selected, that should bypass the parameter problem.

In any event, no matter what I attempted, I could not get things to concatenate through using the query. I resolved things by turning the query in question into a Make Table query and then using that table in the strSQL.

I still don't understand why it wouldn't work with the query but the run-time error was persistent, and always looking for an additional parameter, even when WHERE clause was added to the concatenation expression.

Tom

 

[ghalewood]

Hi, Should your SELECT statement be surrounded by quotes, so

Set rst = dbs.OpenRecordset(strSQL)
becomes
Set rst = dbs.OpenRecordset(""" & strSQL & """)

I think.

Graham

 

[THWatson]

Graham
Perhaps that would work.

But since the parameter got in the way...and there was going to be an additional parameter...I abandoned using a module completely, and am using a form with list boxes from which the user selects names to concatenate into a string.

Thanks. Appreciate it.

Tom