Recordset is null 1

[beckyr51]

Ive seen similar posts on this but none of the solutions seems to work for me. Im trying to test if a recordset is null or not. If it is i don't want another recordset to have a value for STU_TU_CODE else i do want it to have a value. I have a few variations, the latest one is below and has an error "object required"

If rs2 Is Null Then
rs!STU_TU_CODE = " "
Else
rs.Edit
rs!STU_TU_CODE = rs2("TU_CODE")
rs.Update
End If

Any help please?

 

[bill420]

Try

If rs.BOF = True And rs.EOF = True Then
	rs!STU_TU_CODE = " "
Else	
	rs.Edit
        rs!STU_TU_CODE = rs2("TU_CODE")
        rs.Update
End If

Bill

 

[JoeAtWork]

Recordsets are objects, and therefore never Null. What you want to test for is if it was ever initialized:

  If Not(rst Is Nothing) Then
     '....do whatever
  End If

 

[beckyr51]

Ive tried both of those but neither work. Ive tried

If Not (rs2 Is Nothing) Then
rs!STU_TU_CODE = " "
Else
rs.Edit
rs!STU_TU_CODE = rs2("TU_CODE")
rs.Update
End If

But it still highlights rs!STU_TU_CODE = rs2("TU_CODE")
and says no current record. Would it have anything to do with rs2 containing a select top 1 statement?

 

[beckyr51]

Sorry for the above i meant
If Not (rs2 Is Nothing) Then
rs.Edit
rs!STU_TU_CODE = rs2("TU_CODE")
rs.Update
Else
rs!STU_TU_CODE = " "
End If

 

[pbaldy]

I'm with Bill, though I feel you only have to test EOF:

If Not rs.EOF Then


Paul
MS Access MVP 2007/2008

 

[pbaldy]

Looks like I can't edit my post. I meant rs2.

Paul
MS Access MVP 2007/2008

 

[JoeAtWork]

Well, you really have to test for two things:

Dim strTU_CODE As String

strTU_CODE = " "

'Is rs2 initialized?
If Not (rs2 Is Nothing) Then

  'Does it contain any records?
  If Not (rs2.BOF And rs2.EOF) Then
    'Make sure we are on a record
    rs2.MoveFirst
    strTU_CODE = rs2("TU_CODE")
  End If

End If

rs.Edit
rs!STU_TU_CODE = strTU_CODE
rs.Update

Also, you were not calling rs.Edit if assigning the single space character. Assuming this is a DAO recordset, you would need to do that. If it's an ADO recordset, you never need to call the Edit.

 

[TheAceMan1]

How are ya beckyr51 . . .

. . . and this:

[blue]   If rs2.RecordCount <> 0 Then
      rs.Edit
      rs!STU_TU_CODE = rs2!TU_CODE
      rs.Update
   End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[beckyr51]

Thanks everyone for all your help it works now