Form Problems with Combo Boxes

[Confused]

I created a Contacts Table based on a Customer Table. I.e I copied the
Customer table and added fields such as contact name, tn, email etc. There
is a relationship based on Customer ID.

I then created a form based on a query of these two tables.

The problem started when I added a combo box based for the company names for
users to select from when they add another contact. The Row source is
SELECT DISTINCT Customers.[Customer Name], Customers.[Customer ID] FROM CLECs
ORDER BY Customers.[Customer Name]; Now when I select the company name it
only updates to the Customers list and not the Contacts list. The record
source is the Customer Name in the Query from the Contacts table.

The combo also does not select distinct.

I'm new to this...How should I set this up? I've spent all day changing it
around etc. Please help!

 

[Confused]

CLECs should have equaled customers. I replaced it in the statement below..
sorry...

Correct-two tables one for customers and one for contacts. There can be
multiple contacts for each customer. That's why I thought I needed the
table 'contacts.' I'm trying to show everyone the list of customers we have
and then have them add contacts as needed to the corresponding customer name.


And then when they click on a new record, they can use the combo drop down
to select the company name that corresponds to the contact they enter.

When I created the combo I didn't use the wizard- I just tried to make it do
what I wanted by using row source and record source. I bound the record
source to the Customer Name field from the query that I joined the two
together. Customer names was selected from the Contacts table on that
particular query.

I got my inspiration from Northwind. They have orders and products. They
can go in and select different products and it binds them to the orders table
even though it was selected from the products table. Their form for orders
is based on an orders query that ties orders and customers together.

Thank you very much for any assistance.

Let me get this straight - You now have two tables - Customer and Contacts.
Related by Customer ID. So a customer can have a contact.

Can the customer have more than one contact? One-to-many relationship? Or
only one contact? Can a contact be a customer?

You say you added a combo box based for the company names. What table are
company names in? What field is the combo bound to?

Which of these tables is refered to by CLECs?

I created a Contacts Table based on a Customer Table. I.e I copied the
Customer table and added fields such as contact name, tn, email etc. There
is a relationship based on Customer ID.

I then created a form based on a query of these two tables.

The problem started when I added a combo box based for the company names for
users to select from when they add another contact. The Row source is
SELECT DISTINCT Customers.[Customer Name], Customers.[Customer ID] FROM Customers
ORDER BY Customers.[Customer Name]; Now when I select the company name it
only updates to the Customers list and not the Contacts list. The record
source is the Customer Name in the Query from the Contacts table.

The combo also does not select distinct.

I'm new to this...How should I set this up? I've spent all day changing it
around etc. Please help!

 

[Confused]

I got the Relationship and the primary keys all set up. And the form/subform
does exactly what I want it to do...Thank you!

But I'm wanting to use a separate form for the Contact Information because I
can put it in a better format. Would you then suggest a form based on a
query or based solely on the contacts table? At that point would I still
need combos for new records, so they can select customer name to associate
John Doe e.g.? I really do thank you...

can go in and select different products and it binds them to the orders table
even though it was selected from the products table.
Your situation is different than the example you are citing.

They have a products list to pick from. So they have Orders (one) with
Products (many). You need a one-to-many relation between Customers
(Companies) and Contacts.

Open your Customers table in design view and make Customer ID the primary
key. Click on the Customer ID field and then on the icon that has a golden
key. If you have duplicates you will get an error message.

Once you have set the primary key you need to run an unmatched query to find
any records in the Contacts that are not in Customers and add or delete to
correct the error.

Then open the Relations window, select both tables, drag from Customers
Customer ID to the Contacts Customer ID. Select Referential Integrity and
Cascade Update.

Then use a form/subform for Customer/Contacts with Master/Child link on
Customer ID.

You do not need a combo box to select contacts as there are none to select
that are not already associated with a Customer.

CLECs should have equaled customers. I replaced it in the statement below..
sorry...

Correct-two tables one for customers and one for contacts. There can be
multiple contacts for each customer. That's why I thought I needed the
table 'contacts.' I'm trying to show everyone the list of customers we have
and then have them add contacts as needed to the corresponding customer name.


And then when they click on a new record, they can use the combo drop down
to select the company name that corresponds to the contact they enter.

When I created the combo I didn't use the wizard- I just tried to make it do
what I wanted by using row source and record source. I bound the record
source to the Customer Name field from the query that I joined the two
together. Customer names was selected from the Contacts table on that
particular query.

