A question regarding Access 2010, refresh, requery, and error 2486

[minigendo]

Greetings,



My apologies if this has already been asked, but a search did not turn up anything.

I inherited an access data project to debug which has a peculiar error in an Access 2010 splitform. There is a button which, when pressed, will take users to the blank or new record (the one marked with an asterix), so that they could begin entry of a new record. Users have found that this works the first time they press it, but that if they then enter a new record, and press it again when the data is not filtered, it causes Access problems. When pointed at test data, it merely behaves oddly (which I'll expand on in a moment) but when pointed at live data it causes access to crash without throwing an error. The behavior in test is that rather then being taken to a new record, a record is chosen seemingly at random. The test data and the live data are identical in structure and differ only in their contents. The live data is significantly more populated and thus tends to be slower to respond.

The code that the button executes is as follow:
Me.Refresh
Me.FilterOn = False
DoCmd.GoToRecord , , acNewRec
(Here it calls a function which sets up the form in different ways depending on the status of the record Access is currently looking at)

I put in some debugging output, and walked a user through the steps necessary to trigger the bug in the live data. It happens during the refresh the second time the button is pushed. Looking into the matter, it looked like a requery might be more appropriate, so I changed the code to look like this:
Me.Requery
Me.FilterOn = False
DoCmd.GoToRecord , , acNewRec
(Here it calls a function which sets up the form in different ways depending on the status of the record Access is currently looking at)

This fixed the problem when the application is pointed at the test data. The form no longer jumped to a random record. However, when users tried to use it in the live data, it appears to have crashed at the same spot, except this time it threw an error:
ErrorNumber 2486, You can't carry out this action at the present time. I'm not entirely certain why this is happening. Has any one ever encountered anything similar? Could they recommend any other places to research?

Additional information: Taking a look at what happens when a user creates a new record, essentially when certain fields of a blank record get filled in, and the access app deemed it to be in the state of a new record, a new record is created. The relevant VBA command appears to be DoCmd.RunCommand acCmdSaveRecord. When this runs however, a new blank record is not created. Nor it seems does a refresh create a new blank record, which is probably why in test it jumps to a seemingly random record. I would assume it's jumping to the memory address of the old new record, which is now occupied by something else.

It puzzles me how filtering would interact with this, as users reported they could avoid the problem by applying a filter and then pressing the button in question. As best I can figure out, applying a filter forces a requery or a refresh in a way that generates a new blank record without triggering the Access crashing fault.

My thanks for any help anyone might be able to offer.

[June7]

Think need to view the project to track this down - if you want to provide it.

[minigendo]

Think need to view the project to track this down - if you want to provide it.

My thanks for your response. Unfortunately, I can not provide the project. Do you have any recommendations on places to start researching / things which might cause something similar? Even very general advice would help.

[RuralGuy]

I would start by *importing* your db into a fresh new db and see if the problem follows. http://www.btabdevelopment.com/ts/impnew

[minigendo]

I wpold start by *importing* your db into a fresh new db and see if the problem follows. http://www.btabdevelopment.com/ts/impnew

It's an access data project, and not itself a db. Regardless, I was able to approximate the steps detailed in that link. If corruption has taken place in the forms somehow, hopefully this will help alleviate the symptoms.

On an unrelated note, I was able to get around my original problem before I tried out what you suggested, although I had to employ some truly base hackery to do so.

As I mentioned in the first post, if the data was filtered in between uses of the button, the problem did not occur. Manually triggering what I thought the filter was doing caused the application to crash. So instead I just had it apply a bogus filter.

Essentially:

Me.Refresh
Me.FilterOn = False
DoCmd.GoToRecord , , acNewRec
(Here it calls a function which sets up the form in different ways depending on the status of the record Access is currently looking at)

became:
Dim filterStorage As String
filterStorage = Me.Filter
Me.Filter = "1 = 1"
Me.FilterOn = True
Me.FilterOn = False
Me.Filter = filterStorage
DoCmd.GoToRecord , , acNewRec
(Here it calls a function which sets up the form in different ways depending on the status of the record Access is currently looking at)

Some of this may not be necessary. I could check the value of filter on before messing with it, it might be more efficient to use 1 = 0 (since no records would be returned) etc. Regardless, it seems to have gotten me through the current jam. I don't like it, but I'll take what I can get.

My thanks for your assistance.