Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Public Module

Posted on 2013-01-03
13
343 Views
Last Modified: 2013-01-04
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
Comment
Question by:lrbrister
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 38739910
.. 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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 125 total points
ID: 38739930
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
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 125 total points
ID: 38739940
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 57
ID: 38739942
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
 

Author Comment

by:lrbrister
ID: 38740099
Ok folks,
  Gonna take a look at these later this morning and get back to you with points
0
 
LVL 57
ID: 38740210
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
 
LVL 57
ID: 38740245
<< 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
 

Author Comment

by:lrbrister
ID: 38740555
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38741543
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38741578
<<
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
 

Author Comment

by:lrbrister
ID: 38741712
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
 
LVL 57
ID: 38743730
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
 

Author Closing Comment

by:lrbrister
ID: 38743746
Well done guys.

I wanted everyone with valid input to get points
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question