First part of If statement not working

[bowldog]

Here is the code
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

 

[SkipVought]

hi,

use faq707-4594

Put in a BREAK.

What is the VALUE of rst(1)?

What is the value of IsNull(rst(1))?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hmlhml]

I would do this differently; I would test:
if rst.RecordCount = 0 then...
Note that I'm using a DAO recordset because that's all I know about, but whatever sort of recordset you are using, it's a good idea to dim expressly: dim rst as DAO.recordset.

 

[Darrylles]

Hi bowl,

I check for an empty record set with:
[tt]
if isnull(rst)
[/tt]
Is your [tt]rst(1)[/tt] checking for the value of a field within the recordset?
If so, is this an 'Option Base' issue? E.g. are you intending to check the first (0) field or the second (1) field?

Just an idea.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

bowl,

I believe that your rst(1) is the problem. If you want to know if a record set has a record, then check it for NULL or (as Ace has done) BOF.

As ACE has delivered a partial solution, here's the complete one:

[tt]
Dim db As DAO.Database, rst As DAO.Recordset, SQL As String

Set db = CurrentDb
SQL = "SELECT * " & _
"FROM [program membershipSSA] " & _
"WHERE parorn='" & Text7.Text & "';"
Set rst = db.OpenRecordset(SQL)

If rst.BOF Then 'No Records!
Label0.Caption = "your SSA did not participate in any programs."
Detail.Visible = False
ReportFooter.Visible = False
Else 'At least ONE record available...
Detail.Visible = True
ReportFooter.Visible = True

'Check for more than one record...
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:"
Endif
End If

Set rst = Nothing
Set db = Nothing
[/tt]

Some pointers (you already use object type prefixes - but not consistently).

lbl - labels (lblNAME instead of Label0)
str - string (strSQL instead of SQL)
rst - rstDESCRIPTION instead of rst)
dba - dbaDatabaseNAME (not db)
tbl - tblProgram_membershipSSA (never use spaces in object names)

ATB,

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

bowl,

And the error was....?

Have you changed some library references since you logged this issue?

ATB

Darrylles

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[bowldog]

Stops at Set

Compile error:
Invalid outside procedure

I am using Access 2007 and pulling from a query

 

[TheAceMan1]

bowldog . . .

Your missing the [purple]Microsoft DAO 3.6 Object Library[/purple]. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

bowldog . . .

Anyway ... there are several ways to reference a field in a VBA ecordset. Here are a few with the one you used hilited:

[blue]VariableName = rst!FieldName    [green]'standard no spaces in fieldname[/green]
VariableName = rst![Field Name] [green]'standard with spaces in fieldname[/green]

VariableName = rst("FieldName")
VariableName = rst(StringVariable) 
[purple][b]VariableName = rst(column index)[/b][/purple] [green]'columns are zero based[/green]

VariableName = rst.Fields("FieldName") 
VariableName = rst.Fields(column index) [green]'columns are zero based[/green][/blue]

So when you used ...

[blue]   If IsNull([purple][b]rst(1)[/b][/purple]) Then[/blue]

... your referencing the 2nd field in the recordset. Also note: when you 1st open a recordset the record pointer rests on the 1st record.
[ol][li]If there are no records the comparison will fail.[/li]
[li]If there are records and the 2nd field has data the comparison will fail.[/li]
[li]If there are records and the 2nd field is [blue]Null[/blue] the comparison will [blue]pass[/blue].[/li][/ol]
So the logic is just all wrong.

When you 1st open a recordset (and this is key ... 1st open) if there are no records both [blue]BOF[/blue] & [blue]EOF[/blue] are set to true. So it doesn't matter if you test for both or one.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SkipVought]

I usually

1) open the recordset

2) On error resume next

3) MoveFirst

4)
if err.number = 0 then
'have a recordset so proceed to process
else
'have NO recordset so what to do here???
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

TheAceMan1, you are referring to the default property of the object, I try to explicitly state:

VariableName = rst!FieldName[red].Value[/red]
VariableName = rst.Fields("FieldName")[red].Value[/red]
etc

Have fun.

---- Andy

 

[TheAceMan1]

Andrzejek . . .

Without the dot as I've shown, the [blue]Value[/blue] property is automatically implied. With a page full of code it does make reading easier if its dropped. I see it as a [blue]style[/blue] option. You can use it or not. Either way, which ever you use, there's no operational difference.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[bowldog]

Thanks Aceman

I moved the DAO 3.6 Library as high as it would go (3rd on the list) and still getting the error message.

 

[TheAceMan1]

bowldog . . .

Hmmmm. Post the code as you have it now.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[bowldog]

Compile Error - invalid outside procedure (Set is highlighted)

Dim db As DAO.Database, rst As DAO.Recordset, SQL As String

Set db = CurrentDb
SQL = "SELECT * " & _
"FROM [program membershipSSA] " & _
"WHERE parorn='" & Text7.Text & "';"
Set rst = db.OpenRecordset(SQL)

If rst.BOF Then 'No Records!
Label0.Caption = "your SSA did not participate in any programs."
Detail.Visible = False
ReportFooter.Visible = False
Else 'At least ONE record available...
Detail.Visible = True
ReportFooter.Visible = True

'Check for more than one record...
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

Set rst = Nothing
Set db = Nothing

 

[PHV]

In which procedure is this code supposed to be ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886