Max # of fields in a query?

  • Thread starter Thread starter Suzanne Knapp
  • Start date Start date
S

Suzanne Knapp

How many fields are allowed in a query? When I tried to make a query which
would include all fields in every table (to use as the basis of a group of
forms), I got an error message "too many fields". I don't need all of these
fields in any one form, but it's simpler to have only one query as the forms
are being developed. I have plenty of RAM and disk space - can the number
be configured to be larger?
 
Suzanne said:
How many fields are allowed in a query? When I tried to make a query
which would include all fields in every table (to use as the basis of
a group of forms), I got an error message "too many fields". I don't
need all of these fields in any one form, but it's simpler to have
only one query as the forms are being developed. I have plenty of
RAM and disk space - can the number be configured to be larger?
Click to expand...

255 with no work-arounds. There is also a limit to the number of bytes in a
single row. I don't recall what that is specifically but 2KB comes to mind.
 
Simpler is not always better. I can't imagine attempting to display more
than the limit of 255 fields. Most of the solutions I have seen involve far
fewer fields and use of subforms to display related records.
 
Duane Hookom said:
Simpler is not always better. I can't imagine attempting to display more
than the limit of 255 fields. Most of the solutions I have seen involve
far fewer fields and use of subforms to display related records.
Click to expand...
As I said, I'm not planning to display all of the fields at once - just to
have them available in one query which is bound to several different forms.
As for subforms, I agree that's the way to go unless you want to use Filter
by Form to select records based on the contents of related record fields.
 
I don't need all of these
fields in any one form, but it's simpler to have only one query as the forms
are being developed.
Click to expand...

255... and it's NOT simpler. Such a multitable query will almost
surely be non-updateable and will not support form/subform
arrangements. I'd go the opposite direction: a separate query, as
simple as possible, for each Form.

John W. Vinson[MVP]
 
John Vinson said:
255... and it's NOT simpler. Such a multitable query will almost
surely be non-updateable and will not support form/subform
arrangements. I'd go the opposite direction: a separate query, as
simple as possible, for each Form.
Click to expand...
The only reason I want to use a query-based form is that Filter by Form
doesn't select records based on the contents of subform fields. I think
that's a serious flaw - if it were fixed I'd be happy to use forms and
subforms, as I had originally intended.
 
Suzanne Knapp said:
The only reason I want to use a query-based form is that Filter by Form
doesn't select records based on the contents of subform fields. I think
that's a serious flaw - if it were fixed I'd be happy to use forms and
subforms, as I had originally intended.
Click to expand...

At the risk of sounding harsh, the basic problem is that you are trying to
implement a complex feature using basic tools (and perhaps knowledge).

You should start with proper table/query/form designs to implement proper data
entry. Once you have that then a separate search form can be built that is used
as a means to find records and then display them in the aforementioned data
entry forms.

This search form will almost certainly NOT be able to use Filter-By-Form, but
will need to use more advanced techniques involving dynamic creation of queries,
and/or filter strings in code in response to the filter inputs that the users
enters.

My search forms typically consist of a bunch of TextBoxes and ComboBoxes that
the user fills in for searching and the results of the search are shown on that
same form in a ListBox. When the desired item is found in the ListBox the user
can select it and then has options for printing or editing that record.

Filter-By-Form is a user (perhaps power user) feature and is not usually what
*developers* rely on in a distributed application. For one thing it is not even
supported in the Access runtime.

Do a Google search on the phrase "Query by Form" and you will get some ideas on
different ways to tackle this.
 
The only reason I want to use a query-based form is that Filter by Form
doesn't select records based on the contents of subform fields. I think
that's a serious flaw - if it were fixed I'd be happy to use forms and
subforms, as I had originally intended.
Click to expand...

Filter by form works perfectly well on subforms - *to find records
which are currently validly selected on the subform*. That is, if the
Master/Child Link Field relationship finds 12 records on the subform,
it's those 12 records which will be used as the domain for the search.

If you wish to be able to search on a subform, and thereby change
which mainform record is displayed, you can - but you'll need some VBA
code to do so.

John W. Vinson[MVP]
 
If you wish to be able to search on a subform, and thereby change
which mainform record is displayed, you can - but you'll need some VBA
code to do so.
Click to expand...

Yes, that is what I want to do - I don't see the value, at least in my
application, in having main records displayed whose related records don't
match the criteria entered. But I suppose that's a matter of opinion. I
need a general solution that allows the user to choose any combination of
criteria for the fields in the form/subforms, similar to the way the
FilemakerPro Find function works. It's just like Filter by Form (they call
forms "layouts") except that main record selection is affected by subform
field criteria. I am currently looking into Query by Form, as suggested by
Rick Brandt. Any other suggestions will be gratefully received.
 
Back
Top