Subform not updating in parent form

[Kennelbloke]

I have a form that has a subform attached that has some weird behavioural problem basically when I click on a Prefix field and using onclick and a requery it should update the subform. This works in one instance but not for others. When I open the subform on it's own it shows the correct data for, in this instance all 4 prefixes, but only one instance, Ewenique, it shows in the subform (in the mainform) working correctly. The Link field is the PrefixID. I have tried linking with both the PrefixID and the BreederID but no difference.

the subform sql is

SELECT tblCatDetails.OwnerID, tblCatDetails.PrefixID, tblCatDetails.BreederID, tblCatDetails.CatID, tblCatDetails.CatName, tblCatDetails.DateofBirth, tblSexType.Sex, tblCatBreeds.Breed, tblBreedColours.Colour, tblCatDetails.RegistrationNumber
FROM tblCatBreeds RIGHT JOIN (tblSexType RIGHT JOIN (tblBreedColours RIGHT JOIN tblCatDetails ON tblBreedColours.ColourID = tblCatDetails.ColourID) ON tblSexType.SexID = tblCatDetails.SexID) ON tblCatBreeds.BreedID = tblCatDetails.CatBreedID
WHERE (((tblCatDetails.PrefixID)=forms!frmPrefixes!lstPrefixes) And ((tblCatDetails.BreederID)=forms!frmPrefixes!BreederID))
ORDER BY tblCatDetails.CatName;

The form areas are below.





Would anyone have any idea why I'm getting this behaviour? Maybe I'm missing something. TIA

 

[dhookom]

Why all of the tables in your query. Typically the lookup tables aren’t included in the recordsource. Use combo boxes to display the descriptive fields.

 

[Kennelbloke]

The user doesn't enter or edit data on this form. The form opens with the BreedersID from their details form. All I want them to do is select a prefix on the side to see which cats belong to that prefix. They can double click on the cat to open that record if they want.
It works for one prefix not the other 3. I can open the subform independently for each prefix and it shows the cat records. It just doesn't do it for all when it's subformed