BOF or EOF is True error

[scheeps]

Not sure if this post needs to go in the Modules & VBA section, but here goes.

This error is driving me crazy and I hope there is someone that will help me out.

I've got a form with a list box displaying few thousand records depending on the search criteria. The user can then double click the desired record and another form will appear where the selected record can be edited.

The double click event looks as follow:

Private Sub lstSearch_DblClick(Cancel As Integer)
 
  Const FN As String = "frmReturn_Header"
  DoCmd.OpenForm FN
  Forms(FN).gotoid Me.lstSearch
  
End Sub

The id is passed to the "gotoid" function by Me.lstSearch to update the frmReturn_Header form with the selected id's detail.

The problem comes in when it hits the function and specifically the "Me.Bookmark = .Bookmark" section of code:

Public Function gotoid(formid As Long) As Boolean
  With Me.RecordsetClone
    .Find "Return_ID = " & formid
      gotoid = True
      Me.Bookmark = .Bookmark
  End With
End Function

I get the Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

I don't get it as I'm the only one working on the db (on my local machine) and the record physically exists in the table. I've checked a few to make sure.

And best of all is that it's only for specific records; the majority of records work 100%.

There must be something in my code I'm missing.

Hope you guys will be able to help as I'm loosing hair by the second with this.

 

[Galaxiom]

Is there any good reason that you are using an ADO recordet for this?
Most developers would used the FindFirst Method of a DAO Recordset.Clone

Also we would usually instantiate an object variable where its Type could be defined and work with that rather than directly with the Recordsetclone itself.

The problem you are encountering is probably due in part to the default Start argument of the ADO Find which is to search from the CurrentRecord. Consequently if the pointer is already past the record you want to Find it will miss it.

Moreover when searching in an ADO recordset it should be preceded by a MoveFirst or MoveLast command before the first instance of Find is used on the recordset.

You have no way of trapping a failure to find a record and the error you encountered will be a normal part of operation. You should be testing for failure to find a match.

ADO that means:
If rsClone.EOF Then

In DAO:
If rsClone.NoMatch Then

Also the way you have returned True is pointless. If you have a return it should be based on the success test.

 

[scheeps]

Is there any good reason that you are using an ADO recordet for this?

No, there is no specific reason why I use an ADO recordset for this. My Access experience is not of such a nature that I can weight up the one against the other, making a call on each's pro's and con's. Someone else on the forum helped me this far and now it's causing me a bit of problems.

I've actually managed to get rid of the "BOF or EOF is True...." error. The code looks as follow:

Public Function GoToID(formid As Long) As Boolean
  With Me.RecordsetClone
    .MoveFirst
    .Find "Return_ID = " & formid
    formid = Not .EOF
    If Not .EOF Then Me.Bookmark = .Bookmark
  End With
End Function

Which works fine, except for a certain selection of records, more specifically if I search for "MED-EGM". The records returned all exist in the table but if I run the search after I've selected and edited another record, and I double click a record it returns the first record in the table. I've got past the error but now this bizarre issue.

Is there no way to refresh the recordset once I return to the search list box?

Can't think what can cause this. The users reported that the "MED-EGM" search criteria is the only search causing the problem. I would understand if you cannot help as this is a very specific issue but maybe you can identify what is wrong here.

 

[Galaxiom]

You are submitting the formid as a Long so it can't be a string.

I assume the formid you see is "MED-EGM" but the bound column in the listbox is a number. Check out what the numeric formid is for that one because that will probably point to the problem.

 

[scheeps]

The formid (Return_id) is a numeric value. The "MED-EGM" forms part of the description of the return but the Return_id is used to drive the search criteria.

I've tested the problematic records which form part of the "MED-EGM" (the sql actually seach for "%MED-EGM%" results and I could not see anything wrong as the records exist in the table just as any of the other records which actually display correctly if double clicked.

I probably need to do more thorough testing.

 

[Galaxiom]

If you would like to post a sample of with only the relevant object, code and a handful of records I would be happy to have look. Sometimes we get too involved in code and miss what jumps out to a fresh set of eyes.

 

[scheeps]

Thanks, that would be awesome.

But as I've prepared this copy I've ran into another problem...for some reason I get "Object doesn't support this property or method." on the following line of code:

.Find "Return_ID = " & formid

I've added the necessary references but that doesn't seem to solve it. Sorry about this, hopefully you'll spot the problem quicker than me.

You can search for "MED-EGM" and double click on any of the records. The corresponding record should open up in the frmReturn_Header form.

I've left the Return_id 49 record in the table and that is the one it displays no matter which "MED-EGM" record you select.

Thanks for your help, it is much appreciated.
Charl

 

[Galaxiom]

Change:
.RecordsetClone to Recordset.Clone
.Find to FindFirst
.EOF to NoMatch

