Filter Contacts based on Selected Customer

[davecumber]

I have a form where users can select a company and a contact. At the moment the fields are just lookups of the relevant tables. How could I filter the contacts field to only show contacts that are linked to the selected company?



Thanks

Dave

[pradeep.sands]

Please provide more information regarding your database

[TG_W]

Without knowing more about your database, you could limit your query by referencing the company combo box on the form, assuming that's what you are using to call your data. In your query, add [Forms]![YourFormNameHere]![CompanyComboBoxNameHere], and that should limit the contacts you can select.

[davecumber]

Thanks for the answers. The db is a basic CRM, I have a table called companies, and table called contacts with a linked field to say which company they work for. We then record contacts with them. What I want to do is:

When a user selects a company in the contact form, the contacts list is shortened to just those that are linked to the selected company.

TG_Ws suggestion has worked, I set the form to refresh after update on the companies field and put where contacts.company = [forms]![interactions]![customer] in the contacts data source.

I was put off for a while as the refresh seems to wipe the contact out of all other entries as the list of contacts no longer contains the contact selected against other companies but closing and reopening the form rectifies this.

[TG_W]

You could also add a "Refresh" command button that will open and close the form for you, or just requeries/refreshes it.

To open and close - DoCmd.Close acForm, "Your Form Name Here"
DoCmd.OpenForm "Your Form Name Here"

Refresh - Me.Refresh

Requery - Me.Requery

[davecumber]

Thanks again. Not sure if I can push you for one more question. The above all works on a normal form but I later place this form as a subform inside another. The query doesn't seem to work there, I get the pop up asking me to enter a value for forms!Interactions!Customer - is there a change I can make to get around this?

[davecumber]

Found a solution:

I needed to edit the query a little to work in the main form
From
where contacts.company = [forms]![interactions]![customer]

To
where contacts.company = [forms]![MainCustForm]![Child47].[form]![Customer]

Where 'MainCustForm' is the master form and 'Child47' is the name of the subform object within the master form.

Thanks again for your help TG_W

[TG_W]

Yeah, that slight change once you make it a subform can throw you at first. Glad I could help.