Record not found

[ilanray]

Hi
I have a code that search on a table a record,

set rs=db.OpenRecordset("select fname from employee where id = 5" )

and i would like to use the if statement:
if rs("fname") = "cccc" then....else....

the problem is since my table doesn't have id=5, I get an error message "invalid use of null"
how can I solve it?

 

[Pat Hartman]

You need to check the recordset for EOF

If rs.EOF Then
    ' no record found
Else
    ' record found'
End If

 

[June7]

Test whether recordset has records before referencing field.
If Not rs.EOF Then

Why is the ID static?

If you just want to return a single value, why not use DLookup()?

 

[ilanray]

The id isn't static' this is just an eample
I tried to use the DLOOKUP and I got the same error since the record has not been found

 

[ebs17]

Whenever you filter, you can expect that the filtering will be thorough and will not produce any results, meaning you will get an empty recordset.
Therefore, you have to check as a precaution.

If Not rs.EOF
   if rs("fname") = "cccc" then....else....
End If
'---------------------------------------------------
Dim x As Variant
x = Dlookup(...)
If Not IsNull(x) Then   ' record exists

Addition to the recordset. When a recordset is new opened, the record pointer is automatically set to the first record or to EOF.
With an existing recordset, you don't know this and you have to check for BOF in addition to EOF.

 

[Pat Hartman]

OK, so now THREE posts in a row have told you that you need to check for EOF BEFORE using a recordset.

 

[June7]

Dim x As String
x = Nz(DLookup("fname", "employee", "ID = 5"), "No Name")

 

[jdraw]

ilanray,

First, I agree with the others that checking that there are records in the recordset (eg Not EOF) is fundamental.
Second, do you have any evidence that the recordset has any values? And what those values may be?

You could do a recordcount. It just seems that you may be assuming something and you just need to confirm your data/recordset values.

 

[The_Doc_Man]

if rs("fname") = "cccc" then....else....

the problem is since my table doesn't have id=5, I get an error message "invalid use of null"

It appears that the others are telling you what to check. I'll take a different approach for the explanation.

If the ID in question doesn't exist in your table - and you say it doesn't, the "current record" for your recordset is null. Therefore, rs("fname") is returned as null, which usually puts a crimp in further activity because of how Access treats nulls. However, this is actually correct behavior for Access and OpenRecordset in the absence of methods to prevent returning those nulls.

Now... what the others are telling you (and they ARE right) is that your recordset's RS.EOF property will be true if if your stated operation fails. If you test for that EOF condition BEFORE you look at the fname field then you will know when you cannot make the comparison.

 

[Pat Hartman]

That makes 5 people telling the OP that before you can use the recordset, you need to know if a record exists. I wonder when he will try it.

 

[Gasman]

That makes 5 people telling the OP that before you can use the recordset, you need to know if a record exists. I wonder when he will try it.

You can lead a horse to water......