#Error when record source changed on form in calculated control

[baggyboy26]

Hello.

This is confusing me. I'm getting #Error in some calculated controls. An unbound combo box updates the RecordSource of my form (Alters the WHERE part of the SQL) but does not always do it withouth producing the #Error. I've tried requerying, refreshing even setting the RecordSource to null before carrying out the procedure.
Whenever the form loads it works well but i don't want my users to keep reloading the form.
Unfortunately my research has failed to come up with a solution. Any ideas that point out my obvious mistake would be appreciated.



Sometimes the answer stares you right in the face!!!

 

[lameid]

First you could use filters instead of changing your record source. I do not know if this is related to your problem or not. If this is the sourcee, you should be able to navigate to a record before you filter that shows #Error when filtered and it display a normal result.


Assuming it is not changing the recordsource (seems like it should work), what is the control source of the control giving you #error? Seeing the calculation and any user defined function may help us see what the issue is.

 

[lameid]

Perhaps equally important, what are the values of the fields used in the calculated control for the record that is failing?

 

[baggyboy26]

When the form is opened everything works fine. Changing the recordsource, calculated controls and the filters are all ok. Could the speed of retrieving the data over a network be the problem between the front and back ends? Basically, if it takes too long it doesn't bother?

Sometimes the answer stares you right in the face!!!

 

[lameid]

Changing the recordsource, calculated controls and the filters are all ok.

If they are all ok, what is the problem?

Did you test the specific record scenario? What were the results?

 

[baggyboy26]

I think I've cracked it! Well I hope so anyway.

I think some of my data had been corrupted by users deleting stored values in a field. This did not make the stored value null, hence the error. Due to this I think I had some records that appeared to be null but actually weren't. I exported the tables into Excel and then reimported them and the #Error has disappeared.

I am now about to write a bit of code to prevent users from deleting values with the delete key and using the double click event instead.

Thanks for your help.

Sometimes the answer stares you right in the face!!!