populating option box with correct options

[Guest]

I have seen this asked before and printed the answer but I lost the sheet and
can't find it again, so sorry about the duplicate question.

I have a form called customers that has a subform called salespersons. I am
trying to make a job form and what I want is when you choose the customer the
combo box displays only the salepersons for that customer.

Do I need a field in the jobs table called Salesperson and then write and
SQL statement or should I link it another way?

 

[Guest]

If you want to display only one value, then a combo box is the wrong control
to use. A text box would be more appropriate. How to get the correct value
into the control will depend on how your data is organized.

 

[John Vinson]

I have seen this asked before and printed the answer but I lost the sheet and
can't find it again, so sorry about the duplicate question.

I have a form called customers that has a subform called salespersons. I am
trying to make a job form and what I want is when you choose the customer the
combo box displays only the salepersons for that customer.

Do I need a field in the jobs table called Salesperson and then write and
SQL statement or should I link it another way?

How do you record which salesperson as associated with which customer?
Having a Subform doesn't do so - the subform on the Customers form
could contain any number of salespeople.

What are the relationships between Customers, Jobs, and salespeople?

John W. Vinson[MVP]

 

[Guest]

Currently I am set up with a customers table and then a salespersons table.
Then I have a form called customers that has a subform called salesperson. I
had to use a subform because there is more than one salesperson per customer.

Now I need to create a jobs form. When I select a customer I want it to
bring up all the salespersons for that customer in a combo box and the person
keying in the job will select which salesperson is responsible for that job.

The reason for this is because when I print any kind of paper work for the
job I need it to reference the correct salesperson.


hope this makes sense

 

[Guest]

I have a direct relationship between customers and salesperson

My customers form has tabs at the top

"Customer Info" "Billing Info" "Shipping Info" "Sales Persons"

on each tab there is a customer ID that connects them all so they all update
together. So when I key in a customer and then key in the sales people
(there are often more than one) the next time I look it up it only shows the
salesperson for that customer.

The subform I have in the customers form is a continous form that allows me
to key in more than one at a time or go back and add more.

 

[John Vinson]

I have a direct relationship between customers and salesperson

My customers form has tabs at the top

"Customer Info" "Billing Info" "Shipping Info" "Sales Persons"

on each tab there is a customer ID that connects them all so they all update
together. So when I key in a customer and then key in the sales people
(there are often more than one) the next time I look it up it only shows the
salesperson for that customer.

The subform I have in the customers form is a continous form that allows me
to key in more than one at a time or go back and add more.

You're describing your Form.

Data is not stored in Forms. It's stored in Tables.

What are your Tables? What is each Table's Primary Key? How (if at
all) are the tables related in the Relationships window?

John W. Vinson[MVP]

 

[Guest]

Here's what my tables look like:

Customers
CustomerID -----primary key
CompanyName
CompanyAddress
etc......

Salespersons
SalespersName-----primary key
CustomerID
etc.....

In the relationship table they are connected by the CustomerID


Hope this will make sense to you, if not I give up.