Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

Public Module

In the last part of my question Here  mbizup suggests

" If there is a sub or function that I need to use across multiple forms, I generally will place it in a separate public module.  You can pass form and/or control names to public functions as needed. "

My question...
Is there an example somewhere of passing Form and control names through or to a public function?
0
Larry Brister
Asked:
Larry Brister
  • 5
  • 4
  • 3
  • +1
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
.. in your form, call a function, and pass the current Form's object ...

Dim b as Boolean
b = fn_help(Me)

... then in a separate code module..

Public Function fn_help(frm as Form) as boolean

'air code
frm.btn_help.Caption = "foo"
frm.txt_help = "This is sample text"

'Return a value
fn_help = True

End Function
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
for a form:

Function/Sub:
Sub LockControls(frm as Form)

call:
call  LockControls([Form])

For a control:

Functions/Sub:
Function ControlType(ctl As Control)

call:
Dim ctl as control

For Each ctl In Reports(strReportName).Controls
       If ControlType(ctl) = acListBox Then
        'Set the TableDef based on the Caption of the list box's attached label.


Jim.
0
 
mbizupCommented:
If a function or sub is geared towards one specific form, I'd use the full form reference like this rather than passing a formname (a function/sub in a public module can be used anywhere in your database):

Public Sub DoStuff()
         MsgBox "Total value is: " & Forms!YourFormName.YourSubformName.txtTotal
         ' etc
End Sub

Open in new window


However, if you want the procedure to apply to multiple forms (generically), you can use the form name as a parameter:


Public Sub DoStuff(strFormName as String, strControlName as String)
         Forms(strFormName).Requery
         Forms(strFormName).controls(strControlName).Requery
End Sub

Open in new window


You can call it from any form like this:
 
DoStuff Me.Name, "cboMyComboBox"

Open in new window

0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
It should be pointed out that 'Me' and '[Form]' only work when your coding within a form module.  If you were in a general module and had a form name as a string, you'd need to do something like this:

Function/Sub:

  Sub LockControls(strFormName as string)

  Dim frm as form

  Set frm = Forms(strFormName)


 frm is now a form object and you can access it's methods and properties.  ie.

  If frm.visible = True then

Jim.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Ok folks,
  Gonna take a look at these later this morning and get back to you with points
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Just one other point, doing your routines like this:

Public Sub DoStuff(strFormName as String, strControlName as String)
         Forms(strFormName).Requery
         Forms(strFormName).controls(strControlName).Requery
End Sub

 is a tad slower rather then addressing it as an object because your forcing Access to lookup the object in the forms collection each time you reference it rather then giving it a direct pointer.

However it's a lot easier to use that way.  For example, you can easily call it from the debug window simply by typing:

 Call DoStuff("JimsForm","FirstControl")

and hitting return.  You can't do that always when it's defined as an object:

Public Sub DoStuff(frm as Form, ctl as Control)

 I actually use both approaches making sure I use the latter for the critical stuff to ge the best performance and the first for procedures I might want to call on the fly as I test and debug.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<< is a tad slower rather then addressing it as an object because your forcing Access to lookup the object in the forms collection each time you reference it rather then giving it a direct pointer.>>

  BTW, a best of both worlds approach is to do what I showed, especially if your going to perform a lot of operations on it:

  Sub LockControl(strFormName as string, strControlName as string)

  Dim frm as form
  Dim ctl as control

  Set frm = Forms(strFormName)
  set ctl = Forms(strFormName)(strControlName)

  and now you have objects to work with even though you passed in strings.

Jim.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Ok guys...

I built a module and placed this in it

Option Compare Database

Public Function DoStuff(strFormName As String, strControlName As String) As String
         DoStuff = Forms(strFormName).Controls(strControlName).Caption
End Function

I'm calling it with this...
    Dim s As String
    s = DoStuff("MarketingVisitationClientContactsWorklist", "lblTestMessage")

The FORM Name I got by actually doing a F2 on the name of the form from the Forms window in MS Access and copying the name.

When I look at the properties in the form...I see that the Caption is the same thing

Screenprint

On other forms I can access the information I need.
But on this particular form...I get a "Can't fins the Form error message"

Screen print 2
I have tried every way possible to acces this subform per question  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27981361.html

Any ideas?

ALthough this may need to be placed in a new question?
0
 
mbizupCommented:
Since you're dealing with subforms, the references to your forms and controls are going to be different - depending on where you place the code you call your public function or sub with.

This is a 5-minute (ugly) sample that demonstrates this:PublicFunction.mdb


The code in the sample uses Jim D's suggestion of using the form object rather than the form name when calling your routine. This simplifies working with subforms - especially since you need to look at your subforms as controls containing forms.  

It calls the public function from a variety of places, refering to a main form, sub form and sub-subform.  Take a look at the code behind the buttons for the differences.


Note - To work with a subform as a form, you need to first refer to the control housing the subform and then specify that you want to work with it as a form (hence the  Me.subformControlName.Form syntax)
0
 
mbizupCommented:
<<
The FORM Name I got by actually doing a F2 on the name of the form from the Forms window in MS Access and copying the name.
>>

Also  - when dealing with subform paths, the "subform name" you need to refer to is actually the name of the control that houses your subform -- which might not be the same as the name you see in the Forms window.  The subform control name is determined by right-clicking the 'frame' of the subform control in your form's design, bringing up its property sheet and looking up the Name property.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey folks,
  Thanks.  Looks like I should do a split on the points between mbizup and Jim D

Any objections?

Jim was first and mbizup gave a working example.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Looks like I need to go back to JimD; there are two Jim's in the thread.   Jim Horn was first.

I'm fine with no points or whatever.

JimD.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Well done guys.

I wanted everyone with valid input to get points
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now