Results 1 to 6 of 6

Thread: Null Recordset

  1. #1
    Join Date
    Dec 2003
    Posts
    268

    Unanswered: Null Recordset

    I am doing a nexted for next loop and am coming across an issue I have not encountered before.

    The second part of the for loop uses the value from the first part of the loop to pull contact information for an organization. My goal is to pull the two contacts for an organization and update the respective orgs information with the contact info. The problem is that not every org has contacts, thus the RS is returning a null value. How can I get it to move to the next value on the outer loop. Following is my code that I am using. My only idea is to do a dlookup for the organization and do a count for the number of contacts, if the count is >0 then add the contact information, else just move to the next record. This will require additional code and I ould like to keep the code to a minimum. Anyone have any ideas, suggestios on this one?

    *******CodeExample********

    Dim db As Database
    Dim rs As Recordset
    Dim i As Integer
    Dim x As Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT Key1 FROM [tblOrgs&Admins]")
    While Not (rs.EOF)
    Set x = db.OpenRecordset("SELECT Contact FROM Sqry240_241QNETAdminsContacts_level1 WHERE Key1 = " & addQuotes(CStr(rs(0))))
    i = 1
    While Not (x.EOF)
    db.Execute ("INSERT INTO [tblOrgs&Admins] (Contact" & i & ") VALUES (" & addQuotes(rs(0).Value) & ")")
    i = i + 1
    x.MoveNext
    Wend
    rs.MoveNext
    Wend

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Null Recordset

    Check the Bof and Eof properties of the recordset. If they are both not true then your recordset has records

    If not (recordset.bof and recordset.eof) then

    'Recordset has records

    Else

    'Recordset has no records

    End If
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Dec 2003
    Posts
    268
    I had thought about that, but it doesnt complet the set command if the value you are trying to set the RS to is null. Any other ideas?

  4. #4
    Join Date
    Jan 2004
    Posts
    184
    How about this?

    Dim rs As Recordset

    set rs = New Recordset

    rs.OpenRecordset("SELECT Key1 FROM [tblOrgs&Admins]")

    Now if there are 0 records the recordset is not Nothing, and eof and bof still work
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Dec 2003
    Posts
    268
    Doesn't work. Doesn't allow me to assign the value to the RS. I am working with ADO not DAO, so I don't know of late binding will work on RSs.

    Any other ideas/suggestions out there?

  6. #6
    Join Date
    Jan 2004
    Posts
    184
    ADO even better.

    Dim rs As Recordset

    set rs = New Recordset

    rs.Open "SELECT Key1 FROM [tblOrgs&Admins]", ConnectionObject
    In abundance of water only the fool is thirsty. Bob Marley.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •