Pick customer contact 1

[DoctorJDM]

Have a Company form where contacts are entered on a subform.

On a Jobs form containing the Company field I'd like to be able to pop up a list of the contacts for that company, pick one and enter it onto the Jobs form.

When new jobs are created for a company the relevant contact for each job could then be assigned for that job.

 

[Remou]

It setting the combo as one of the link fields for the subform an option for you? Alternatively, you could update the subform in a suitable event. Finally, why not include a combo listing cotacts as the row source in the subform? The combo could be bound to the subform contact field. Or have I mistaken the problem?

 

[DoctorJDM]

... sorry, adding to this I've created a listbox on frmJobs that lists the contacts for the particular company.
The SQL for the rowsource is this

***********

SELECT tblCustomerContacts.ContactName, tblCustomerContacts.Title, tblCustomerContacts.Customer
FROM tblCustomerContacts
WHERE (((tblCustomerContacts.Customer)=[forms]![frmJobs].[Customer]))
ORDER BY tblCustomerContacts.ContactName;

***********

I've then used the list box's AfterUpdate to enter the selected Customer Contact into frmJobs.

Problem is the list box contents don't change when moving from job to job.

 

[Remou]

If I understand you, you need to requery the listbox in the Current Event of the form.

 

[DoctorJDM]

Thanks Remou, just right and easy when you know how.