John W Colby
jwcolby at gmail.com
Thu Mar 13 07:50:05 CDT 2014
I tend to use a wrapper class for this. If an object is "dependent" on another object then whenever the parent object changes, the child object needs to be requeried. Yes, this can be done completely manually, but can also be done completely automatically. The concept is to have a class which wraps (for example) the combo box. That class then sinks the afterupdate event (and others as required). It also has a collection which holds a pointer to other objects which need to be requeried if this object needs to be requeried. And finally, it has a Requery method which, when called iterates the collection requerying all of the objects in the collection. Notice that the collection stores wrapper classes, NOT access controls. The wrapper classes hold the control. For example: State City High School Class Persons Any time the state changes, the city is requeried (and thus changes). Any time the City changes it requeries the the High School object (and thus changes). And so forth down the chain. Notice that the AfterUpdate could occur at any level down the chain, and the child combos of THAT OBJECT would all need to be requeried in the correct order - down the chain. So you have a query for each combo which has a where clause using the parent combo to filter its records. A combo class instance is created wrapping each combo. The State is the top most parent. Its collection gets a pointer to the city combo class. The city combo class gets a pointer to the High School combo class, which gets a pointer to the class combo class etc. Now, when the AfterUpdate event fires for any of the combos, it is sunk in the appropriate wrapper class, which grabs the object(s) in its collection and calls the requery method. The requery method of the wrapper class requeries its combo and then grabs the objects in its collection doing the same thing. The requeries just automatically ripple down the chain, requerying every child combo. Notice too that other objects could be dependent on the parent. You could have a subform which pulls records (or views) based on a combo, or even vice versa, combos which change depending on the form. Using a collection of dependent objects allows you to have multiple dependent objects, and they all get requeried when the class requery method is called. The child chain can be arbitrarily complex and since the process is fully automatic it will just happen, every time, regardless of where in the chain the AfterUpdate (requery trigger event) happens. Every child object will get requeried. John W. Colby Reality is what refuses to go away when you do not believe in it On 3/13/2014 7:11 AM, David A Gibson wrote: > I have a DB that uses cascading listboxes to drill down State > County > > City > High School > > I use the AfterUpdate event to clear the listboxes > > Private Sub List30_AfterUpdate() > > Me!List32 = Null > Me!List32.Requery > > Me!List34 = Null > Me!List34.Requery > > End Sub > > Glad I was able to help lessen your pain. > > David Gibson > > > --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com