Add Field from Query to Form - Access 2007

[Commish]

OK, so, I am certain that is something obvious that I am just not
seeing.

I'm creating some new forms, and I cannot figure out how to add a
field from an existing query.

When I select "Add Existing Fields" - my queries are not available; it
just displays the tables.

What am I missing here?

 

[John W. Vinson]

OK, so, I am certain that is something obvious that I am just not
seeing.

I'm creating some new forms, and I cannot figure out how to add a
field from an existing query.

When I select "Add Existing Fields" - my queries are not available; it
just displays the tables.

What am I missing here?

You need to select the Query (which must be a Select query, not an Action
query such as an append or update query) as the Recordsource for the form, or
change the existing recordsource.

What version of Access are you using, and what are you seeing on the "Add
Existing Fields" option?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

You need to select the Query (which must be a Select query, not an Action
query such as an append or update query) as the Recordsource for the form, or
change the existing recordsource.

What version of Access are you using, and what are you seeing on the "Add
Existing Fields" option?
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John,

What if I want to base the form off of a table and view related
information from a query, or if the form is based off of a query and
needs to show information form a second query?

This is for Access 2007.

In the Layout View, if I select "Add Existing Fields" it displays the
fields from the table that I have based my query on. I can select
"Show All Fields" - which then displays a list of tables.

Mark

 

[John W. Vinson]

What if I want to base the form off of a table and view related
information from a query, or if the form is based off of a query and
needs to show information form a second query?

This is for Access 2007.

In the Layout View, if I select "Add Existing Fields" it displays the
fields from the table that I have based my query on. I can select
"Show All Fields" - which then displays a list of tables.

Mark

A Form must be based on a single Table or a single Query. That Query can
contain more than one table (by going into query design and joining other
tables), though this may make updating the form tricky; or you can use a Form
based on one table (or query) with one or more Subforms based on other tables
or queries, with or without linking fields.

But no, you cannot have a form based on one table and just arbitrarily pull in
a field from this other table or that other table.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

A Form must be based on a single Table or a single Query. That Query can
contain more than one table (by going into query design and joining other
tables), though this may make updating the form tricky; or you can use a Form
based on one table (or query) with one or more Subforms based on other tables
or queries, with or without linking fields.

But no, you cannot have a form based on one table and just arbitrarily pull in
a field from this other table or that other table.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Base..

Can I base my form on a table and then display information/data from
other forms and queries? Or does each separate piece of data need to
be in its own form/subform?

But, I can base a form on a table and then add data from any number of
tables, but not an additional query?

Mark

 

[Commish]

A Form must be based on a single Table or a single Query. That Query can
contain more than one table (by going into query design and joining other
tables), though this may make updating the form tricky; or you can use a Form
based on one table (or query) with one or more Subforms based on other tables
or queries, with or without linking fields.

But no, you cannot have a form based on one table and just arbitrarily pull in
a field from this other table or that other table.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Another way to look at this - what I want to do is take a key ID field
from a table, and select a value using a pull down list. Then, have
the form be populated with records from 3-4 other tables/queries. And
I am having trouble overcoming some of the complexity of this. I am
also much more familiar with the old version of Access. So, I was used
to doing things one way... I aven't figured out how to "link" the
field with the pull down to the other pieces of data on the form.

 

[John W. Vinson]

Another way to look at this - what I want to do is take a key ID field
from a table, and select a value using a pull down list. Then, have
the form be populated with records from 3-4 other tables/queries. And
I am having trouble overcoming some of the complexity of this. I am
also much more familiar with the old version of Access. So, I was used
to doing things one way... I aven't figured out how to "link" the
field with the pull down to the other pieces of data on the form.

I'm sorry, Commish, your question doesn't give me any information I could use
to compose an answer. What are these tables? How are they related? How does
this ID field relate to the form, or to the other tables?

Nothing has changed in the underlying structures of forms and subforms, from
Access 2.0 through Access 2010; the "ribbon" interface is certainly new with
2007, and takes a good deal of getting used to - but Forms still have
Recordsources, there's still a toolbar (now on the ribbon) that lets you
insert subforms, subforms still have Master/Child Link Fields and so on.

If you could post some more details about the structure of your database -
what real-life Entities the tables represent, how the tables are related, and
what you want displayed on the form - I might be more able to help. Also
consider posting to the newer Microsoft forums in my .sig to see if some of
the other good folks who hang out there can help more.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Commish]

I'm sorry, Commish, your question doesn't give me any information I coulduse
to compose an answer. What are these tables? How are they related? How does
this ID field relate to the form, or to the other tables?

Nothing has changed in the underlying structures of forms and subforms, from
Access 2.0 through Access 2010; the "ribbon" interface is certainly new with
2007, and takes a good deal of getting used to - but Forms still have
Recordsources, there's still a toolbar (now on the ribbon) that lets you
insert subforms, subforms still have Master/Child Link Fields and so on.

If you could post some more details about the structure of your database -
what real-life Entities the tables represent, how the tables are related,and
what you want displayed on the form - I might be more able to help. Also
consider posting to the newer Microsoft forums in my .sig to see if some of
the other good folks who hang out there can help more.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

Sorry for not giving you enough detail - I was hoping for just enough
of a general answer to push me in the right direction.

But, let me try to give more detail.

I have a handful of tables that hold my data, and I am using queries
to pull out the subsets that I wish to query. Which is why I am
frustrated that Access 2007 is making it difficult to put query data
on a form.

I have everything - tables and queries - keyed on a PersonID.
I have a PersonDetail table that has some basic demographic
information on the person - location, etc.
I have a currentYear table for each person; each person must have 1
records in the CurrentYear table. The current year table represents
projected current assets for the coming period.
I also have an Asset table. The Asset table has none, one, or more
than one record for a person.

What I wish to do is to have a form that lets me select a PersonID
from a pulldown, and then populate the rest of the form, with subforms
for each of the three datasets - persondetail, currentYear and assets.
I can make the subforms for each of the three detail datasets, but I
haven't worked out how to link those subforms to the PersonID.

And I need to be able to at least update the PersonDetail in the
subform and have it write back to the person detail table.

Thanks.

 

[manfriday]

Hi

I noticed a question by a previous member. Which did not receive what I would consider to be a satisfactory answer: "why, when trying to populate a new blank form with fields, why does the field list contain only tables and not query's. It does not make any kind of sense to me. I used to use access up to and including Access 2003, after many years I have started using it again. I have started using Access 2007 with the new ribbon architecture


So let me repeat the question: Why am I now able to see any query's when I click on the ribbon's 'field list button' ???

MF

 

[manfriday]

Error correction to my previous reply

Why am I unable to access tables and not repeat not query's when pressing the field list button on the ribbon

I am using version Access 2007