Unanswered: Concatenate values related query records
I'm using an Allen Brown concatenate related records code from Allen Browne (Microsoft Access tips: Concatenate values from related records). The code works fine for 'tables' in MS Access, but the code fails if I try to run it from a query (even though the fields and data in the query are identical to the table).
If it helps I'll post the code, but my basic questions is: "What's inherently different from running a loop code from a table vs. running the same code from a query?" My only thought is that it takes the query 5-10 seconds to process the data, and this is triggering an error message.
What kind of "fails" are we talking here? He does have a note about some garbage text getting generated if you use it as a source to something else.
I think the problem you're hitting when trying to use this in the source of a query (as opposed to using the results as the source to another query), is the code doing this:
"SELECT " & strField & " FROM " & strTable
If strTable is, in fact, a physical table, then nothing stops you from running the above statement. However, if strTable is a QUERY that is attempting to in turn call "SELECT ... FROM strTable", then you enter in to an infinite recursive loop. This is because your query must call ConcatRelated() to return any results at all. ConcatRelated() REQUIRES the ability to query the underlying DataSource WITHOUT the concatenated field present in order to return a result. When you're doing this from a query, that query does not exist without the concatenated field.