Make Code Available to All Forms (Remove Duplication of code)

[Gaddy]

Hi All,

I have several forms which are basically a copy of one another, the only difference is is that each one has a combo box or two missing, and or a variation of buttons. I also have some simple vba code I want each of these forms to use. At the moment I have the code on each form, but if I want to make a change to the code I will have to repeat the change on every form.

How do I make the code below available to all forms (or even a selection of forms) and only to exist once, i.e. not in every form?

Here is my code:

Private Sub cboLocationID_Change()
Me.cboManagerID.Requery
Me.cboManagerID = ""
End Sub

Many thanks

 

[pbaldy]

Not the type of code I'd do it with, but for the sake of argument you could create a public function that accepted the form and control names as inputs.

 

[Gaddy]

Hi there,

Do you have any suggestions (I'm new to database design and VBA)?

Do I just swap out, 'Public Sub' with 'Public Function' - but then where do I put the code and how do I link it to the forms?

For instance I have these forms:

frmDevAddUser
frmEditUser
fmrAddUser

 

[way2bord]

Hi there,

Do you have any suggestions (I'm new to database design and VBA)?

Do I just swap out, 'Public Sub' with 'Public Function' - but then where do I put the code and how do I link it to the forms?

For instance I have these forms:

frmDevAddUser
frmEditUser
fmrAddUser

Create a new Module

(more realistic) pseudocode:

Public Function MyOnChangeEvent()
 with Forms(Application.CurrentObjectName) 
  .Controls("cboManagerID").Requery
  .Controls("cboManagerID") = ""
 end with
End Function

In every form object that you want to use this, you'd add the following to the "On Change" property =MyOnChangeEvent()

 

[pbaldy]

Start here:

http://www.baldyweb.com/Function.htm

Way2Bord is too bored, because Me won't work in a module. If you passed the 2 as strings:

Forms(FormVariable)(ControlVariable).Requery

Depending on your situation, you might also be able to use ActiveForm/ActiveControl.

 

[Gaddy]

Pardon my ignorance but I cannot see an "on change" property in the forms properties.

To clarify is it a class module or regular module that I create?

EDIT: Tried both and for anyone else like me, it's a regular module.

Thanks for the link pbaldy, I've now named, the module something different to the function name.

It works, but it's complaining because of the 'Me' bit as you mentioned..

 

[nanscombe]

Way2bord has now changed his code in post 4. It now seems to function properly, if you care to retry it.

 

[Gaddy]

Thanks to all.

I've used Way2bord's code and have tagged the module to the 'After Update' event on a certain combo box on each form which has worked for me.

 

[Galaxiom]

If you passed the 2 as strings:
Forms(FormVariable)(ControlVariable).Requery

Rather than passing the names as strings a reference to the objects can be passed. In this example (I also acknowledge it as trivial) the only thing that needs to be passed is the Control object.

 

[Galaxiom]

I have several forms which are basically a copy of one another, the only difference is is that each one has a combo box or two missing, and or a variation of buttons.

In that case I would seriously consider having just one form and modifying its instances as required.

Then the code could remain contained in its module without duplication.

 

[pbaldy]

Rather than passing the names as strings a reference to the objects can be passed. In this example (I also acknowledge it as trivial) the only thing that needs to be passed is the Control object.

Good point; better point is probably the second one, that there may not even be a need for multiple forms.

 

[Gaddy]

In that case I would seriously consider having just one form and modifying its instances as required.

Then the code could remain contained in its module without duplication.

Thanks.

I've deleted the other forms - I think I'll go another route with them.

What I had was a developer form and a normal user form. The dev form allowed me to assign an access level ID, whereas the user one was defaulted to a certain level and hidden.

I will need to trap who is logged in, obtain their access level and go from there.

 

[MarkK]

You can do this very effectively with a class. Put this code in a class module . . .

private withevents m_cbo as access.combobox

public sub load(cbo as access.combobox)
[COLOR="Green"]' constructor for class, requires the combo for which we need to handle events
  'set local withevents variable[/COLOR]
  set m_cbo = cbo
[COLOR="Green"]  'enable event handling[/COLOR]
  m_cbo.onchange = "[Event Procedure]"
end sub

private property get mgrCombo as access.combobox
[COLOR="Green"]'  exposes the cboManagerID combo on the parent form of m_cbo[/COLOR]
   set mgrCombo = m_tb.parent.Controls("cboManagerID")
end property

private sub m_cbo_Change()
[COLOR="Green"]'  handles the change event of the combo[/COLOR]
  with mgrCombo
    .Requery
    .value = ""
  end with
end property

. . . and then on each form that requires this functionality, create an instance of the class that exists for the lifetime of the form . . .

private m_myclass as new cMyClass

private sub form_load
[COLOR="Green"]   'initialize an instance of our custom class[/COLOR]
   m_myclass.load me.cboLoactionID
end sub

. . . so not much code, and you simply can't do that with a standard module.

 

[ChrisO]

Given the code and control names supplied…

In the On Change event in the property sheet for cboLocationID:-

=HandleChange([cboManagerID])

And in a standard module:-

Public Function HandleChange(ByRef cbo As ComboBox)
    cbo.Requery
    cbo = ""
End Function

The above works even if the Form's Has Module property is set to No.

Chris.

 

[Pat Hartman]

Are you sure you want to use the Change property for that Chris? Change runs for EVERY character. Either the BeforeUpdate or AfterUpdate might be more appropriate.

 

[ChrisO]

Pat.

That was not the point of my reply and I did say “Given the code and control names supplied…”

The point was that Mark posted a class module solution and made the comment
“. . . so not much code, and you simply can't do that with a standard module.”

I was trying to show that it can be done in a standard module and, in fact, the code is much smaller.

If it is done on the Change or AfterUpdate event makes no difference to the technique, it can still be done that way. The BeforeUpdate event could also be handled in a standard module with:-

Public Function HandleChange(ByRef cbo As ComboBox)
    Dim blnSomeCondition As Boolean
    
    blnSomeCondition = True
    
    If (blnSomeCondition) Then
        cbo.Undo
        DoCmd.CancelEvent
    Else
        cbo.Requery
        cbo = ""
    End If
    
End Function

And that also works if the Form’s Has Module property is set to No.

But it is just to demonstrate that those events can be handled in a standard module and the Form does not even need a module to handle them.

Chris.