ComboBox Doesn't Display Some Values

[atrodden]

Hello,

In a form used to record a sale for a company we have a dropdown box with the contact names for that company and when one is selected it populates other boxes like phone number etc however the combobox brings up all possible contacts but when some of them are selected all the boxes return blank, including the combobox, whereas most of the contacts work fine and I have no idea why.

The SQL used for the combobox is as follows..

SELECT tbl_Contacts.ContactTelephone, tbl_Contacts.ContactMobile, tbl_Contacts.ContactEmail, tbl_Contacts.ID_Contact, tbl_Contacts.ID_Company, tbl_Contacts.Salutation & " " & tbl_Contacts.ContactForename & " " & tbl_Contacts.ContactSurname AS MainContact
FROM tbl_Contacts
WHERE (((tbl_Contacts.ID_Company)=[tbl_CompanyBookings].[ID_Company]))
ORDER BY tbl_Contacts.ContactForename;

Anyone got any idea why some contacts work and some don't ??

 

[Mihail]

Try to establish the Referential Integrity between tables (Relationships window).
What Access say ?

 

[atrodden]

I tried to enforce the referential integrity but it says "No Unique Index found for the reference field of the primary table"

The are linked on ID_Company but this isnt the primary key for the table

 

[Mihail]

So, now you have the answer to your original question

 

[atrodden]

Ok so how can I make this work...

I have three tables...
tbl_Company - Primary Key ID_Company
tbl_CompanyBookings - Primary Key ID_Booking
Tbl_Contacts - Primary Key ID_Contact

They are all linked via ID_Company

 

[atrodden]

also the ComboBox works perfectly for some companies and not at all for others... :banghead:

 

[Mihail]

Your combo is OK.
Just Access can't find the related data because... don't exist.
Try to find yourself, manually, what data is related to those IDs that don't work.

 

[atrodden]

I appreciate your help but I am very confused, all the data does exist, when I run the query (Manually typing in a Company ID) it brings up all the data I'm looking for.