Odd "#Error" in field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I send a SQL statement to as it's Record source. The
query has a few little twists the users can specify, so I do the in-line
composition of the statement rather than have a couple versions of a similar
query hanging around. The query and form work fine...

In one process I output this query with a couple of others, and dumpt he
data to Excel...again, no problem. I do have an odd behavior though if I
close a form that lies on top of this particular form-at that point I lose
one of the columns in the datasheet to "#Error"...usually just the rows that
were covered get blown away initially, but if I scroll a bit then they all
go...but this appears to be merely a screen re-paint issue: If I export the
datasheet to excel with a partially filled column of "#error" the spreadsheet
gets no good data in that column.

As of now this is a mere annoyance...I can't see my users running into this
with any kind of frequency, and the processes I've designed do their job.
Just seems odd that the closing of another form would lead to this AFTER the
query has run and all the data appears to be gathered correctly. Certainly
I've seen "#Error" before, but usually I've been able to locate a cause for
it.

If anyone has any suggestions, I'd appreciate them!
 
What is the relationship with the "form that lies on top" and the "form that
I send a SQL statement". If the top form holds the code that writes the SQL
statement then the SQL is maybe part of a local variable that goes out of
scope when you close that form.
Or is there any code in the OnClose event for the "top" form?
 
Thanks Alicia...
What is the relationship with the "form that lies on top" and the "form that
I send a SQL statement".
Click to expand...

Not necessarily much...there is a public string variable that is generated
in the forma that calls the three datasheets...one of which is the form that
gets the "#Error". Aside from being called from the same form the three
datasheet forms that are displayed have nothing in common.

The form that gets the error is also displayed in a process that only
displays the results of that query. If I run the query this way (essentailly
isolating the one troublesome datasheet) I can still create the error by
opening any other process that displays a datasheet, and closing the other
datasheet that covers up any of the screen space that the error prone sheet
occupies...

At least I *think* it only happens when another datasheet form gets into the
act...I can take the form that sets the parameters for this query (and passes
the public string), move it all over the datasheet, and close it with no
problem...it's when another datasheet form moves in front of it, and that
oterh form is closed that the "#Error" appears...

So it doesn't seem like the reference comes from the "other"
form...especially since the public string (the SQL statement for the form
that's getting the error) gets saved in a variable in the local form as soon
as it opens.
 
Figured it out:

After playing around a bit I determined that one field that relied on a
nasty looking Iif statement was the culprit. I wrote a function in my main
code module to do the work and no more "#Error". :-)

Moral of the story? I guess if the field you are trying to display requires
more than 1 level of Iif statements you're probably better off writing a
function.
 
Back
Top