Solved

Public Module

Posted on 2013-01-03
13
338 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now