I am trying to figure out how to populate address fields and subform
information in a form automatically when I select a business name in a pull
down. I have created the query for the pull down but I cannot figure out how
to populate the related fields.

WE,

If I understand you correctly, then this article may be of interest:
http://accesstips.datamanagementsolu...biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP

WEinLaguna wrote:
> I am trying to figure out how to populate address fields and subform
> information in a form automatically when I select a business name in a pull
> down. I have created the query for the pull down but I cannot figure out how
> to populate the related fields.

I have tried all of the methods in the article, the query looked like it
gathered the correct data but i am still running into errors such as #Name?
in the form fields I am trying to populate.

"Steve Schapel" wrote:

> WE,
>
> If I understand you correctly, then this article may be of interest:
> http://accesstips.datamanagementsolu...biz/lookup.htm
>
> --
> Steve Schapel, Microsoft Access MVP
>
> WEinLaguna wrote:
> > I am trying to figure out how to populate address fields and subform
> > information in a form automatically when I select a business name in a pull
> > down. I have created the query for the pull down but I cannot figure out how
> > to populate the related fields.
>

WE,

#Name? typically indicates that the Control Source of the textbox on the
form is not correct. Can you please look at the properties for the
affected controls on the form, and make sure that the Control Source is
entered correctly... you can select from the drop-down list of field names.

This assumes that the Record Source of the form itself has been set to
the name of the Query.

If it still doesn't come right, can you go to the design view of the
query, select SQL from the View menu, and copy/paste the SQL view of the
query into your reply, and also tell us which fields are showing the
error. Thanks.

--
Steve Schapel, Microsoft Access MVP

WEinLaguna wrote:
> I have tried all of the methods in the article, the query looked like it
> gathered the correct data but i am still running into errors such as #Name?
> in the form fields I am trying to populate.

Steve,
The record source for the form is a table I need to be able to add records
as well as recall from the existing table. I have been able to poulate some
fields by changing the control source of the text box to a specific column in
the queries text box =[Shop Name].column(3). This seems like a very round
about way and it does not work for the fields that I have value lists for.
Can the tble still be updated if the record source for the form is the query?

WE

"Steve Schapel" wrote:

> WE,
>
> #Name? typically indicates that the Control Source of the textbox on the
> form is not correct. Can you please look at the properties for the
> affected controls on the form, and make sure that the Control Source is
> entered correctly... you can select from the drop-down list of field names.
>
> This assumes that the Record Source of the form itself has been set to
> the name of the Query.
>
> If it still doesn't come right, can you go to the design view of the
> query, select SQL from the View menu, and copy/paste the SQL view of the
> query into your reply, and also tell us which fields are showing the
> error. Thanks.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> WEinLaguna wrote:
> > I have tried all of the methods in the article, the query looked like it
> > gathered the correct data but i am still running into errors such as #Name?
> > in the form fields I am trying to populate.
>

WE,

Sorry, I interpreted your earlier reply to mean that you had decided to
use the method that I refer to in my article as "Query".

It would assist those reading your posts if you could give some specific
details.

Here is what I have understood so far...

You have a form bound to a table.
One of the fields in that table is a Business.
This field is represented on the form by a Combobox.
The Row Source of the Combobox is another table, which lists the shops
with other contact information.
The Combobox's Column Count property is such that several of these
fields in the Shop table are included.
The name of the Combobox is [Shop Name].
When you enter a business in the Combobox, you have other controls on
the form where you want some of the shop's contact information to be
displayed.
You are successfully using the 3rd approach shown in the article, which
is referred to as "Column Property", in some of the textboxes.

This is a perfectly valid approach, and is not really "round about".

In response to your question, if you decided to try the "Query" method
instead, the data on the form should still be editable. As long as the
field in the shops reference table that is the basis of the relationship
to the other table, is the Primary Key field, or otherwise uniquely
indexed. And, as mentioned in the article, any controls bound to fields
from this shops table should have their Locked property set to Yes - you
want the fields from the main table to be editable, but this is not the
place for editing data from your master list of shops.

Is this making any more sense now?

--
Steve Schapel, Microsoft Access MVP

WEinLaguna wrote:
> Steve,
> The record source for the form is a table I need to be able to add records
> as well as recall from the existing table. I have been able to poulate some
> fields by changing the control source of the text box to a specific column in
> the queries text box =[Shop Name].column(3). This seems like a very round
> about way and it does not work for the fields that I have value lists for.
> Can the tble still be updated if the record source for the form is the query?

I am having a similar problem!
I am trying to edit a db that someone else created.

There is one table of data (RUNNERS).
There is one form (BIB LOOKUP).
The goal is that on the form you would type the BIB NO into a field and all
other runner info would populate.

I am not sure how this is accomplished - but it is working.

My issue is that I have a new set of data to load - with different column
names. I have replaced the data into RUNNERS and now I need to adjust the
form to read my new data, but I cannot figure out how this is done.

In the control source of the field which need to be populated, it says:
=[BIB LOOKUP].COLUMN(2)

If BIB LOOKUP is the name of the form itself, so I do not see how that is
reference my table RUNNERS. Obviously the column numbers are different, but
what else needs to be changed to avoid the #NAME? that I am receiving as
output?

Very confused, any input would be greatly GREATLY appreciated!


"Steve Schapel" wrote:

> WE,
>
> Sorry, I interpreted your earlier reply to mean that you had decided to
> use the method that I refer to in my article as "Query".
>
> It would assist those reading your posts if you could give some specific
> details.
>
> Here is what I have understood so far...
>
> You have a form bound to a table.
> One of the fields in that table is a Business.
> This field is represented on the form by a Combobox.
> The Row Source of the Combobox is another table, which lists the shops
> with other contact information.
> The Combobox's Column Count property is such that several of these
> fields in the Shop table are included.
> The name of the Combobox is [Shop Name].
> When you enter a business in the Combobox, you have other controls on
> the form where you want some of the shop's contact information to be
> displayed.
> You are successfully using the 3rd approach shown in the article, which
> is referred to as "Column Property", in some of the textboxes.
>
> This is a perfectly valid approach, and is not really "round about".
>
> In response to your question, if you decided to try the "Query" method
> instead, the data on the form should still be editable. As long as the
> field in the shops reference table that is the basis of the relationship
> to the other table, is the Primary Key field, or otherwise uniquely
> indexed. And, as mentioned in the article, any controls bound to fields
> from this shops table should have their Locked property set to Yes - you
> want the fields from the main table to be editable, but this is not the
> place for editing data from your master list of shops.
>
> Is this making any more sense now?
>
> --
> Steve Schapel, Microsoft Access MVP
>
> WEinLaguna wrote:
> > Steve,
> > The record source for the form is a table I need to be able to add records
> > as well as recall from the existing table. I have been able to poulate some
> > fields by changing the control source of the text box to a specific column in
> > the queries text box =[Shop Name].column(3). This seems like a very round
> > about way and it does not work for the fields that I have value lists for.
> > Can the tble still be updated if the record source for the form is the query?
>