I got my inspiration from Northwind. They have orders and products. They
can go in and select different products and it binds them to the orders table
even though it was selected from the products table. Their form for orders
is based on an orders query that ties orders and customers together.

Thank you very much for any assistance.

Let me get this straight - You now have two tables - Customer and Contacts.
Related by Customer ID. So a customer can have a contact.

Can the customer have more than one contact? One-to-many relationship? Or
only one contact? Can a contact be a customer?

You say you added a combo box based for the company names. What table are
company names in? What field is the combo bound to?

Which of these tables is refered to by CLECs?

:

I created a Contacts Table based on a Customer Table. I.e I copied the
Customer table and added fields such as contact name, tn, email etc. There
is a relationship based on Customer ID.

I then created a form based on a query of these two tables.

The problem started when I added a combo box based for the company names for
users to select from when they add another contact. The Row source is
SELECT DISTINCT Customers.[Customer Name], Customers.[Customer ID] FROM Customers
ORDER BY Customers.[Customer Name]; Now when I select the company name it
only updates to the Customers list and not the Contacts list. The record
source is the Customer Name in the Query from the Contacts table.

The combo also does not select distinct.

I'm new to this...How should I set this up? I've spent all day changing it
around etc. Please help!

 

[KARL DEWEY]

You can have a form just for entering contact information based on a query on
the contacts table. There is not need for combo as you are not going to
relate one contact to many companies.

I got the Relationship and the primary keys all set up. And the form/subform
does exactly what I want it to do...Thank you!

But I'm wanting to use a separate form for the Contact Information because I
can put it in a better format. Would you then suggest a form based on a
query or based solely on the contacts table? At that point would I still
need combos for new records, so they can select customer name to associate
John Doe e.g.? I really do thank you...

I got my inspiration from Northwind. They have orders and products. They

can go in and select different products and it binds them to the orders table
even though it was selected from the products table.
Your situation is different than the example you are citing.

They have a products list to pick from. So they have Orders (one) with
Products (many). You need a one-to-many relation between Customers
(Companies) and Contacts.

Open your Customers table in design view and make Customer ID the primary
key. Click on the Customer ID field and then on the icon that has a golden
key. If you have duplicates you will get an error message.

Once you have set the primary key you need to run an unmatched query to find
any records in the Contacts that are not in Customers and add or delete to
correct the error.

Then open the Relations window, select both tables, drag from Customers
Customer ID to the Contacts Customer ID. Select Referential Integrity and
Cascade Update.

Then use a form/subform for Customer/Contacts with Master/Child link on
Customer ID.

You do not need a combo box to select contacts as there are none to select
that are not already associated with a Customer.

CLECs should have equaled customers. I replaced it in the statement below..
sorry...

Correct-two tables one for customers and one for contacts. There can be
multiple contacts for each customer. That's why I thought I needed the
table 'contacts.' I'm trying to show everyone the list of customers we have
and then have them add contacts as needed to the corresponding customer name.


And then when they click on a new record, they can use the combo drop down
to select the company name that corresponds to the contact they enter.

When I created the combo I didn't use the wizard- I just tried to make it do
what I wanted by using row source and record source. I bound the record
source to the Customer Name field from the query that I joined the two
together. Customer names was selected from the Contacts table on that
particular query.

I got my inspiration from Northwind. They have orders and products. They
can go in and select different products and it binds them to the orders table
even though it was selected from the products table. Their form for orders
is based on an orders query that ties orders and customers together.

Thank you very much for any assistance.







:

Let me get this straight - You now have two tables - Customer and Contacts.
Related by Customer ID. So a customer can have a contact.

Can the customer have more than one contact? One-to-many relationship? Or
only one contact? Can a contact be a customer?

You say you added a combo box based for the company names. What table are
company names in? What field is the combo bound to?

Which of these tables is refered to by CLECs?

:

I created a Contacts Table based on a Customer Table. I.e I copied the
Customer table and added fields such as contact name, tn, email etc. There
is a relationship based on Customer ID.

I then created a form based on a query of these two tables.

The problem started when I added a combo box based for the company names for
users to select from when they add another contact. The Row source is
SELECT DISTINCT Customers.[Customer Name], Customers.[Customer ID] FROM Customers
ORDER BY Customers.[Customer Name]; Now when I select the company name it
only updates to the Customers list and not the Contacts list. The record
source is the Customer Name in the Query from the Contacts table.

The combo also does not select distinct.

I'm new to this...How should I set this up? I've spent all day changing it
around etc. Please help!