Field not in Field List: What to do?

J

Jerry Crosby

I have a form based on a crosstab query. Occasionally, because of the way
crosstab queries work, a particular field may not appear in the final matrix
(because no data is contained in that "crosstab" location).

When that happens, I'll get an error in the field because that field is not
in the field list of the query.

What is the code/function/etc. for that field box on the form that would
say, "If this field doesn't exist in the field table, then just leave it
blank (and don't enter '#NAME?')"

Thanks in advance.

Jerry
 
G

George Nicholson

You can use the ColumnHeadings property of the crosstab query to specify the
"variable" field names. If there is no data, the field will still appear
but it will be empty. This makes forms & reports based on the query much
more cooperative.

Obviously, this is only helpful if you have a short, static list of possible
fieldnames. Otherwise, the solutions require some coding to 1) get the list
of Fieldnames currently in the result set and then 2) change the
ControlSource of Form/Report controls to the current Fieldnames as the
Form/Report opens (or leaving the controls unbound and poking the
appropriate values into controls yourself...)
--
HTH,

George Nicholson
(Please post responses to newsgroup but remove "Junk" from return address if
used)
 
J

Jerry Crosby

Thanks, George. It will take some work...I haven't got it totally figured
out yet. wil have to experiment at home tonight. And, yes, I have a short,
static list of possible field names. Only 3.

If I don't get it working, I'll repost.

Jerry
 
N

Neil

With your "short" list table, link the query to include all records from the
"Short" list table to the coresponding record in your table/query. In
another words, left table link points to right table. that should do it.
 
Top