Limiting a list box

[lukyhare]

Im making a form with a sub form as follows:

Main form: Customer Company Info (main Company information)
sub form: Customer Contacts (information on each contact, each company can have multiple contacts)

I have a combo box that is the main contact person for the company (each company can only have one main contact) in the maind form.
The problem is now when you choose from the combo box, it lists all the contacts not just the contacts who belong to that company.
Is there anyway i can set this up so in the combo box it only shows the contacts for that company?

Thanks for all your help. Have a great weekend.

Len

 

[Ukraine82]

I would make a blank combo box.

Set your Row Source Type to Value List and Row Source Type in your contact names. Ex: "Mike";"John";"Anna"

Michael

 

[Mile-O]

Do a search on Cascading Comboboxes.

 

[joeselch]

The Row Source for your combo should be a query based on the lookup table for the many-to-many relationship between customers and contacts.

Use a WHERE clause to limit the returned records to only those contacts associated with the selected customer.

 

[mohammadagul]

as per joeselch you combobox rowsource should be based on a Query, so what i wuold suggest tht you make a query from table of the needed data in your combobox. Do remeber to add the Company name so that it can filter for you only the details of the company you have chosen

in the criteria type the folloiwng
[Forms]![FormName]![FieldName]

Forms will remain same as it is
Formname is the name of your Form in which combo box resides
fieldname is the name of your textbox/combbox for which you are trying to filter records.

Same things goes for a continuous subform

 

[lukyhare]

First thanks for all the feedback and suggestions.

joeselch,mohammadagul,

Im trying to get the querry that u suggested to work in my form. I made a querry of my customer contacts and main contact person forms using just Company ID and Customer ID. (i tried company name also but figured company ID would be best). I put
[Forms]![CustomerCompanyandContactsInputForm]![CustomerID]
In the criteria under Customer ID. (I also tried using this in the criteria under Company ID and Company name)
Then in my form in the combo box property i changed the row source to the querry. Then i tried it and it doesnt work. If i change the control source to blank, i only get blanks, and if i make the control source the Customer ID, i only get one contact choice and this same contact choice no matter which company i am in. Can you tell where im going wrong.
Thanks for your help again.

Len

 

[joeselch]

Len,

You can't make a query of a form; queries are based on tables (or other queries).

You should be making a query of the junction table that links customers and contacts.

What are the names of this table and its fields?

 

[lukyhare]

Oh my bad, thats what i meant, i made a querry of 2 tables to use in the form.

The querry is using 2 tables:
1CustomerCompanyInfo
1CustomerContactList

The fields:
1CustomerCompanyInfo: CompanyID (Key), CompanyName, CustomerID, Address, City/State, Country, PostalCode, WorkPhone1, WorkPhone2, Fax1, Fax2, ShippingCompany, ShippingCompanyAccount, WebSiteAddress, SalesRep

1CustomerContactList: CustomerID (key), CompanyID, ContactFirstName, ContactLastName, ContactTitle, CompanyOrDepartment, Extension, MobilePhoneNumber, EmailAddress, Birthdate, Notes

 

[lukyhare]

I had another idea, would it be better to instead of having a combo box [Main contact ID] in the main form, i could have a check box called [Main Contact] in the sub form where each contact person for that company gets filed.

Is this an easier and better way to point out which contact is the main contact for each company? Also if this is a solution, does anyone know how i can make the check box so that it is limited to one contact person per company. Im thinking a type of check box that is limited to one, so if i click on another check box then the other box that was checked gets automatically un checked. Everyone lost yet?

Thanks,

Len

 

[joeselch]

Len - I'm afraid I misunderstood the situation. It appears that you have a one-to-many relationship between Customer Companys and Customer Contacts... disregard my comments re junction table.

You will need to decide whether you want to store the "main contact" info in the Company table or the Contacts table. (I'm not really sure which is most proper.)

If you use the Company table, you would add a field to store the Contact ID of the main contact. Then, you could add a combo box to the main form, based on a query of the Contacts table and use this to select the main contact.

If you use the Contacts table, you would add a field to store the designation of "main contact." Then, you would use the Contacts sub-form to enter/update the main contact designators - note that this will require code to ensure that no more than one contact per company is designated as the main contact.

 

[lukyhare]

joeselch,

Which do you think is easier? If I wanted to go with putting a new field in the company table, how do i set up the querry so that in the combo box it only allows you to choose the contacts relevant to that company.

Do you think the check box idea could work too? If i wanted to add a field in the Contacts table instead? Im not good with code so i think this would be the second option.

Thanks again for your help,

Len

 

[joeselch]

Len,

Here is an A2K example of the first option - storing the ID of the main contact in the Company table.

Again, I'm still not sure that this is the "correct" approach. I've applied referential integrity rules to ensure that you can't delete a contact if that contact has been designated as the main contact.

Note, that I've done nothing to ensure that a main contact is designated for each/every company - a company could be main contact-less.

Use the form "frmCustomerCompany" to see it in action. Poke around a bit - I hope it provides enough to get you on track with your own project.

 

[lukyhare]

Hey joeselch,

Thanks for the post and sample. I will take a look and see if i can get your sample to work with my form. Will let you know later about how it goes.

Best regards,

Len