Unanswered: Left() Trim() etc not working References OK
I know you are all probably tired of hearing about this one, but we have tried all the reference things that have been posted and we are still stumped.
We are running Access 2000. We have a master Frontend/Backend on the server, with the Frontend being copied to individual PC's for the users, (linked to the backend on the server). These databases have worked for years. Recently, when we started deploying new PC's, we started getting an error on one form "Runtime error '3059' Operation Canceled by User". This only happens on a few machines and is running fine on others. The first time you open the form it runs fine. We have a double click option that will take you to another form with more detail. When you double-click the first time it works fine. If you close that form and come back to the first form, double-click again, we get the above runtime error. (We have a requery in the onactiviate to capture any new records that may have been added on the second form).
As we began to troubleshoot, it seemed to come down to a query that is part of the input for the form that includes Left(), Mid(), Right() functions.
Everything we've read about this says it's a reference problem. We have checked all the references, none are missing, we've added, deleted references, we have tried to reregister with regsvr02.exe. I downloaded a wzref reference check and they all seem to be fine. We have checked file locations, versions, dates. We have found no logical answer.
We cannot think of anything different we are doing in deploying new PC's. The process is the same and the files used are the same as they've been. In fact the datbase runs fine on one of the new PC's.
Another message we have seen on this is "Run-time error '3420' Object invalid or no longer set" which points to the Requery command in the OnActivate.
These are different messages, so maybe we've gone down the wrong path with the reference thing, but we would appreciate any help we can get!
Thanks ( sorry to go on so long),
Last edited by dbprog; 07-15-04 at 16:42.
Reason: More info in title
The references may not be missing, but they may be corrupted within the MDB file.
One sure-fire cure I've always found effective when this happens, is to create a new, blank MDB file in Access2000, then import all the objects from the converted A2k MDB file with the corrupted references into the new file. See if that works for you.
Thanks, this is always a good tip! Unfortunately, it didn't work either. I thought we were onto something. We had an isnull() function also and I thought when we got rid of that it helped. Unfortunately, I have a docmd.requery in the OnActivate of the form and I had commented that out. The form worked fine without requerying, but we need to get new numbers on the form when someone makes changes in the detail. Why would it work the first time in, but not when we requery?
I just re-read the initial post, and now I am not clear as to whether the problem is in VBA code or the SQL query. If it's the SQL, LEFT and RIGHT are valid commands but MID is not, though DAO may convert the MID to SUBSTRING.
That doesn't explain why it would work on some machines and not others, unless there are significant differences in DAO versions...
The problem is in a query. I completely took out all references except the VBA and Access refs (which you can't remove), and everything worked the same as before, so it's interesting that MID would work without the DAO reference after what you've said. What is weird is I can run the query by itself and it's fine. The first time I open the form it's fine. This same form has run for 4 years just fine! I know at this point it must be something really easy or really stupid, I just can't figure out what it is.
I keep trying to think what is different in our installs. The only thing I can think of is we have always installed Office from the server. The server that holds the installation files was recently upgraded from Win 2000 to Win 2003. I don't know why that would make a difference, I would like to think that's the problem, but one of the machines they recently did an install on, the database works fine just like the old machines!
We've decided to store the fields in the table and take out the functions from the query so we can move on, but I'm sure this will come up again, so any other thoughts are still greatly appreciated!
We also had done the decompile with no luck. I looked at the other thread and I think there must be something similar in both situations, hopefully we can find it!
The problem machines are Win2K, same set up as always, same files, versions, etc. I took the db home on my XP machine with Office XP and it worked fine. We put Office 2003 on a laptop here and it didn't work.