Set an Event Procedure to User Procedure

[Cory]

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

 

[Jeff Boyce]

"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

 

[Marshall Barton]

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

 

[Cory]

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