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

 

[Norman Sherlock]

Since it works with 18195 but not the other three...it looks like it will find 18195, but just does not find 20427, 20426, and 20266.

I would start by examining the data types of lstPrefixes and PrefixID. If those match perfectly, then I'd examine the contents of lstPrefixes to see if there is something different between 18195 and the other three. Then repeat this with PrefixID. Does the data contain extraneous spaces, for example.

 

[dhookom]

Are you using the link master child? This is the recommended method which allows you to remove the WHERE clause from your recordsource.

 

[BFroeschlKC]

I think you need to tell the subform to requery after selecting a prefix or breeder. The subform has no idea that the main form changed values that affect it's internal query.
It works when you open the subform because then the subform's query is run again.

I always use the "code behind" (not macros). It would look something like "Me.subForm1.Requery" in the "AfterUpdate" event of the "lastPrefixes" control. Ditto for the Breeder control.

But you also have a problem with your SQL. You are using Right Join which says "give me all the records from the left side and make an empty record on the right side if one does not exist". Then you constrain the right side of the query which basically says, "never mind on the right join, I only want records with a values." I think you wanted to start with tblCatDetails, then do right joins to your "lookup" tables: tblCatBreeds, tblSexType and tblBreedColours.

 

[dhookom]

If this were my application, I would get rid of the lookup tables and replace with combo boxes. Also use only Link Master child to the two fields from tblCatDetails.

 

[Kennelbloke]

Sorry guys, have to do other things for the next few days will check your responses later and get back to you thanks so far

 

[Kennelbloke]

Ok I'm back. Reading through the much appreciated replies the following applies.

Affectively the prefixes on the left are just the filter for the subform.
Every time a prefix is clicked on, the subform is requeried (i.e. Me!sfrmBreedersCats.requery)
I have removed the BreederID from the query to simplify (when I thought about it, it wasn't needed as the prefixid is already bound to the cat details)

Hopefully the attached video explains better.
View attachment bandicam 2024-12-04 10-15-32-896.mp4

 

[dhookom]

It looks like you have the link master/child set to the PrefixID. There is no reason to set this in the recordsource.

 

[Kennelbloke]

YES!!!! That was it Duane. Thank you so much. Removed the Link Master/Child fields for the subform.

One headache out of the way