Help with first part of IF statement

[bowldog]

The first part of the IF statement is not working but the second and third parts work fine. Below is the code created using Access 2000 but currently using Access 2007.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim db As Database, rst As Recordset
Dim sql As String

Set db = CurrentDb
sql = "select * from [program membershipSSA] where parorn='" & Text7.Text & "'"
Set rst = db.OpenRecordset(sql)

If IsNull(rst(1)) Then
Label0.Caption = "your SSA did not participate in any programs."
Detail.Visible = False
ReportFooter.Visible = False
Else
Detail.Visible = True
ReportFooter.Visible = True
rst.MoveLast
If rst.RecordCount > 1 Then
Label0.Caption = "your SSA participated in the following programs:"
Else
Label0.Caption = "your SSA participated in the following program:"
End If
End If

rst.Close
Set db = Nothing

End Sub

 

[jdraw]

Did the code work in 2000?
Are you saying rst(1) is Null and you're not getting the result you expected?

 

[bowldog]

Yes it worked until we upgraded from Access 2003 to 2007 last year. Yes to the 2nd question.

 

[vbaInet]

Three things:

1. You're running this code in a Report, don't use .Text
2. Instead of using a recordset use either a DLookup() function or even better use a subreport then you can lookup the value returned.
3. You didn't mention if it's throwing an error?

 

[bowldog]

It is running in a subreport. It doesn't print anything if there is no programs but it prints correctly for the other options

 

[vbaInet]

Then create a subreport inside the subreport.