This makes the clone a DAO recordset and the problem is over.

Really though most developers would say it is better practice to use an explicitly dimmed object variable for the clone. It certainly is very subtle relying on the difference between RecordsetClone and Recordset.Clone

The form has a DAO recordset and the DAO and ADO bookmarks are not compatible. I don't know how it worked before.

You can see this mismatch if you Dim a variable as ADODB.Recordset and Set that as the RecordsetClone. The error becomes Type Mismatch which I think is referring to the recordset type mismatch in the function.

 

[scheeps]

Change:
.RecordsetClone to Recordset.Clone
.Find to FindFirst
.EOF to NoMatch

No luck...got the "Object doesn't support this property or method." error on .FindFirst "Return_ID = " & formid.

Do I need to reference the DAO Object Library? I've actually tried that but got the "Error in loading DLL" error.

 

[Galaxiom]

It worked fine for me with the references you had in the sample and this code.

Public Function GoToID(formid As Long) As Boolean
   With Me.Recordset.Clone
      .MoveFirst
      .FindFirst "Return_ID = " & formid
      formid = Not .NoMatch
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
 
End Function

It sounds like you are still getting an ADO recordset for the clone.
Try using a type defined object.

Public Function GoToID(formid As Long) As Boolean
Dim rsClone as DAO.Recordset
Set rsClone = Me.Recordset.Clone
 
   With rsClone
      .MoveFirst
      .FindFirst "Return_ID = " & formid
      formid = Not .NoMatch
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
 
Set rsClone = Nothing
 
End Function

 

[scheeps]

I'm now getting a Type Mismatch error (when using your second set of code); not sure if this error relates to your type mismatch comment earlier.

Can't think why this is working on your side and not on mine. I've actually got my project in an ADP file; I've just sent the sample database in an .accdb format to get the size of the file down.

Do you think that will cause the error?

And I've read that the DAO object library is a 32-bit executable and will not load in 64-bit mode seeing that I'm running Win7 with Access2010 - but you said you've got it working without the DAO reference in any case.

 

[Galaxiom]

I am using Access 2007 on XP Pro. I have never used an ADP file so I don't know anything about them. I didn't actually look at the references until now.

I normally use mdb files and the DAO 3.6 Object library is included by default. However this causes a conflict if added to an accdb. They default to the Microsoft Office 12.0 Access database engine Object Library instead.

 

[scheeps]

Is there no way I can convert the code to a robust version of ADO?

Why is ADO so unstable, if I can put it like that?

I don't think there is a good chance that I'll get DAO working on a Win7 Access 2010 .adp project.

 

[Galaxiom]

The ADP is an issue. They use ADO by default. Access mdb and accdb use a DAO recordset by default.

This would be why you got the problem when you moved the code to an accdb and I didn't get a problem with the DAO but you did.

So it seems you should be using ADO for the clone afterall.

Try declaring rsClone as ADODB.Recordset and change back to Find and EOF.

The other thing I wonder about was:
formid = Not .NoMatch

formid is a Long and noMatch is a Boolean. Usually Access does an implicit conversion to -1 or zero but maybe ADP is different there?

Is this what you actually meant to do? Change the outside value of the ID that is fed to the function. Or did you do a typo where you really just meant to set the return value of the function?

 

[scheeps]

I've made the recommend change and it worked...but yet again only for majority of the Returns. If I search for "MED-EGM", it works up to a point and then again starts to to return Return_id 49 (first record in the recordset) as if it doesn't refresh/reset the recordset after every search.

Someone else on the forum actually helped me with this function. I basically would just like to pass the selected Return_id from the ListBox to the frmReturn_Header form to be edited.

Don't you think there is another way of doing this?

Thanks a lot for you help so far...it's much appreciated.

 

[Galaxiom]

I've made the recommend change and it worked...but yet again only for majority of the Returns.

We are back to the original problem. At least we now know about the recordset difference between ADP and accdb.

If I search for "MED-EGM", it works up to a point and then again starts to to return Return_id 49 (first record in the recordset) as if it doesn't refresh/reset the recordset after every search.

All works fine for me so I am a bit baffled as to what is wrong.

Don't you think there is another way of doing this?

I would use the Where argument of the OpenForm Method to select the return. This doesn't require a filter or clone.

 

[scheeps]

use the Where argument of the OpenForm Method to select the return.

No way!! I can't believe this, everything's working 100% after I've made the change you've recommended. I can kick myself for over-complicating things right from the start.

lagbolt initially helped me creating the function, but even before that I've complicated things for myself...you probably couldn't care less but have a look if you are interested http://www.access-programmers.co.uk/forums/showthread.php?p=1054036#post1054036

I still just found it so bizarre that the function works perfectly for some records but is not robust at all when searching for the MED-EGM records.

Thanks for your help and patience...it's much appreciated!