Populate a field by choosing another

  • Thread starter Thread starter Alaska1
  • Start date Start date
A

Alaska1

I have a Company name in a drop down menu. When I chose the Company Name I
want the Address field to populate. How do I do that?
 
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I have the data in one table.

Jeff Boyce said:
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Alaska1 said:
I have a Company name in a drop down menu. When I chose the Company Name I
want the Address field to populate. How do I do that?
Click to expand...


.
Click to expand...
 
Then I guess I'm not seeing your picture.

Are you working in a form, or attempting to work directly in a table?

When you say you pick a CompanyName from a drop down box (AKA "combobox"),
where is that?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Alaska1 said:
I have the data in one table.

Jeff Boyce said:
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Alaska1 said:
I have a Company name in a drop down menu. When I chose the Company
Name I
want the Address field to populate. How do I do that?
Click to expand...


.
Click to expand...
Click to expand...
 
If you have only one table, then what is there to update? Updating I would
think requires a source table and a target table.

As Jeff is suggesting, storing the same information in more than one table
is generally considered poor practice. Do you have a good reason for storing
the same value in another table?

--
Duane Hookom
Microsoft Access MVP


Alaska1 said:
I have the data in one table.

Jeff Boyce said:
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Alaska1 said:
I have a Company name in a drop down menu. When I chose the Company Name I
want the Address field to populate. How do I do that?
Click to expand...


.
Click to expand...
Click to expand...
 
The data is in the same table. When I choose the company name I want the
address to populate automatically in a form.

Duane Hookom said:
If you have only one table, then what is there to update? Updating I would
think requires a source table and a target table.

As Jeff is suggesting, storing the same information in more than one table
is generally considered poor practice. Do you have a good reason for storing
the same value in another table?

--
Duane Hookom
Microsoft Access MVP


Alaska1 said:
I have the data in one table.

Jeff Boyce said:
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a Company name in a drop down menu. When I chose the Company Name I
want the Address field to populate. How do I do that?


.
Click to expand...
Click to expand...
Click to expand...
 
the drop down is in a form (combo box).

Jeff Boyce said:
Then I guess I'm not seeing your picture.

Are you working in a form, or attempting to work directly in a table?

When you say you pick a CompanyName from a drop down box (AKA "combobox"),
where is that?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Alaska1 said:
I have the data in one table.

Jeff Boyce said:
How depends on what ...

What data structure are you using? Are you saying that you want to have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a Company name in a drop down menu. When I chose the Company
Name I
want the Address field to populate. How do I do that?


.
Click to expand...
Click to expand...


.
Click to expand...
 
"populate automatically in a form" seems to imply that it's coming from
somewhere.

If you only have one table, then are you trying to (to paraphrase) "pick a
company and see it's information (including address)"?

If so, then here's a generic outline...

Create your form.

Create a query that returns records from the table.

Bind your form to the query. Add the fields (including Address) from the
query.

Add a combobox in the header, but do not bind it to any field in the table.

Create another query that gets CompanyID (your table DOES have a unique ID,
right?!) and CompanyName.

Use that query as a row source for the combobox in the form's header.

Modify the first query (the one that returns table records). In the
selection criterion under the ID field, put something like the following
(untested - your naming will vary):

Forms!YourFormName!cboYourComboboxName

One more thing ... in the combobox's AfterUpdate event, create an event
procedure and add:

Me.Requery

Here's what this setup does:

* When you first open the form, the query behind the form looks to the
(empty) combobox and returns that record (i.e., none).
* When you select a CompanyName and leave the combobox (<Enter> or
<Tab>), the AfterUpdate event fires
* The form requeries, looking for the record that matches the selected
one, then loads it.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Alaska1 said:
The data is in the same table. When I choose the company name I want the
address to populate automatically in a form.

Duane Hookom said:
If you have only one table, then what is there to update? Updating I
would
think requires a source table and a target table.

As Jeff is suggesting, storing the same information in more than one
table
is generally considered poor practice. Do you have a good reason for
storing
the same value in another table?

--
Duane Hookom
Microsoft Access MVP


Alaska1 said:
I have the data in one table.

:

How depends on what ...

What data structure are you using? Are you saying that you want to
have
[CompanyAddress] show up in two different tables?

If so, why?! If your (relational) database has already associated an
Address with a Company Name, there's no need to re-record the
Address.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a Company name in a drop down menu. When I chose the Company
Name I
want the Address field to populate. How do I do that?


.
Click to expand...
Click to expand...
Click to expand...
 
The data is in the same table. When I choose the company name I want the
address to populate automatically in a form.
Click to expand...

Use an unbound combo box; the combo box wizard has an option "Use this combo
to find a record". This will let you pick a company from a combo, and bring up
the address (and all the OTHER table fields) on the form.
 
Back
Top