Error 2137 ocurring in new version (1 Viewer)

Hello guys.

I spend all morning and a bit of the afternoon of Friday, trying to solve a error in my database. The problem is now "solved", but I wanted to share it with you.
I made a database for my company register their clients purchases. Version 4.1.0 was installed in several computers and working. I made a few changes to the program, and created version 4.1.1. In my computer, when I was developing, I was getting error 2137 when I clicked a listview item to go to a record. The code is:

ID_OBRAS.SetFocus
DoCmd.FindRecord Item.Tag, , , acSearchAll, , , True

To solve this error, I simply clicked my edit button, and when the error occurred again, I clicked the save button... It was stupid, but it worked and I don't know why, but I thought than in the other computers, working in runtime, this wouldn't happen! But it did! So, I did plenty of tests, compare the two versions, search the web and found in this forum someone whith the same problem, who put a requery before the DoCmd.FindRecord line. Instead of the requery, I put Refresh and it worked. No more of error 2137... but my question is why? Why one version runs ok, and the other don’t if the properties and options are equal?

I added a reference to Excel and code to copy, open and write in a excel file. Could this be what’s causing the error? It has nothing to do, but with access we never know...

If any of you want to think about this, I will appreciate! Thanks!

I can't speak for your problem in particular but I can, for the general process of finding errors. I have just now, spent a good few hours trying to track down an error! I have a VBA routine that adds a booking to the booking table. There's an index on it to prevent a customer booking the same thing twice, so when it wouldn't update the table, with VBA code, with an insert into statement, I immediately suspected the indexes. Removing the indexes didn't help. So then I suspected an entry in the table that matched the new insert that might be interfering with the insert. I made it back up of the table and deleted all the records, that didn't work... I suspected a bug in the database and I did a compile, and for good measure, copied everything into a new virgin database. That didn't work! I copied the form and the table alone into a blank database and it worked.

What was the difference? There were no tables other than the Booking table in the testing database. That's when I realised it was a relationship between the tables that was causing the problem!

This is one reason I don't like establishing relationships too early in the construction of database, particularly when you are dealing with somebody else's data, which may not have the correct defaults set for particular fields. This experience has reinforced my disdain of relationships & referential integrity!


The process for tracking down a bug.

Have a theory, and test it. (It's no good having a theory, like "there's a bug in my database" unless you can test it)

In my case it wouldn't insert, theory:- Something is blocking the insert process.

Remove all the possible blocking processes you know about, the indexes, and then for good measure, the records.

When you've exhausted all of your theory's, then just get the process to work somehow. This usually leads to the Ah! moment when you realise what's causing the issue. But if you still can't see it, add everything back in to the sample database in stages. The code, tables, forms, everything - add it back in individually, gradually Until It Breaks again...

Keep doing this until you can reproduce the error.

Then you will either know what it is, but if you don't, at least you have a good question to ask on the forums...

Thanks! I will do as you suggested!