Access 2013 VBA Bug - method of object recordset2 failed

[bean68]

Hi,

We have some users who have recently been upgraded to Access 2013 and this has caused a problem with all of our applications on some pretty generic code which allows users to navigate a continuous form with the arrow keys on the keyboard. The code is on the keydown event and is as follows:

Dim LastRec As Long
LastRec = Me.Recordset.RecordCount

If Shift = 0 Then


If KeyCode = vbKeyUp Then
If Me.CurrentRecord > 1 Then Me.Recordset.MovePrevious
KeyCode = 0
ElseIf KeyCode = vbKeyDown Then
If Me.CurrentRecord < LastRec Then Me.Recordset.MoveNext
KeyCode = 0
ElseIf KeyCode = vbKeyF5 Then
Me.Requery
End If
End If

The error is on the movenext method of the recordset and states:

'method movenext of object recordset2 failed.

We have users on 2007 and 2010 Access versions and this code has never been an issue. It is currently affecting 3 different applications we have so any advice would be greatly appreciated! Does anyone know if this is a bug with Access 2013?

Thanks in advance

[Missinglinq]

Have you checked for Missing References on the 2013 installations?

Linq ;0)>

[bean68]

Have you checked for Missing References on the 2013 installations?

Linq ;0)>

Hi Thanks for your response. There are no missing references showing in the 2013 installations, this was also never an issue when upgrading from 2007 to 2010. It could be perhaps that the reference library for this method has been updated in 2013 but is not handled automatically like with most reference libraries (i.e. the Access object library auto updates from 14.0 to 15.0) but even then i would expect some kind of indication of this.

The error is also unpredictable in the sense that it is not happening every time the keydown event is triggered. This is what leads me to believe it may be an issue with Access 2013.

[Missinglinq]

The problem, of course, is that so few experienced developers have moved up to 2013, as yet. The few questions I've seen here, and on the other 4-5 forums I frequent, are almost exclusively from newbies to Access who are getting started with '13, and have actually seen no bug reports, to date.

Access' buginess, in general, is why I always stay at least one version back, and sometimes two!

Good luck with running it down!

Linq ;0)>

[marshallarts]

I just came across this thread after bumping into this exact same error with a client who has upgraded a laptop to Access 2013. I'm doing a very similar thing - i.e. allowing the user to move up/down on a continuous form with the arrow keys. I developed the app in Access 2010 and I cannot reproduce the error on my machine. I know this is a pretty cold thread but I thought I'd ask - did anyone ever find the solution/fix to the problem?

Thanks and regards
Steve

[ItsMe]

I have no experience with the error, but I would use a line a code to declare the recordset as DAO.Recordset And another line to set me.recordsetclone.

The error message seems that access is assuming Recordset2 which does not have the same methods available as recordset.

[marshallarts]

Many thanks for the reply. Good thought - my code is not identical to the above, but aims for the same end result. Like the above, I do not declare a recordset and SET it to the the form's recordset, but will definitely try that. One thing that has been a bit unclear to me is the difference between the Recordset and RecordsetClone properties of the form. I've been using the Recordset property, but now think it should be the RecordsetClone property, as your reply suggests. I just found some info that explains them a bit, so it is a bit clearer - it seems they are almost interchangeable, but not quite! :-(

[ItsMe]

So something like

Dim rs as DAO.Recordset
set rs = Me.Recordsetclone

You just copied the current form's recordset into rs

This will include any filters in the current form and will not require a trip to the tables/data.

Sometimes you need to take a trip to the data because you want your recordset to be unique. Then use a table or SQL to open a new Recordset

Something like

Set rs = CurrentDB.OpenRecordset("TableNameOrSQL", dbOpenDynaset)

[marshallarts]

For what it's worth - I made a very small change to my code as suggested (almost) - instead of Me.Recordset.Movenext, I assigned the Recordset property of the form to a variable declared as DAO.Recordset, and executed the MoveNext on that. It seems to have worked - I have just tried it on the client's computer, and in a lot of arrowing up and down the continuous forms did not see the error pop up. From what I saw previously, it would definitely have occurred in that many keystrokes if it was going to. Interestingly, if I used the RecordsetClone property of the form instead, it didn't work at all: every time I tried to use the arrow key to move up or down a record, I got another error - something like "Update without Addnew or Edit". So I stuck with the Recordset property, and I'm hopeful that this has nailed it. Many thanks ItsMe for the input, which sent me in the right direction.

[ItsMe]

Without seeing the code and or understanding what you are doing (trying to accomplish) it is hard to say why you are getting the error. The error is associated with trying to edit a record via code without first doing something like

rs.Edit
or
rs.AddNew

Where rs is the instantiated Recordset Object.

[marshallarts]

Thanks, but given that I seem to have found a solution I don't really need to pursue this further. I only posted the previous message because I was very surprised at the major difference in behaviour between using the Recordset and RecordsetClone properties of the form. For what it's worth, there is actually NO code in the form which updates records directly, only indirectly - when a Quantity is entered (see below), the line item total is calculated and inserted into the appropriate form field. I do this sort of thing in lots of other apps and Access handles it fine. The only other code in the form is for handling the up-arrow and down-arrow keystrokes.

If you'd like the bigger picture, the continuous forms contain a list of product items which can be added to an invoice. The form is bound to a query which joins the InvoiceItem table and the Product table.
When an Invoice is started, the system creates InvoiceItem records for every possible Product, then allows the user to zip through and enter the quantities for the items being ordered. When they finalise the invoice, the items with no quantity get discarded. This may be a slightly unusual approach to this commonly-programmed task, but it is what the user wanted. The user can only edit the Quantity field, and the idea is that (for speed) they can just enter a quantity (or not) then press the Up or Down arrow key, instead of having to go to the mouse or press a lot of Tab keys to get down to the next (or up to the previous) record. Only the Quantity field has Tabstop=true, so the cursor stays in that field and it's all quite fast and easy - once it works! Seems a fairly obvious thing to want to do, but surprisingly, not entirely straightforward to achieve.

[marshallarts]

I was wrong - the previous post suggested that I thought I'd found the way around this, but it appears not. The client reported that Access was not just giving a run-time error, but totally crashing in this form. I went there and saw it with my own eyes!

So it was back to square one (or even further), but I may have found another way around this. If there is no compelling reason to use Continuous Forms view, you can change it to Datasheet view. I thought I'd try that, and found that Access behaves MUCH better. In fact there is no need for any code at all to handle the arrow keys, because Access does it all, beautifully. The only issues you might have are in setting column widths and ordering - it's not entirely straightforward, but not too difficult. So for me, Datasheet View looks to be a much better solution, and so far is working fine.