RecordSet Help

[mattc66 via AccessMonster.com]

I need to check my RecordSet if it contains no records I want it to stop. I
created the below IF statement. However it when I run it, it stops on the IF
and I get the message "No Current Record".

What is the proper syntax for checking the RecordSet?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset


Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryOrderEDI")

If rs1![CustID] = Null Then
MsgBox "There are no EDI Records to Upload.", vbOK
Set rs1 = Nothing
DoCmd.Close

Else

 

[Rick Brandt]

I need to check my RecordSet if it contains no records I want it to
stop. I created the below IF statement. However it when I run it, it
stops on the IF and I get the message "No Current Record".

What is the proper syntax for checking the RecordSet?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset


Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryOrderEDI")

If rs1.EOF Then...

If rs1![CustID] = Null Then
MsgBox "There are no EDI Records to Upload.", vbOK
Set rs1 = Nothing
DoCmd.Close

Else

 

[Guest]

Hi Matt,

You can't test for null like that. By defination, Null is not equal to
anything, not even itself.

To test for Null, use the IsNull() function.

If IsNull(rs1![CustID]) Then


or you can use

If IsNull(rs1![CustID]) = True Then


but they are effectively the same.


A better way might be:

If rs1.BOF And rs1.EOF Then

because the only way the recordset can be at the beginning-of-file AND
end-of-file is if there are no records; ie Both .BOF and .EOF are true when
an empty recordset is created

HTH

 

[John Vinson]

I need to check my RecordSet if it contains no records I want it to stop. I
created the below IF statement. However it when I run it, it stops on the IF
and I get the message "No Current Record".

What is the proper syntax for checking the RecordSet?

Dim db As DAO.Database
Dim rs1 As DAO.Recordset


Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryOrderEDI")

If rs1![CustID] = Null Then
MsgBox "There are no EDI Records to Upload.", vbOK
Set rs1 = Nothing
DoCmd.Close

Else

Just another alternative... rather than checking for CustID = Null
(which will NEVER work, since nothing is equal to Null), you can use

If rs1.RecordCOunt = 0 Then
<no records>
Else
<do something else>

The recordcount property will be nonzero if there are any records
(though it may be equal to 1 or to some partial value until you do a
MoveLast, since it won't populate the entire recordset until needed).

John W. Vinson[MVP]

 

[mattc66 via AccessMonster.com]

If rs1.BOF And rs1.EOF Then...

That worked great thanks Matt

Hi Matt,

You can't test for null like that. By defination, Null is not equal to
anything, not even itself.

To test for Null, use the IsNull() function.

If IsNull(rs1![CustID]) Then

or you can use

If IsNull(rs1![CustID]) = True Then

but they are effectively the same.

A better way might be:

If rs1.BOF And rs1.EOF Then

because the only way the recordset can be at the beginning-of-file AND
end-of-file is if there are no records; ie Both .BOF and .EOF are true when
an empty recordset is created

HTH

I need to check my RecordSet if it contains no records I want it to stop. I
created the below IF statement. However it when I run it, it stops on the IF

[quoted text clipped - 16 lines]

Else