Is there a way to have a calculated field in a form that counts child records for each parent record? (Please don't snap at me for asking a question that I've seen many others ask. I've Googled it enough before posting here and I still can't find what I'm looking for. I have also tried various VBA and query experiments to get the end result I'm looking for without success.)
What I'm trying to achieve is, I want to use conditional formatting to highlight the records in subform1 which have zero child records in subform2, so that's what I was trying to use the calculated field for. The highlighted records in subform1 will indicate to users the records that are missing child records in subform2.
The closest thing I got was to link the query1 for subform1 to an aggregate query that contained the counts of the child records. That sort of worked because I was able to see if a record had zero or more child records, but then I wasn't able to edit the query. That may be due to the aggregate query.
To keep this simple, let's say this database is to keep track of dogs per houses per street. Every house is supposed to have at least 1 dog. Some might have 20 dogs. If a user has added a new house, they have to then edit the house, and they might forget to add the dogs last, especially if they added 20 houses in a batch. I want the users to see which houses are missing dogs, so they can add the dogs.
Main Form: Streets
Subform1 and Subform2 are side by side on Main form.
Subform1: Houses
Subform2: Dogs
What I'm trying to achieve is, I want to use conditional formatting to highlight the records in subform1 which have zero child records in subform2, so that's what I was trying to use the calculated field for. The highlighted records in subform1 will indicate to users the records that are missing child records in subform2.
The closest thing I got was to link the query1 for subform1 to an aggregate query that contained the counts of the child records. That sort of worked because I was able to see if a record had zero or more child records, but then I wasn't able to edit the query. That may be due to the aggregate query.
To keep this simple, let's say this database is to keep track of dogs per houses per street. Every house is supposed to have at least 1 dog. Some might have 20 dogs. If a user has added a new house, they have to then edit the house, and they might forget to add the dogs last, especially if they added 20 houses in a batch. I want the users to see which houses are missing dogs, so they can add the dogs.
Main Form: Streets
Subform1 and Subform2 are side by side on Main form.
Subform1: Houses
Subform2: Dogs