Set an Event Procedure to User Procedure Cory 10/8/2008 6:38:25 PM Using Access 2007 on Vista Home Premium. I have a procedure called Build_Form, which creates a new form at runtime and populates it with a tab control. The tab control has multiple pages and each page has some labels and combo boxes. My goal is to get the value of the combo box before and after it is updated by the user so that I can find and update a record(s) in a table. I have set the BeforeUpdate and AfterUpdate events for all of the combo boxes to run custom functions as follows: cmbTemp.BeforeUpdate="=Combo_BeforeUpdate()" cmbTemp.AfterUpdate="=Combo_AfterUpdate()" Since the Events will be running outside of the actual form, I can't use something simple like Me.cmbComboName.Value to get the value from the combo box. The only way that I've come up with to get the values is to go through each combo box on the form and put their values and names into an array both before and after update then compare to see what changed. Does anyone have a more elegant solution? Before someone suggests that I create a form in design mode, save it, and then simply change the visible property of controls as they are needed at runtime, I have chosen to do it this way for good reason. Let's just assume that designing and saving a form isn't an option. Thanks in advance for any help. -Cory

Re: Set an Event Procedure to User Procedure "Jeff Boyce" <nonsense[ at ]nonsense.com> 10/8/2008 7:14:22 PM "Trust me, I know what I'm doing." You may know your reasons, and they may be good reasons. If they are, don't you think the rest of us might benefit from learning about a (?new?) situation in which this approach is more applicable than designing a form and turning controls off/on? If you need to see/get the values on a form from outside the form, consider using something like: Forms!YourFormName!cboYourComboboxName to refer to the control(s). Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Cory" <Cory[ at ]discussions.microsoft.com> wrote in message news:B7E8F4A5-93C4-4C79-BD57-783451B041FA[ at ]microsoft.com... [Quoted Text] > Using Access 2007 on Vista Home Premium. > > I have a procedure called Build_Form, which creates a new form at runtime > and populates it with a tab control. The tab control has multiple pages > and > each page has some labels and combo boxes. My goal is to get the value of > the > combo box before and after it is updated by the user so that I can find > and > update a record(s) in a table. > > I have set the BeforeUpdate and AfterUpdate events for all of the combo > boxes to run custom functions as follows: > cmbTemp.BeforeUpdate="=Combo_BeforeUpdate()" > cmbTemp.AfterUpdate="=Combo_AfterUpdate()" > > Since the Events will be running outside of the actual form, I can't use > something simple like Me.cmbComboName.Value to get the value from the > combo > box. The only way that I've come up with to get the values is to go > through > each combo box on the form and put their values and names into an array > both > before and after update then compare to see what changed. Does anyone have > a > more elegant solution? > > Before someone suggests that I create a form in design mode, save it, and > then simply change the visible property of controls as they are needed at > runtime, I have chosen to do it this way for good reason. Let's just > assume > that designing and saving a form isn't an option. > > Thanks in advance for any help. > > -Cory

Re: Set an Event Procedure to User Procedure Marshall Barton <marshbarton[ at ]wowway.com> 10/8/2008 9:07:37 PM Cory wrote: [Quoted Text] >Using Access 2007 on Vista Home Premium. > >I have a procedure called Build_Form, which creates a new form at runtime >and populates it with a tab control. The tab control has multiple pages and >each page has some labels and combo boxes. My goal is to get the value of the >combo box before and after it is updated by the user so that I can find and >update a record(s) in a table. > >I have set the BeforeUpdate and AfterUpdate events for all of the combo >boxes to run custom functions as follows: > cmbTemp.BeforeUpdate="=Combo_BeforeUpdate()" > cmbTemp.AfterUpdate="=Combo_AfterUpdate()" > >Since the Events will be running outside of the actual form, I can't use >something simple like Me.cmbComboName.Value to get the value from the combo >box. The only way that I've come up with to get the values is to go through >each combo box on the form and put their values and names into an array both >before and after update then compare to see what changed. Does anyone have a >more elegant solution? > >Before someone suggests that I create a form in design mode, save it, and >then simply change the visible property of controls as they are needed at >runtime, I have chosen to do it this way for good reason. Let's just assume >that designing and saving a form isn't an option. You will never be able to make your app into an MDE and have a wide open door for corruption, require frequent Compacts and need a robust recovery procedure. Instead of using Me in the event procedures, set the event properties to: cmbTemp.BeforeUpdate= "=Combo_BeforeUpdate(Form)" and change each procedure to have an argument like Public Sub Combo_BeforeUpdate(frm As Form) x = frm.Combo.Value -- Marsh MVP [MS Access]

Re: Set an Event Procedure to User Procedure Cory 11/24/2008 4:53:02 PM Marshall, Thanks for your post. Passing a parameter in the event procedure didn't even cross my mind until my wife mentioned it. I gave it a try and it worked. Thanks again. -Cory "Marshall Barton" wrote: [Quoted Text] > Cory wrote: > > >Using Access 2007 on Vista Home Premium. > > > >I have a procedure called Build_Form, which creates a new form at runtime > >and populates it with a tab control. The tab control has multiple pages and > >each page has some labels and combo boxes. My goal is to get the value of the > >combo box before and after it is updated by the user so that I can find and > >update a record(s) in a table. > > > >I have set the BeforeUpdate and AfterUpdate events for all of the combo > >boxes to run custom functions as follows: > > cmbTemp.BeforeUpdate="=Combo_BeforeUpdate()" > > cmbTemp.AfterUpdate="=Combo_AfterUpdate()" > > > >Since the Events will be running outside of the actual form, I can't use > >something simple like Me.cmbComboName.Value to get the value from the combo > >box. The only way that I've come up with to get the values is to go through > >each combo box on the form and put their values and names into an array both > >before and after update then compare to see what changed. Does anyone have a > >more elegant solution? > > > >Before someone suggests that I create a form in design mode, save it, and > >then simply change the visible property of controls as they are needed at > >runtime, I have chosen to do it this way for good reason. Let's just assume > >that designing and saving a form isn't an option. > > > You will never be able to make your app into an MDE and have > a wide open door for corruption, require frequent Compacts > and need a robust recovery procedure. > > Instead of using Me in the event procedures, set the event > properties to: > cmbTemp.BeforeUpdate= "=Combo_BeforeUpdate(Form)" > and change each procedure to have an argument like > > Public Sub Combo_BeforeUpdate(frm As Form) > x = frm.Combo.Value > > -- > Marsh > MVP [MS Access] >