Populate unbound controls on report using VBA



I stumbled across this newsgroup having a similar problem and wanted
to share my approach. The situation was I needed to conditionally
populate a text box on a detail section of a report with an indicator
(in this case two asterisks), to notate that certain detail records
stood out from others. Specifically, the goal was to examine a
company's country where they did business and provide a roster of
employees. Employees residing outside of the company's home country
of business where to be highlighted on the report. For instance:

Company Name and Location: ABC Company, New York, NY USA

Name Date of Birth City Country
** John M. 3/17/1946 Rome Italy
Joe T. 2/10/1965 Topeka USA

** Indicates member resides outside USA (footer notation)

My solution:
1) Create an unbound text box on the report to hold the asterisks or
hold an empty string. I called this text box txtCountryIndic.

2) Within the detail section of the report, create a second text box
who's control source is set to a function call. The function call
assigned to the text box's control source looked like this:


You can call this 2nd text box whatever you like since its only
purpose is to hold the code in its control source to pass to the
function. I use this indirect method since if I tried to do this in
txtCountryIndic, it would become a bound text box and I wouldn't be
ab;e to assign values to it.

3) Then I wrote a simple function that was:

Function CountryIndicator(strCompany As String, strEmployee as String)
As String

On Error GoTo CountryIndicator_Error

If strCompany = strEmployee Then
[Reports]![REPORTXXX]![txtCountryIndic] = ""
[Reports]![REPORTSXXX]![txtCountryIndic] = "**"
End If

On Error GoTo 0
Exit Function


MsgBox "Error " & Err.Number & " (" & Err.Description & ") in
procedure CountryIndicator of Module UDF"

End Function

Hope someone can get some mileage out of this. I am sure there is a
more elegant way of doing this, but this works for me.

- Dave Ferguson
Washington, DC

Hi Dave,

I'm working on something similar... What if you wanted to call the values of strCompany or strEmployee based upon if one of them were null.

Don't exactly expect an answer through here but figured I'd give it a shot.

Thanks Guys

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question