Hi,
I am setting up a database which so far has a Client table, Contacts table and Job table.
Clients can have many Jobs and many Contacts, each Contact can have many Jobs but only one Client, each Job can only have one Client and one Contact.
In the Contacts table you can select the Client that the Contact refers to through a dropdown box.
In the Jobs table you can select the Client that the Job refers to through a dropdown box also.
I would like then to be able to select the Contact for the Job from a dropdown box, but only display Contacts that relate to that particular Client.
I tried creating a new field Job.JobContact and made it a list box with the following expression:
SELECT [Contacts].[ContactFirstName] & " " & [Contacts].[ContactLastName]
FROM Contacts
WHERE ((([Job].[JobClient])=([Contacts].[ContactCompany])));
I am only just learning Access so this might be wrong.
Every time I try and select the dropbox box it says "Enter Parameter Value"
Might this has something to do with the fact that the field for Job.JobClient is a lookup AND the field for Contacts.ContactCompany is also. When I look at these in DesignView it states that each field is a number (due to the lookup) - Would the "number" be the same between the different tables even though relating to the same overall Client?
Or is there a better way to do this?
HELP!