Report Text Field Dependent on Query

[cosmic debris]

I have two reports: MonitoredLocationInfo and ImpairedWaterInfo.

I want to create a Text Field (RWImpYN) on the MonitoredLocationInfo
form which will display "Receiving Waters Impaired!" if there are
records in a Query (qryRWImp). Otherwise, the Text Field displays ""
(nothing!).

I've tried a few scripts I found through Help in Access; searches on MS'
web site; and Google searches; but none of them seem to work. Is this
possible??

Thanks.

 

[Dennis]

If the records in your query change while your form is open, set the control
source property of your text box to
=IIF(DCount("*","qryRWImp")>0,"Receiving Waters Impaired!","")

If the query does not change once the form is open, then in the OnLoad event
of the form put this code
IF DCount("*","qryRWImp")>0 Then
TextBoxName = "Receiving Waters Impaired!"
Else
TextBoxName = ""
End if

 

[cosmic debris]

Hello Dennis,

I adapted your second method and decided to reword the two messages as
the users would want it stated that there is NO impairment. As this is a
Report, I had to use the On Activate; but its the same difference! This
is really cool!

=== Sample Code ===
Private Sub Report_Activate()
If DCount("*", "qryRWImp") > 0 Then
RWImpYN = "Receiving Waters 303(d) Impaired! (See attached sheet)"
Else
RWImpYN = "No 303(d) Impairment!"
End If
End Sub
=== End Code ===

Thanks