Refer to A Private sub

[dgaller]

First, I must apologize I am a novice and may not have all the correct terminology, but I should be able to get my question accross.

I have a form with 4 combo boxes (Combo150, Combo170, Combo158 and 160) that are used to filter a sub form, which works fine as long as I filter in order. What I would like to do is add a line in say Private Sub Combo160_AfterUpdate() that if Combo160 is null run the code for Combo170_afterupdate().


This seems as though it would be easier that coding every possibility in each Combo Afterupdate.

 

[MSAccessRookie]

First, I must apologize I am a novice and may not have all the correct terminology, but I should be able to get my question accross.

I have a form with 4 combo boxes (Combo150, Combo170, Combo158 and 160) that are used to filter a sub form, which works fine as long as I filter in order. What I would like to do is add a line in say Private Sub Combo160_AfterUpdate() that if Combo160 is null run the code for Combo170_afterupdate().


This seems as though it would be easier that coding every possibility in each Combo Afterupdate.

I believe that you can actually do that if you want to, but you might want to consider adding a Common Subs/Functions Module. You could then have each AfterUpdate Procedure call whichever procedures or functions that it requires.

As an aside, I do not like to use Generic control names, and prefer to rename all of mine to have a name that describes their purpose as often as possible. The program may get completed, but I never know when I will need to come back to repair/enhance a part of it, and I want to be able to know what each control is for without spending too much time and effort

 

[dgaller]

What you are saying is I can make a public sub and put all the code written to private subs in there. Correct?

If that is the case then how do I activate that after update of the combo box? Currently I goto the properties and assign an event proceedure to the after update and it makes it a private sub. How do I assign it to the Public Sub?

 

[MSAccessRookie]

What you are saying is I can make a public sub and put all the code written to private subs in there. Correct?

If that is the case then how do I activate that after update of the combo box? Currently I goto the properties and assign an event proceedure to the after update and it makes it a private sub. How do I assign it to the Public Sub?

What I meant was

• Create Public Functions/Subs that are needed and store in a Common Module
• Create Private Event Procedures for any controls that need events.
• Have the Private Event Procedures call the Public Functions/Subs as required

This will only be necessary if a procedure or function becomes common to multiple Control related events.

 

[dgaller]

I think what I am missing is how to call the public from the private?

 

[boblarson]

One thing that wasn't stated is that you can take your combo's AfterUpdate event and make it Public (just don't name any other combos the same name). Just change the word Private to Public on the AfterUpdate event and then you can call it from your other combo's event like this:

Public Sub MyComboName_AfterUpdate()
    Whatever....
End Sub

Private Sub MyOtherComboName_AfterUpdate()
   MyComboName_AfterUpdate
End Sub

 

[Banana]

It'd be something like this:

Public Sub CallMe()

End Sub


Private Sub Caller()

CallMe

End Sub

Some folks like to add this:

Call CallMe

so they know they're calling a public procedure.

BTW, to be clear, you can call a private sub in *same* module, but not a private sub in *another* module. Public subs, obviously are available to all modules.


Bob- Good point, but I am sure you don't have to make it public if the other combo calling it is from the same module...

 

[MSAccessRookie]

I think what I am missing is how to call the public from the private?

Sorry, I assumed you knew.

Below are two examples. from my code, as well as the method of calling them.

Example 1 (Function to set Form BackGround Color Attribute)

Public Function SetFormBackColor() As Variant 
    If Forms!fmrWorkorders!ProgramMode = "Production" Then
        SetFormBackColor = 12632256
    ElseIf Forms!fmrWorkorders!ProgramMode = "QA Testing" Then
        SetFormBackColor = 5327805
    ElseIf Forms!fmrWorkorders!ProgramMode = "Development" Then
        SetFormBackColor = 16705454
    End If
End Function

Example 2 (Procedure to set Form Title String Attribute)

Public Sub SetFormTitle(TitleString As String) As String 
    SetFormTitle = TitleString + " - UserName: " & fOSUserName() & _
             " - " & Forms!fmrWorkorders!ProgramMode & " Version"
 
End Sub

They are called in the On Load Event of a form as follows:

Private Sub Form_Load()
    SetFormTitle("Monthly Report of Transactions")
    Me.FormDetail.BackColor = SetFormBackColor()
End Sub

 

[dgaller]

GO IT! Thank you all.

 

[boblarson]

Bob- Good point, but I am sure you don't have to make it public if the other combo calling it is from the same module...

Yeah, shows you how often I do it, eh? Often enough to forget how to do it

 

[Banana]

Often enough to forget how to do it

That'd make for a great t-shirt.

I've got plenty of things I've done often enough to forget how to do it.