Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Recordset oddness (2000SP3)

    I had always thought that whenever a recordset object was created the BOF and EOF properties would reflect whether the recordset contained any records without having to try and set a current record. I've just run into a problem where I set a recordset object to the recordsetclone of the form of a subform, and although I can see the records in the subform, both BOF and EOF are true until I try to move to a record in the recordset.

    This behaviour seems very strange. I have a workaround in that I can move to the first record and trap the resultant error if the recordset is empty, but do I have to question all the times I have used checking BOF and EOF to check for an empty recordset? I can think of many examples where it works however, so I'm a bit baffled by this one. I've tried repairing the database, but no effect, and I've rigorously tested to make sure that this really is the situation, and it is.

    Is it something to do with the fact I'm using the recordsetclone of a subform? As I say, I can not only see the records, but once I move to the first record in the recordset, all the data is there, so it's not a matter of the subforms not being up to date.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset oddness (2000SP3)

    I don't have Access 2000, but I have never seen the behavior you describe in either Access 97 or Access 2002 (and just tested again, to make sure). Where do you set the recordset variable?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Recordset oddness (2000SP3)

    I suspect your issue is somehow related to the subform issue - subforms do dynamically alter their recordset based on the parent data, so you may be hitting a situation where the underlying code that does that is in an ambigious state - though I'm surprised that EOF is always true. I would expect it to always be false - though I'm not sure why. If you want to post a snippet of code where this is happening, perhaps someone can spot an issue.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset oddness (2000SP3)

    How are you creating the recordsetclone of the subform? What's the specific code involved?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset oddness (2000SP3)

    Here's the relevant bits in a small db. It's just a form for me to look at data on the same individual from different tables (long story). To see the effect, enter 3 in the ID box (or 5 - both have records in the tables) and with the breakpoint as set, you'll notice that both .BOF and .EOF are true, but, with the code still paused, one can move to the first record in the recordset and access its data.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Recordset oddness (2000SP3)

    Instead of testing for BOF and EOF, try this:

    With rst
    If .RecordCount <> 0 Then
    ...

    That will work with the DAO recordset returned by the recordsetclone method.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset oddness (2000SP3)

    I can't explain why EOF and BOF are both true, but Charlotte has pointed you to the solution: test rst.RecordCount for being non-zero.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Recordset oddness (2000SP3)

    Thanks a lot. I always thought that one had to move to the end of a record set to get a reliable record count. Is that not true of a recordsetclone then? I've always used bof and eof specifically to avoid generating errors when trying to move to a record in an empty recordset, but if I can use recordcount with a recordsetclone without having to move through the recordset, that would be useful.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Recordset oddness (2000SP3)

    When you open a recordset, its RecordCount property will be 0 if there are no records, and some non-zero value if there are records. For many types of recordset, this non-zero value is not accurate. If you need the exact count of records, you need to move to the last record; RecordCount will then be up-to-date. But if you just want to know if the recordset is not empty, a test

    If rst.RecordCount > 0 Then

    is sufficient. In my experience, the RecordCount of a RecordsetClone is usually accurate without moving to the last record first, but your mileage may vary.

Posting Permissions

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