Forms work and then don't work and then work...

[TwoOdd]

This is driving me crazy. We are using Access 2000 and Access 2003. Forms will work for a while and all of sudden stop working. I'll change some code, any code, save the db, then change the code back to what is was originally and save it again and it will work again...with the same exact code that wasn't working! Just doing a compact and repair doesn't make it work. Funny thing is, sometimes going through the process above works the first time and sometimes it doesn't. Fact of the matter is, once it works again, the code is identically as it was prior to working.

Also, the same database on Access 2003 using Windows XP will work on one pc, but not on another -- both pc's have the same setup. Case in point, I just went over to someone else's pc to fix their db -- couldn't fix it at their machine -- so I copied it to my pc so I could fix it at my desk -- program works fine, no errors, can't reproduce the problems on my machine?! Both pc's are running Windows XP and Access 2003!! It's the exact same db. HELP!!!!!! Am I crazy or has anyone else experienced this?

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[PHV]

Some error message(s) ?
Same versions of ALL used DLLs, ActiveX, ... ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TwoOdd]

The most recent error message was error 2001 previous action was cancelled (or something along those lines -- can't reproduce it on my machine so I don't have the exact text). That error occurred when trying to reset the record source for the subform.

I checked the references in the tools section and they are identically (same order as well).

By the way, the errors only occur on one form. The db has about 10 different forms, but whenever a problem occurs, it's always on the same form.

The only other common thread among all the errors, everytime I go in to debug the vba, it errors out on a line that references two unbound combo boxes in the main form. Realize, there are many different lines that reference the combo boxes, it's not the same line that causes the error evertime, but whichever line it errors out on that line just happens to reference both of the unbound combo boxes.

I was wondering if it wasn't capturing the value in the combo boxes, so prior to the error line, I put in a msgbox to print the value of the combo boxes and the values showed up just fine.

What really makes this difficult, is that I have never been able to reproduce the errors on my machine. This has been going on for about a month or so now. I have been able to resolve most of the errors using the process described in the first post, but today with the error 2001, I have not been able to solve it. Hopefully, with some help, I'll be able to resolve this one and in the process keep the others from reoccurring.

Let me know if you need more info.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TheAceMan1]

How are ya TwoOdd . . . . .

[blue]I checked the references in the tools section and they are identically (same order as well).[/blue]

Excellent!

[blue]By the way, the [purple]errors only occur on one form[/purple]. The db has about 10 different forms, but whenever a problem occurs, [purple]it's always on the same form[/purple].[/blue]

Nice & specific!

Several things you can try:

In the form window, rename the form, copy the form, paste the form. Assign origional name.

Make a blank database & import all.

Reconstruct a new form & see if it works (include any code . . .of course).

Out of curiosity, post the recordsource for the form.

See Ya! . . . . . .

 

[TwoOdd]

Here is the structure of the line that resets the recordsource:

Forms![MainForm]![SubformObjectName].Form.RecordSource = "SELECT MyTable.* FROM MyTable WHERE (((MyTable.Field1)=nz(Forms![MainForm]![Combo76]))) AND (((MyTable.Field2)=nz(Forms![MainForm]![Combo46])));"

This works everytime on my computer and works majority of the time on everyone elses computer. But every now and then it will just stop working on someone elses computer. I'll go in and change the line and save it. Then change it right back, compact and repair the db, usually after that it magically works again for another week or two before it stops working again.

The two error messages users have experienced are:

error 2001 previous action was cancelled

and

(sorry don't remember the error number here)
You have entered an invalid expression...

I won't be able to test anything immediately, because I have to go to the users computer to do the testing, remember everything works on my pc so there's nothing to fix on my pc. The users are in a different building so it will take me a while to do the testing. In the meantime, any and all suggestions are welcome.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TheAceMan1]

TwoOdd . . . . .

You need to answer [blue]PHV's[/blue] questions (I thought you would've answered by now). I believe he's on target here. In fact, [blue]Version Conflict[/blue] is the only thing that fits the problem. [blue]And you did say you were using 2000 & 2003![/blue]

Of high interest are the reference files, check those first.

Let us know of any Version Conflicts you find.

See Ya! . . . . . .

 

[RoyVidar]

You could try concatinating the values of the combos into the recordsource string:

[tt]Forms![MainForm]![SubformObjectName].Form.RecordSource = "SELECT MyTable.* FROM MyTable WHERE MyTable.Field1= " & nz(Forms![MainForm]![Combo76]) & " AND MyTable.Field2= " & nz(Forms![MainForm]![Combo46])[/tt]

- here assuming the two fields are numeric (in this referencing, using the me keyword in stead of forms!formname would also work for the combos), could perhaps also explicitly state the return values of the nz function if Null (0). Another thought, don't know how relevant it is, but could it be a possibility that the combos return an empty string, an not Null? Then other tests might perhaps be better, for instance

[tt]if (len(trim(me("Combo76").value & vbnullstring))=0) then
strFirstCrit=0
else
strFirstCrit=clng(me("Combo76").value)
end if[/tt]

I know the kind of referencing you do, works sometimes (amongst others when passing a where condition when opening reports), but I've found concatenation of the values into the SQL strings more reliable.

I can't substantiate this in any way, but I have a slight impression that 2003 is, well not more demanding, but perhaps less forgivning than previous versions regarding referencing, but again, this is heresay. Interested in the result of this.

Form corruption, as mentioned in TheAceMan1's previous reply, is also something I suspect whenever something doesn't perform as expected, trying Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times often resolves such on my setups.

Roy-Vidar

 

[willir]

Do you compact and repair on the 2000 or 2003 workstation? Compact and repair will recompile the forms, etc which may create compatability problems, especially if compilation occurs on the 2003 workstation and then accessed by the 2000 workstation