Is there anything wrong with this code?

[bowldog]

Upgraded from Access 2000 to Access 2003 and now my subreport is not working correctly. Any thoughts would be appreciated. The problem that is when there are no records for that group it's not showing anything when it should be showing "your SSA did not participate in any.....". The other options work fine. Thanks in advance for your cooperation.

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 TASB 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 TASB or TASB related entity programs:"
Else
Label0.Caption = "your SSA participated in the following TASB or TASB related entity program:"
End If
End If

rst.Close
Set db = Nothing

End Sub

 

[dhookom]

What are you attempting to do with the recordset? I would not use "SELECT *..." and then assume the second field might be a specific field. I think you could replace all the recordset code with a single line using a domain aggregate function like:

If DCount("*","[Program MembershipSSA]","PARORN='" & Me.Text7 & "'") = 0 Then

I didn't think you could use the "Text" property of a control in reports. In forms, the Text property is only valid if the control has the focus which is not possible in a report.

If the recordset you are returning is the same as the report's recordset, you might be able to use

  If Me.HasData = False Then

Duane
Hook'D on Access
MS Access MVP