Autofill multiple form fields based upon user selection

I have two tables (1) Customers and (2) Orders. The Customers table
stores information on the customers (e.g. company name, address, phone
number, etc.) and the Orders table stores information on customer
orders. I've created a form based upon the Orders table. I want the
user to be able to select a customer and that customer's information
to be automatically filled into the form.

To do this, I used a Combo Box. The Row Source for the Combo Box
selections is from the Customers table. In the Order form I set the
After Update of the customer Combo Box to:

CustomerAddress = Customer.Column(1)

And I set the Control Source of the CustomerAddress field to:

Customer.Column(1)

How can I have additional form fields automatically filled based upon
the Combo Box selection (e.g. city, state, zip etc.).

Thanks!

Re: Autofill multiple form fields based upon user selection

Relational databases are intended to minimize redundancy, and copying data
from one table into another is redundant. Unless the customer's address
information is likely to change and you need to capture what it is at the
moment of the order for posterity, it would be better if your order record
included a "foreign key" field that can be used to join with the Customers
Table in a Query.

You can include additional columns in the Combo box to contain the other
fields of interest and refer to them with the same method Column(x) if you
do need to capture and save it.

Larry Linson
Microsoft Access MVP

wrote in message
news:1193860906.719977.294780@o80g2000hse.googlegroups.com.. .
>I have two tables (1) Customers and (2) Orders. The Customers table
> stores information on the customers (e.g. company name, address, phone
> number, etc.) and the Orders table stores information on customer
> orders. I've created a form based upon the Orders table. I want the
> user to be able to select a customer and that customer's information
> to be automatically filled into the form.
>
> To do this, I used a Combo Box. The Row Source for the Combo Box
> selections is from the Customers table. In the Order form I set the
> After Update of the customer Combo Box to:
>
> CustomerAddress = Customer.Column(1)
>
> And I set the Control Source of the CustomerAddress field to:
>
> Customer.Column(1)
>
> How can I have additional form fields automatically filled based upon
> the Combo Box selection (e.g. city, state, zip etc.).
>
> Thanks!
>

Re: Autofill multiple form fields based upon user selection

On Oct 31, 6:10 pm, "Larry Linson" wrote:
> Relational databases are intended to minimize redundancy, and copying data
> from one table into another is redundant. Unless the customer's address
> information is likely to change and you need to capture what it is at the
> moment of the order for posterity, it would be better if your order record
> included a "foreign key" field that can be used to join with the Customers
> Table in a Query.
>
> You can include additional columns in the Combo box to contain the other
> fields of interest and refer to them with the same method Column(x) if you
> do need to capture and save it.
>
> Larry Linson
> Microsoft Access MVP
>
> wrote in message
>
> news:1193860906.719977.294780@o80g2000hse.googlegroups.com.. .
>
> >I have two tables (1) Customers and (2) Orders. The Customers table
> > stores information on the customers (e.g. company name, address, phone
> > number, etc.) and the Orders table stores information on customer
> > orders. I've created a form based upon the Orders table. I want the
> > user to be able to select a customer and that customer's information
> > to be automatically filled into the form.
>
> > To do this, I used a Combo Box. The Row Source for the Combo Box
> > selections is from the Customers table. In the Order form I set the
> > After Update of the customer Combo Box to:
>
> > CustomerAddress = Customer.Column(1)
>
> > And I set the Control Source of the CustomerAddress field to:
>
> > Customer.Column(1)
>
> > How can I have additional form fields automatically filled based upon
> > the Combo Box selection (e.g. city, state, zip etc.).
>
> > Thanks!

Thanks for the reply. I assume that I would have to include the
Column(x) statement in the Control Source of the other fields but does
it also have to be entered into the After Update of the Combo Box?

For instance, would I need to place a statement, similar to below, in
the After Update of the Combo Box? If so, how do I format it
correctly?

CustomerAddress = Customer.Column(1), CustomerCity =
Customer.Column(2), CustomerZip = Customer.Column(3)

Thank you!

Re: Autofill multiple form fields based upon user selection

I don't recall ever using a Column reference to a Combo Box in the Control
Source of a Control. If you did, that would only display it, because you
have no other place to define the bound field for it to be stored. I've
always bound the Controls to the Field where I wanted data stored, and set
their value in the event code for After Update of the Combo Box. I did that,
even if I only wanted to display the informations, by using an unbound
Control.

Larry Linson
Microsoft Access MVP

"CHurst5841" wrote in message
news:1193881907.314431.255810@o80g2000hse.googlegroups.com.. .
> On Oct 31, 6:10 pm, "Larry Linson" wrote:
>> Relational databases are intended to minimize redundancy, and copying
>> data
>> from one table into another is redundant. Unless the customer's address
>> information is likely to change and you need to capture what it is at the
>> moment of the order for posterity, it would be better if your order
>> record
>> included a "foreign key" field that can be used to join with the
>> Customers
>> Table in a Query.
>>
>> You can include additional columns in the Combo box to contain the other
>> fields of interest and refer to them with the same method Column(x) if
>> you
>> do need to capture and save it.
>>
>> Larry Linson
>> Microsoft Access MVP
>>
>> wrote in message
>>
>> news:1193860906.719977.294780@o80g2000hse.googlegroups.com.. .
>>
>> >I have two tables (1) Customers and (2) Orders. The Customers table
>> > stores information on the customers (e.g. company name, address, phone
>> > number, etc.) and the Orders table stores information on customer
>> > orders. I've created a form based upon the Orders table. I want the
>> > user to be able to select a customer and that customer's information
>> > to be automatically filled into the form.
>>
>> > To do this, I used a Combo Box. The Row Source for the Combo Box
>> > selections is from the Customers table. In the Order form I set the
>> > After Update of the customer Combo Box to:
>>
>> > CustomerAddress = Customer.Column(1)
>>
>> > And I set the Control Source of the CustomerAddress field to:
>>
>> > Customer.Column(1)
>>
>> > How can I have additional form fields automatically filled based upon
>> > the Combo Box selection (e.g. city, state, zip etc.).
>>
>> > Thanks!
>
> Thanks for the reply. I assume that I would have to include the
> Column(x) statement in the Control Source of the other fields but does
> it also have to be entered into the After Update of the Combo Box?
>
> For instance, would I need to place a statement, similar to below, in
> the After Update of the Combo Box? If so, how do I format it
> correctly?
>
> CustomerAddress = Customer.Column(1), CustomerCity =
> Customer.Column(2), CustomerZip = Customer.Column(3)
>
> Thank you!
>