Which event?

[jschping]

Hi,

I have a tab control on my main form, and each tab has a separate subform
inside it. Each of those subforms contains a its own subforms.

I have a combo box in the main form that selects a record. All of the
subforms are set to filter based on that choice.

Each subform has some formatting code that needs to run each time the user
changes the selection from the combo box. I only want it to run once to
reformat the subform based on its new filter.

Which event should I use to place the code? I am currently using the On
Current event for the subforms. But I think it runs twice. (I had placed a
message box to see when it ran, and the msgbox came up twice!!) I don't want
to waste time since it takes a little to run the code.

Thanks!

John Schping

 

[RonaldoOneNil]

The After Update event of the combo box

 

[Dirk Goldgar]

Hi,

I have a tab control on my main form, and each tab has a separate subform
inside it. Each of those subforms contains a its own subforms.

I have a combo box in the main form that selects a record. All of the
subforms are set to filter based on that choice.

Each subform has some formatting code that needs to run each time the user
changes the selection from the combo box. I only want it to run once to
reformat the subform based on its new filter.

Which event should I use to place the code? I am currently using the On
Current event for the subforms. But I think it runs twice. (I had placed
a
message box to see when it ran, and the msgbox came up twice!!) I don't
want
to waste time since it takes a little to run the code.

Why not use the AfterUpdate event of the combo box itself? Let each subform
have a Public procedure to do the formatting. Give all those procedures the
same name, say, "FormatForSubtype", and call them all in combo box's
AfterUpdate event. For example,

Private Sub cboYourCombo_AfterUpdate()

Me.sfSubform1.Form.FormatForSubtype
Me.sfSubform2.Form.FormatForSubtype
Me.sfSubform3.Form.FormatForSubtype
' ... and so on.

End Sub

Alternatively, if you don't want to have to make the main form aware of any
formatting needs of the subforms, you could have each subform record (in a
module-level variable), what state it's currently in. Then in the Current
event of the subform, you could check that variable to see whether the
subform needs to be reformatted. The reformatting code would only be run
when necessary.

 

[jschping]

Thanks for replying.

Where would I declare that variable? How would I set it the first time, and
where would I change its value?

If the user switches records via the combo box on the main form, will I need
to reset the variable using the combo box after update event?

Thanks!

 

[Dirk Goldgar]

Where would I declare that variable? How would I set it the first time,
and
where would I change its value?

In the code module of each subform (that is doing this sort of dynamic
formatting), you would declare a variable in the General/Declarations
section of the module; For example:

'----- start of code snippet -----
Option Compare Database
Option Explicit

Dim mvarFormat As Variant
'----- end of code snippet -----

Then in the Current event of the subform, you'd check the variable to see if
you need to reformat:

'----- start of code snippet -----
Private Sub Form_Current()

If Me.Parent!cboYourCombo = mvarFormat Then
' Do nothing; we're already formatted correctly.
Else
mvarFormat = Me.Parent!cboYourCombo

' Now format the form according to the value of mvarFormat.

' ... your code here ...

End If

End Sub
'----- end of code snippet -----

If the user switches records via the combo box on the main form, will I
need
to reset the variable using the combo box after update event?

When the user switches records via the combo box, the subform's Current
event will fire, and so the variable will be reset by the code in the
subform's Current event.

 

[jschping]

Excellent! Thanks so much!

In the code module of each subform (that is doing this sort of dynamic
formatting), you would declare a variable in the General/Declarations
section of the module; For example:

'----- start of code snippet -----
Option Compare Database
Option Explicit

Dim mvarFormat As Variant
'----- end of code snippet -----

Then in the Current event of the subform, you'd check the variable to see if
you need to reformat:

'----- start of code snippet -----
Private Sub Form_Current()

If Me.Parent!cboYourCombo = mvarFormat Then
' Do nothing; we're already formatted correctly.
Else
mvarFormat = Me.Parent!cboYourCombo

' Now format the form according to the value of mvarFormat.

' ... your code here ...

End If

End Sub
'----- end of code snippet -----


When the user switches records via the combo box, the subform's Current
event will fire, and so the variable will be reset by the code in the
subform's Current event.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)