Using concatenated strings in control source for report

[mnsemple83]

I wrote some code in VBA that takes fields from a query and concatenates their values to create one string that will be used in a text box control for a report. If a field is empty, it is not to be included in the report.

The code was written in the OnCurrent event of the report. I declared a String variable, set it to include the concatenated value, and then set the control source to equal the string variable.



When I view the report in print preview, it does not include the information. The code I had written is similar to this:

Dim strSource As String

strSource = IIf(IsNull([field1]), "", [field1] & ", ") & _
IIf(IsNull([field2]), "", [field2] & ", ") & _
IIf(IsNull([field3]), "", [field3] & ", ") & _
IIf(IsNull([field4]), "", [field4] & ", ") & _
IIf(IsNull([field5]), "", [field5] & ", ") & _
IIf(IsNull([field6]), "", [field6] & ", ") & _
IIf(IsNull([field7]), "", [field7] & ", ") & _
IIf(IsNull([field8]), "", [field8])

Me.TextBox.ControlSource = strSource

Where did I make my error?

[pbaldy]

For starters, I'd use the format event of whatever section the text box is in. Secondly, I think you want to set the Value property, not the ControlSource property. Why not just set the control source in design view and not bother with code? There's nothing dynamic in what you're doing.

[mnsemple83]

I changed my code so that the value is recorded in the Value property instead of the ControlSource property and pasted the code in the On Format property of the Detail section. When I go to view it in Print Preview mode, I get an error message stating "A custom macro in this report has failed to run, and is preventing the report from rendering." Everything is being done in design view.

I was wondering if there was a way inside the query to not include any null fields in the result so when it is being used by the report, they are not included and no blank spaces are present.