Row Source

[injanib via AccessMonster.com]

I have a table that contains three fields. Customer name, Location, Phone
number.
My form also contains these three fields that are combo boxes. The table is
usesd as the row source of these fields in the form.
On my form I like to be able to select the customer name from the list, but
want the corresponding Location and Phone number to be automatically filled
out in my form's Location and Phone Number fields.

Thanks in advance.

 

[John W. Vinson]

I have a table that contains three fields. Customer name, Location, Phone
number.
My form also contains these three fields that are combo boxes. The table is
usesd as the row source of these fields in the form.
On my form I like to be able to select the customer name from the list, but
want the corresponding Location and Phone number to be automatically filled
out in my form's Location and Phone Number fields.

Thanks in advance.

Do the location and phone fields need to be Combo Boxes? I.e. do you want to
be able to select a record in any of the three controls? Do note that if
you're using combo boxes to FIND records (rather than to update data in the
existing record) then the combo boxes must be Unbound - having nothing in
their Control Source property.

You can *display* the location and phone number (not editably) on the form by
setting the control source of textboxes to

=comboboxname.Column(n)

where the combo box contains all three columns of data, and (n) is the zero
based index of the desired field; e.g. if the phone number is the fourth
column in the combo use (3).

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

My form is based on a different table called Tracking. I use this form to
enter customer orders. I need to keep the control source as "Tracking" so
that I can print reports of the orders. My combo boxes are not to find record.
They are to be filled out everytime I log orders. However, because I already
have a table of my customers names and their locations and phone number, I
somehow want to set these three fields such that I can select the name from
the table and have the other two fields automaticaly fill out with the
corresponding data. This is to eliminate having to type the customer names
locationans and phone number everytime I log orders.

I have a table that contains three fields. Customer name, Location, Phone
number.

[quoted text clipped - 5 lines]

Thanks in advance.

Do the location and phone fields need to be Combo Boxes? I.e. do you want to
be able to select a record in any of the three controls? Do note that if
you're using combo boxes to FIND records (rather than to update data in the
existing record) then the combo boxes must be Unbound - having nothing in
their Control Source property.

You can *display* the location and phone number (not editably) on the form by
setting the control source of textboxes to

=comboboxname.Column(n)

where the combo box contains all three columns of data, and (n) is the zero
based index of the desired field; e.g. if the phone number is the fourth
column in the combo use (3).

John W. Vinson [MVP]

 

[John W. Vinson]

My form is based on a different table called Tracking. I use this form to
enter customer orders. I need to keep the control source as "Tracking" so
that I can print reports of the orders. My combo boxes are not to find record.
They are to be filled out everytime I log orders. However, because I already
have a table of my customers names and their locations and phone number, I
somehow want to set these three fields such that I can select the name from
the table and have the other two fields automaticaly fill out with the
corresponding data. This is to eliminate having to type the customer names
locationans and phone number everytime I log orders.

STOP.

You're missing the point of how relational databases are designed to work!

An Order does not have a phone number. A *customer* has a phone number!

Fields that pertain to a customer - address, phone number, name - should exist
in the customer table, and ONLY in the customer table. The Order table should
have a CustomerID, a foreign key to the unique primary key of the customer
table - *and nothing else* from the customer table!

If you need to *see* customer information in conjunction with user information
on a Form, use the textbox referencing the combo box; if you need to see the
information together on a Report, use a Query joining the orders table to the
customer table. Storing the customer address and phone redundantly in the
Orders table *is simply bad design*.

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

Sorry! i am still new access.

please explain to me. first: how do I set the primary key of the Customer
table as the Customer ID in the order table.

Second: The combo box to which the textboxes should be referenced to, am I
supposed to creat this combobox somewhere on the form for this purpose?

My form is based on a different table called Tracking. I use this form to
enter customer orders. I need to keep the control source as "Tracking" so

[quoted text clipped - 5 lines]

corresponding data. This is to eliminate having to type the customer names
locationans and phone number everytime I log orders.

STOP.

You're missing the point of how relational databases are designed to work!

An Order does not have a phone number. A *customer* has a phone number!

Fields that pertain to a customer - address, phone number, name - should exist
in the customer table, and ONLY in the customer table. The Order table should
have a CustomerID, a foreign key to the unique primary key of the customer
table - *and nothing else* from the customer table!

If you need to *see* customer information in conjunction with user information
on a Form, use the textbox referencing the combo box; if you need to see the
information together on a Report, use a Query joining the orders table to the
customer table. Storing the customer address and phone redundantly in the
Orders table *is simply bad design*.

John W. Vinson [MVP]

 

[John W. Vinson]

Sorry! i am still new access.

You might want to check out some of these resources, particularly Crystal's
tutorial and the Database Design 101 links on Jeff's website:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

please explain to me. first: how do I set the primary key of the Customer
table as the Customer ID in the order table.

If CustomerID (don't use spaces in fieldnames, if you want to avoid problems
in the future) is an autonumber Primary Key in Customers, create a field named
CustomerID (well, it can have any name, but it's easier to remember if you use
the same name!) in the Orders table. Use a Number datatype; in the number
properties on the lower left of the screen be sure it's still the default Long
Integer datatype.

Then open the Relationships window; add both tables; and drag CustomerID from
the Customers table to CustomerID in the Orders table. Check the "Enforce
Referential Integrity" checkbox. This will ensure that you can't enter an
order for a nonexistant customer.

Second: The combo box to which the textboxes should be referenced to, am I
supposed to creat this combobox somewhere on the form for this purpose?

Yes.

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

Ok, this works great, but here is my concern. I feel the need to delete some
customers from time to time if they no longer exist or don't do business with
me any longer. However, I want to keep a history of my orders. If I delete a
customer from my customer table what happens to the orders made for those
customers in the past?

 

[John W. Vinson]

Ok, this works great, but here is my concern. I feel the need to delete some
customers from time to time if they no longer exist or don't do business with
me any longer. However, I want to keep a history of my orders. If I delete a
customer from my customer table what happens to the orders made for those
customers in the past?

They'll be deleted, and you'll lose the history. Not good!

What I'd suggest instead is that you create a Yes/No field in the Customers
table named Active, with a default value of True. When a customer ceases to
exist for the purposes of your current database, simply uncheck the checkbox
for this field on the Customers form.

Base your combo boxes and display forms, not directly on the customers table,
but on a Query selecting those customers who are Active.

This will let you keep the history without your active database being
cluttered with former customers.

John W. Vinson [MVP]

 

[injanib via AccessMonster.com]

Thanks allot John.

They'll be deleted, and you'll lose the history. Not good!

What I'd suggest instead is that you create a Yes/No field in the Customers
table named Active, with a default value of True. When a customer ceases to
exist for the purposes of your current database, simply uncheck the checkbox
for this field on the Customers form.

Base your combo boxes and display forms, not directly on the customers table,
but on a Query selecting those customers who are Active.

This will let you keep the history without your active database being
cluttered with former customers.

John W. Vinson [MVP]