Solved Use a calculated field to count child records for every parent record (1 Viewer)

[autumnnew]

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

 

[MajP]

You can use a Dcount in your query
Select PK, Field1, field2, Dcount("*","SomeChildTable","SomeForeignKey = " & [Pk]) as CountChildRecords from someTable

 

[autumnnew]

You can use a Dcount in your query
Select PK, Field1, field2, Dcount("*","SomeChildTable","SomeForeignKey = " & [Pk]) as CountChildRecords from someTable

Ugggghhhh. I just got it to work. Thanks! I had tried that, too, but I kept getting an error message. Apparently the issue was I didn't use the correct placement of quotes and brackets in the criteria, and I didn't know I was supposed to use the & [PK] outside of quotes. I wasted too much time today on this.

Thanks again!

 

[MajP]

I wasted too much time today on this.

Come here early. Sometimes it just takes a second set of eyes to see a mistake such as proper quotes.

 

[RogerCooper]

If you just need to know which houses have no dogs, you don't need to count. You can just used an unmatched query (left join selecting for null on the right table).

 

[Gasman]

Ugggghhhh. I just got it to work. Thanks! I had tried that, too, but I kept getting an error message. Apparently the issue was I didn't use the correct placement of quotes and brackets in the criteria, and I didn't know I was supposed to use the & [PK] outside of quotes. I wasted too much time today on this.

Thanks again!

Put your criteria into a string variable. Then you can debug.print it until you get it correct. When correct, you can use that variable in the function

 

[autumnnew]

Put your criteria into a string variable. Then you can debug.print it until you get it correct. When correct, you can use that variable in the function

Good idea.. I'll certainly do that next time!

If you just need to know which houses have no dogs, you don't need to count. You can just used an unmatched query (left join selecting for null on the right table).

Yes, but I don't want to do that for several reasons, and the main reason is I want to use the field in the query that the Houses subform is based on for conditional formatting. I want the form to visually indicate to users which houses need more dogs. If I can put it in a calculated field rather than create another query, then I'd rather do that, because I'm trying to keep from creating too many queries (I already have enough). I've accomplished the calculated field and it works like a charm now. Maybe an even better solution would be to have vba code assign the value to a text box on the [continuous] form.. I dunno if that would be faster than a calculated field.

Also, I forgot to say that the number of dogs is proportionate to the size of the house. So if a user changes the size of the house, then some dogs need to be added or removed. Therefore, a house may already have dogs and still needs more dogs (sounds like my kinda house!). So a find unmatched query would only give me a portion of the right results.

So my calculated field determines which houses needs more dogs based on 2 conditions:

1. the house has zero dogs - needs dogs.
2. the house got bigger or smaller - may need more or less dogs.

Whenever either of these conditions are true, the respective houses will highlight to red, and a red button and red text appear saying 'add more dogs'. The button runs VBA code that will add or remove dogs based on the information of each house. When the above conditions are both false, the red highlight, button and text go away.

 

[MajP]

Maybe an even better solution would be to have vba code assign the value to a text box on the [continuous] form.. I dunno if that would be faster than a calculated field.

That cannot be done. If you change the value of an unbound textbox in a continuous form all instances of that textbox change.