Bill Benson
bensonforums at gmail.com
Thu Mar 13 08:03:54 CDT 2014
John why is it any less painful writing code that instantiates class objects at runtime (Form_Load?) And adds them to a collection, with code in the class events to look for every child of that object to do a re query.... than it is to simply write a line of code within each object's re query event to re query each child object (mentioned specifically by name). Isn't it taking the same amount of time to type the different code (your way) versus the standard way... ? Now another question to show how ignorant I can be, don't collection objects and classes have the potential to go out of scope in ACCESS VBA? I know they do in Excel, maybe not Access. One thing I will give you props for is the control events sometimes get detached from the objects and your method might avoid that possibility. Bill On Mar 13, 2014 8:52 AM, "John W Colby" <jwcolby at gmail.com> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >