MS Access: Trying to get ConcatRelated() Function to work.

Question

I'm trying to combine all the rows of Invoice Numbers from a query so they display together as a list in a textbox on a report. I have the ConcatRelated function from Allen Browne's site saved as a Module.

The name of the query to concatenate from is: qryInvNumList with the field column called: FacilityInvoiceNumber.

Next I have a textbox on the report with the following in the expression builder:

=ConcatRelated([qryInvNumList]![FacilityInvoiceNumber],[qryInvNumList],[qryInvNumList]![FacilityInvoiceNumber])

Once I go to the print preview button, I receive a "Enter Parameter Value" prompt box regarding the qryInvNumList. If you click ok or enter a number in the prompt, the textbox on the form shows #Type!

What is causing it to not recognize what is in the query? Any help is greatly appreciated, thanks!

Answer 1

A modified version of the function could be written that would check for and fill in any form/control references in the query.  Give me a few minutes, and I'll post it.

I'm not comfortable posting Allen's code directly -- it's better to refer to his website -- so I'll post instructions for modifying the code posted at http://allenbrowne.com/func-concat.html .

1. Among the Dim statements at the head of the procedure, add these:

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Dim prm As DAO.Parameter

2. Replace this statement:

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

with these:

Set db = DBEngine(0)(0)

Set qdf = db.CreateQueryDef("", strSql)

For Each prm In qdf.Parameters

    prm.value = Eval(prm.Name)

Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)

3. Near the end, where he is cleaning up after the Exit_Handler: statement label, insert these statements:

Set qdf = Nothing

Set db = Nothing

That's it.  I suggest you comment the inserted statements to show that they are not part of Allen Browne's original code.  That way you'll know not to blame him if it doesn't work. <g>

Answer 2

Here is the code from the SQL view for the query:

     SELECT tblFacilityBillDetail.FacilityInvoiceNumber, tblFacilityBillDetail.FacilityName

     FROM tblFacilityBillDetail

     GROUP BY tblFacilityBillDetail.FacilityInvoiceNumber, tblFacilityBillDetail.FacilityName

     HAVING (((tblFacilityBillDetail.FacilityName)=[Forms]![frmFacilityBilling]![Text20]));

Note: the "Having" part is when the user selects one of the facilities from a list on the form. The "Group By" part removes duplicate invoice numbers to display only once.

For your suggestion, do I place   ? ConcateRelated("[Your Field Name Here]", "[Your Table Name Here]") into the module I copied from Allen Browne? (If so, where would it go?)

or

Do I create a new module and place it in there?

(I'm a beginner at VBA/Modules)

Also the field column is correct "FacilityInvoiceNumber"

Answer 3

Can you confirm your query qryInvNumList has the field/column [FacilityInvoiceNumber]? 

You should simplify the function by just calling it with a table and field name. Find a small table and test it.

• Open the debug window (press Ctrl+G)
• Enter something like:

          ? ConcateRelated("[Your Field Name Here]", "[Your Table Name Here]")

Can you provide the SQL view of qryInvNumList?

Answer 4

Tried the suggested: =ConcatRelated("FacilityInvoiceNumber","qryInvNumList")

and received a 3061 error "Too few parameters. Expected 1." (Textbox shows blank)

Tried doing it as =ConcatRelated([qryInvNumList]![FacilityInvoiceNumber],[qryInvNumList"]) and it went back to needing the parameters prompt.

I also had setup a separate query casting the integers into strings and pulling from the new query, but received the same parameters needed prompt.

Is it possible that the ConcatRelated function in the module needs something changed to work with the numbers?

Answer 5

The documentation suggests sending string type arguments to the function. Try:

=ConcatRelated("FacilityInvoiceNumber","qryInvNumList")

This should return every FacilityInvoiceNumber in qryInvNumList. Typically you would want to filter this for a particular facility. Hopefully the query doesn't expect parameters.