[RESOLVED] Run-time error '3021' Either BOF or EOF is true (if statement)

[dgallsop]

I have this code: -

Dim MyConn As ADODB.Connection
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database97.mdb;"
MyConn.Open
Set myrecset = MyConn.Execute("SELECT SUM(total) as total FROM calculations WHERE firstno = " & firstno.Text & " AND secondno = " & secondno.Text & " GROUP BY firstno, secondno")
If IsNull(myrecset.Fields.Item("total")) Then
calculate.Hide
incorrect.Show
MyConn.Close
Else
correct.total.Text = myrecset.Fields.Item("total")
calculate.Hide
correct.Show
MyConn.Close
End If

I am having a problem with the "If IsNull(myrecset.Fields.Item("total")) Then " and the else statement.

If IsNull(myrecset.Fields.Item("total")) Then
calculate.Hide
incorrect.Show
MyConn.Close

The above should hide the calculate form and show the incorrect form IF there is nothing in the database total field.

the else statement: -

Else
correct.total.Text = myrecset.Fields.Item("total")
calculate.Hide
correct.Show
MyConn.Close
End If

Puts the total in the database into 'total' text box and hides calculate form and shows the correct form.

When this is run and the 2 numbers match from the text boxes on calculate form (from the database query) then the correct (else) statement works.

But if you input 2 numbers which arent in the database and therefore have no total, then it displays:-

Run-time error '3021'

Either BOF or EOF is true or the current record has been deleted.

Requested operation requires a current record.

And highlights the else code: correct.total.Text = myrecset.Fields.Item("total")


Any idea whats wrong?

[kaffenils]

Thats because there are no rows matching your WHERE clause, and no rows are therefore returned.

You must also include a IF statement to check rs.EOF=True

[dgallsop]

When there are no rows returned I want it do what is in the 'Then' part of the code, what code should I be using in an if statement for 'if no rows returned' then: -

calculate.Hide
incorrect.Show
MyConn.Close

[kaffenils]

Replace the If IsNull(...) then
with
If rs.eof=true then

[dgallsop]

thanks that works great.

[Hack]

If you consider this resolved, you could help us out by pulling down the Thread Tools menu and clicking the Mark Thread Resolved menu item. That will let everyone know that you have your answer.

Thank you.