I am working with an Access 2002 .adp that connects to a SQLServer database over a network. There are several of the .adp front-ends on people's workstations and are networked to the SQLServer machine on a small LAN (total of 7 workstations). The application is basically an inventory management and sales tool (for a farmers' cooperative). The application has been working fine for several years and I occasionally do modifications to make the software match new business practices, etc. I made some modifications recently and afterwards this problem started.
There is a form that displays the results of a view from SQLServer in an easy to read format. The form has the Unique Table value set so that the records are updatable, but there is only one field on the form that is left unlocked for the users to modify. This is a checkbox called "Push", which is used so that the sales manager can mark which items the salespeople should be encouraging the customers to buy. It is a two-state checkbox, either on or off.
The problem is that since my latest modifications (which didn't involve making any changes to this Push functionality), every time a user tries to mark an item for Push, the adp will crash and create an auto-backup. Every time.
Unfortunately, this error was not detected immediately, and I have since left the area so I am no longer working for them on-site. I have a copy of their setup on my own computer, but I am using Access 2003, with the SQLServer on the same workstation, and I am not having the problem that they are having. But not being on-site is making it difficult to trouble-shoot.
I can't post the exact code for the VBA Event OnClick (for when the checkbox is clicked) at the moment because I'm on the wrong computer, but I will add it later. However it is very short, just setting a bookmark on the current record and set Me.Dirty = False. Also this code was not modified with my recent changes, it is the same as in several previous version. I tried modifying the code (commenting out pretty much everything) and they reported the same error.
A colleague on-site tested this with a computer that is there and running the latest version on a single computer (ADP and SQLServer on the same workstation - the testing machine that I was working on while I was there) and it's also having the same error, which seems to indicate that the networking is not part of the problem. But I haven't been able to duplicate the error in my own testing environment (which is only different in that I'm using Access2003, as I said).
I have attempted replacing their version of the .adp with the one that is working on my computer, but that didn't help. I have checked that the UniqueTable is set properly, I have checked that the view is returning the data correctly and that the underlying primary table hasn't been modified to remove or change that data field. I'm not sure what else to try, so I'm hoping an expert out there will be able to guide me!
Thanks very much for any and all input.