Field dissapears from query when "Show" is unchecked

[DavidG]

Access 2007: I am working on a simple query [call it Q1] that combines
fields form 2 tables/queries. I have a second query [call it Q2] that
uses data from Q1.
Q1 data is displayed in SubForm1. Q2 data is displayed in SubForm2.
SubForm1 is a datasheet view form. I test my design and everything
works as expected.

Q1 has an ID [auto generated primary key] field which I don't want to
display is the datasheet view. I uncheck the "Show" check box in the
Q1's query design view. Save it, and try to run it. Errors are
generated by Q2.There is missing data. It can't find the ID data that
I just hid in Q1.

I open Q1 in design view and the ID column is missing. Hiding the
column caused the field to be dropped from the query. Restoring the ID
field restores the functioning of Q2. I try hiding the ID field in Q1
again and get the same error results. Opening up Q1 in desing view
shows the ID field has been removed from the query.

Any comments on why the field disappears would be greatly appreciated.

 

[Albert D. Kallal]

Rememmber, the field list in a query has no speical relatoship

The remember, the field list in the query has no special relationship to
that of the sub form that you've built. Adding new columns, or removing
columns from the query does not in any way shape or form cause the sub form
itself to display or change the fields that you've defined in the query.

In a form, even a datasheet or continuous form, what fields does are
displayed is determined by the text box control that you place on that form.
It should be further noted that those text box control you place on the form
does not need to have the same name as the actual fields or columns as your
query has.

So I can create a text box and place it on that data sheet form and call
that text box

MrStupidZoo

I can now set the data source of the above text box to the column called id.
(you change this in the property sheet for the control, it's called the data
source property)

If that column is removed or unchecked from the query as you did in your
example, then you would also have to open up the form in design mode and
remove the text box controll called MrStupidZoo.

I suspect when you try to display to the sub form without the ID, you're
getting some kind of prompt to enter a value for the id.

So when you add a new column to a query, or in your case remove a column
from a query, you must bring up that form in design mode, and remove any
text box control placed on the form that is bound to that particular field
you are using. As I pointed out above, that text box controll does not even
have to have the same name as the column, it's the data source setting for
the text box control inside that will determine what column the text box
displays.

It is very common practice to name the text box control on the form has the
same as the column name (and wizard created forms are this way). This naming
convention is not a requirement, and often some developers always use
different names as to not to create any kind of confusion between column
names, and the names of text box controls that you place on a form.

So if you remove in that column from the query, you must bring up that sub
form in design mode, and find that text control that is bound to the column
that you just removed. When you find a text box control, you simply then
delete that from a form, and that should resolve your problem.

 

[Jeff Boyce]

David

"Unchecked" <> "Hidden"

In an Access query design window, unchecking the "Show" tells Access you
don't want it. If you look at the SQL statement Access generates, you won't
see the unchecked field.

So when Query2 tries to find something with that name, it is not there.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tom Wickerath]

If you look at the SQL statement Access generates, you won't

see the unchecked field.

Unless......you apply a sort, add a criteria, or involve the deselected
field in some way. This is not new behavior for Access. Every version of
Access that I can remember will remove a field from the QBE grid (and the
resulting SQL statement) if the field is not needed for JET to solve the
query plan.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________