Module to loop through fields and print on report?

[gillianleec]

I am trying to print the results of a query in a textbox on a report. There are 99 fields for each record that may or may not have data. The fields are named test_id1, test_id2 and so on through test_id99. I would like to build a Function that would loop through the fields and then concatenate them to a string.

Here is what I have so far but my programming experience is limited...

Function TEST(IntI As Integer)
Dim Line As String

DoCmd.SelectObject acQuery, "qryReport_IDreportable", True
For IntI = 10 To 99
Set Line = ("Test_id" & IntI)
TEST = TEST +", "& qryReport_IDReportable("Line&quot
Next
TEST = Left(TEST, Len(strText) - 2)

End Function

I get a "Can't execute code in design mode" error. Am I anywhere near??

Gillian

 

[MichaelRed]

Some dearth of details on the post, but you MIGHT glean a few hints comparing the following with your posted code:

Public Function basConcatFlds()

    Dim intI As Integer
    Dim strLine As String

    DoCmd.SelectObject acQuery, "qryReport_IDreportable", True

    For intI = 10 To 99

        strLine = ("Test_id(" & intI & ")")
        strLine = strLine & ", " & qryReport_IDReportable.Fields(intI)

    Next

    basConcatFlds = strLine

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[gillianleec]

I am sorry for the lack of info. Let's see if I can explain this more clearly. I have a query with the fields (test_id0, test_id1, ....test_id99). The data is a string (ex. RMSC)

I would like to display these fields in a report in the following format:
RMSC, IOLD, UIOI, OIUK...

Instead of doing a statement naming each field (which I can't do anyways because of textbox character limits), I would like to create a function that concatenates them and then call that function from a textbox on my report. So far, my code is concatenating and displaying the field name instead of the data in that field. I have had to REM out the do.cmd line because on an error saying "You can't carry out this action at the present time". How do I get the data instead of the fieldnames?

Here is the current code that I am working with again. Thanks for the previous suggestions.

Public Function basConcatFlds()

Dim intI As Integer
Dim txtline As String, strLine As String


'DoCmd.SelectObject acQuery, "qryReport_IDreportable"

For intI = 10 To 99

txtline = ("Test_id" & intI)
strLine = strLine + txtline & ", "


Next

basConcatFlds = strLine

End Function

I think my problem is how I am calling my query. when I tried putting the query name in the txtline=query!Name!(...)
I got an Object undefined error.

Gillian

 

[MichaelRed]

Just declare the database and recordset. You should, of course, then check the recordset is not empty, From there, if the recordset is intended / expected to have only a single record. If it is a single record, yoiu should be able to loop through the fields and be done. If it is expected to have multiple records, you would need to loop through each record and place the record results somewhere (another text box?).

In particular, I would not suggest this for multiple records. While I understand the issues involved in placing 90 fields on a form / report, I also believe that it is ultimately an easier and better soloution, I have posted a simple soloution for "Single Line" reports based on an arbitrary recordset in thread705-481652. It would not taks much modification to change it to a multiple lin (per record) and it would (in my opinion) result in a much more readable output.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places