Recordset Anomaly

[HeyMoe]

This is a bit strange, and hoping to get some understanding on what is happening.
I have a very simple bit of code that goes thru a Recodeset
Sometimes (and i mean sometimes) rs!Name returns a NULL, and I get "Invalid use of Null".
But, if I debug the code, and continue it running, rs!Age does not return a NULL.
If I run it again, the point (the iterartion) at which I get the error changes.
Is there something I'm not setting up properly?

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
NumberPeople = rs.RecordCount
'I then have a for loop that that goes thru each record.
For i = 1 To NumberPeople
CurrentName = rs!Name
CurrentAge = rs!Age
......Do something etc.
rs.MoveNext
Next i

Thanks.

 

[GustavBrock]

Try adding the Value property:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

If rs.RecordCount > 0 Then
    rs.MoveFirst
    While Not rs.EOF
        CurrentName = rs!Name.Value
        CurrentAge = rs!Age.Value
        ......Do something etc.
        rs.MoveNext
    Wend
End If
rs.Close

And, of course, study the actual data.

 

[arnelgp]

the question, does your table have Null values on either Name or Age field?
you can cancat then with empty string:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
NumberPeople = rs.RecordCount
'I then have a for loop that that goes thru each record.
For i = 1 To NumberPeople
    CurrentName = rs!Name & ""
    CurrentAge = Val(rs!Age & "")
    ......Do something etc.
    rs.MoveNext
Next i

 

[HeyMoe]

the question, does your table have Null values on either Name or Age field?
you can cancat then with empty string:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
NumberPeople = rs.RecordCount
'I then have a for loop that that goes thru each record.
For i = 1 To NumberPeople
    CurrentName = rs!Name & ""
    CurrentAge = Val(rs!Age & "")
    ......Do something etc.
    rs.MoveNext
Next i

No nulls.

 

[CJ_London]

rs!Age does not return a NULL.

How do you verify that? By hovering over the rs!age in the vba?

and why when you said this?

mean sometimes) rs!Name returns a NULL, and I get "Invalid use of Null".

 

[DHookom]

“Name” is a horrible choice for the name of anything in Access since every table, field, control, object, … has a Name property. Use a name that isn’t a reserved word such as FirstName, LastName, or FullName.

 

[DickyP]

You,re obviously not wrong to use .RecordCount but I would tend to use While Not rs.EOF - does avoid problems (on occasion).

“Name” is a horrible choice for the name of anything in Access since every table, field, control, object, … has a Name property. Use a name that isn’t a reserved word such as FirstName, LastName, or FullName.

If you feel that you have to use "Name" as your field name then put it in square brackets when referring to it: eg, rs![Name.]

 

[Imb]

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
NumberPeople = rs.RecordCount
'I then have a for loop that that goes thru each record.
For i = 1 To NumberPeople
CurrentName = rs!Name
CurrentAge = rs!Age
......Do something etc.
rs.MoveNext
Next i

Thanks.

A rather strange construction: two loops interwound.
You could (better?) use:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do While (Not rs.EOF)
    CurrentName = rs!Name
    CurrentAge = rs!Age
    '......Do something etc.
    rs.MoveNext
Loop

 

[LarryE]

This is a bit strange, and hoping to get some understanding on what is happening.
I have a very simple bit of code that goes thru a Recodeset
Sometimes (and i mean sometimes) rs!Name returns a NULL, and I get "Invalid use of Null".
But, if I debug the code, and continue it running, rs!Age does not return a NULL.
If I run it again, the point (the iterartion) at which I get the error changes.
Is there something I'm not setting up properly?

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
rs.MoveFirst
NumberPeople = rs.RecordCount
'I then have a for loop that that goes thru each record.
For i = 1 To NumberPeople
CurrentName = rs!Name
CurrentAge = rs!Age
......Do something etc.
rs.MoveNext
Next i

Thanks.

Try inserting:
On Error Resume Next
at the beginning of your code

And change that field name Name to something else.

 

[theDBguy]

......Do something etc.

Would that "something" involve maybe deleting a record?

Just curious...