Counting Related Records - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Counting Related Records

Kyle Rapp

2 years ago

Hey guys, I'm stuck on something and hoping someone can help me figure this out.
I have a customer table and a vehicles table.  Customers can own more than 1 vehicle so there is a one-many-relationship.
I have a status field (active or inactive) on both the customer record and the vehicles record.

In the customer form's OnCurrent and BeforeUpdate events I set a number field called 'ActiveVehicleCount' equal to a Dcount of active vehicles that are related to the customer in focus.  In this case, I'm in the customer record, updating a field on the customer record, based on the count of related records.  The Dcount works perfectly.
The issue is that if someone goes into the related vehicle record and marks the vehicle as 'inactive', the 'ActiveVehicleCount' does not get updated unless I open or edit and save the customer record.  Makes perfect sense, since I only have the Dcount in the Customer form's events.

Does it make sense to perform a similar Dcount in the vehicle form's events?  For example, count vehicles where me.VehicleOwner = customers.CustomerName and me.vehiclestatus = 'active'.
(I'm not using the actual code syntax, just trying to make my explanation easier to follow.

Basically, the count needs to be recalculated whenever either the customer or vehicle record is changed.  And if a new customer or vehicle record is changed the count needs to be updated.

Thanks,
Kyle

	Adam Schwanz @Reply 2 years ago
Add in the after update event of your "Active/Inactive" field to FieldName.Requery the dcount textbox

Kyle Rapp OP @Reply

2 years ago

Hi Adam, to make sure I understand:  in the AfterUpdate event of the Status field I would requery the activevehiclecount field correct?  If so, does the requery command just force it to run the DCount again?

Also there are other fields that are used as additional criteria so I don't think I can do it based on only the status field's AfterUpdate.  Would I just do the requery for each of the fields used as criteria?  (meaning, if any of the fields are updated, it updates the count)

Last question on this...does AfterUpdate for a field get triggered when its a new record?

Thanks,
Kyle

	Adam Schwanz @Reply 2 years ago
Yes, requery just redoes the dcount calculation. Field after-update events still trigger on new record One thing to note is if you do not save the record and it's still dirty, the dcount might still not be right, you'll have to test that, you might need to add a me.refresh above the field.requery

Kyle Rapp OP @Reply

2 years ago

Great! I'll give it a shot. I recall Richard's video for counting orders on the customer form, which was done by creating an unbound textbox and setting the ControlSource to the Dcount. I was tempting to do something like that but was worried that since the value would not be stored in the table, the filter may not work? In otherwords, if I use a DCount as the controlsource of a textbox, would I still be able to filter that form by the value in that control? What I'm really after here is the ability to filter the customer form by active customers who have at least 1 active vehicle but for the count to be always up to date, regardless which record was added or changed.

	Adam Schwanz @Reply 2 years ago
I would put the dcount inside of a query instead that the field is based off of, then you could search/filter off that field in the query and set your textbox to that control source, instead of trying to filter off the value in an unbound field. I would suspect it would also automatically update then, if not you could still use requery.

	Kyle Rapp OP @Reply 2 years ago
Great, thank you so much Adam. I'll give that a try. Kyle

Kyle Rapp OP @Reply

2 years ago

Hey guys, still not able to get this working. Any additional help would be appreciated. I have a query that works great. It lists the customerID and customer name for each active customer that has at least 1 active vehicle. It also provides the count of active vehicles for each customer in the list. I have a button on the customer form that should filter the customer form to show only the customers from that query. I am using docmd.setfilter "queryname" but its not working. I open the form, I can see all records. I click the button and it asks for a parameter. The weird thing is its using a parameter I created as part of a macro that I'm using for a button on my main menu form. That form and macro are not specified anywhere in the customer form's button or in its onload, oncurrent or other events. I'm really at a loss at this point.

Kyle Rapp OP @Reply

2 years ago

Hey guys, so I have this working with a workaround in place, but I'm sure there's a better way to do this.  Here is where I'm at:

Let's say I change a vehicle status to inactive and save the vehicle record.  The next time I open or close the related customer record, the active vehicle count for that customer will be updated because I'm setting the value of that field using a Dcount on the form's OnCurrent and BeforeUpdate events.

So, after changing the vehicle record, in order for the count to update, the user has to remember to open the customer record.  The workaround for now is I put some VBA code in the AfterUpdate event of the Vehicle form to open the customer form to that customer record and then close the form.  I'd rather just force a requery of the DCount in the related customer record, but I'm not sure if that can be done from the afterupdate event of a form that is bound to different table.   Any ideas?

	Adam Schwanz @Reply 2 years ago
Can you upload some images of what's going on? You should just be able to requery the field regardless of the bound table. But maybe I'm not seeing something.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View