[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

How do I reference a generic form and field from VBA

I have a module that has a sub that is passed a form name in strFormName and a text field name in strFieldName.

How do I access the value of that field via code?

Thanks!
0
jrmcanada2
Asked:
jrmcanada2
  • 4
  • 4
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try

debug.print Forms(strFormName)(strFieldName)
or

debug.print Forms(strFormName)(strFieldName).Value
0
 
PatHartmanCommented:
I do this all the time. Don't pass the items as strings.  The calling procedure passes in the form object --

Call MySub (Me)

Then

Public Function MySub(frm As Form)

If IsNull(frm.cboClientID) Then
    Msgbox "Please select a client.",vbOKOnly
....

"Me" is a reference to the form object.  If you want the sub or function to be usable from many forms, you have to use identical names.  So, the combo that holds ClientID must be named cboClientID on every form.
0
 
Rey Obrero (Capricorn1)Commented:
< Don't pass the items as strings.>

and why not?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
PatHartmanCommented:
Why pass a string when you can pass a reference to the object?  You don't get full intellisense since you don't see the controls collection but you do get to see all the form properties and methods.  If you only wanted to address a specific control, you could pass in the control directly.  That would allow you to use different names for the control on different forms.  For example, if you wanted to edit every date field the same way, you could create a function and pass in each date field you wanted to validate, one at a time.

Perhaps the statement "don't use" was too strong.  I just have a preference for early binding and intellisense whenever I can get it and passing a form reference gives you a simpler method to create generic code than by creating class modules.
0
 
Rey Obrero (Capricorn1)Commented:
What if you are calling a sub or function different from the form's module?
you can only use (Me) if your calling code is in the form's module.
0
 
PatHartmanCommented:
you pass in Me which is a reference to the form you are calling from -

Call MyFunc(Me)

But in MyFunc, the reference is different

Public Function MyFunc (frm as Form)

in MyFunc, you use frm.something to reference form objects.  If you were in the form's class module itself, you would use me.something.
0
 
jrmcanada2Author Commented:
Thanks! That's exactly what I needed.
0
 
Rey Obrero (Capricorn1)Commented:
what if you want to get the value of  text1 from form "A" and you code is in Form B?
0
 
PatHartmanCommented:
Then you would need to address form "A" specifically - Forms!frmA!yourcontrol.  But that's not what we're talking about.  We are talking about calling a subroutine and passing it local data.  Using the form object as in the example lets you call the same piece of code from form "A" and from form "B".   So depending on which form called the sub, it is that form's controls that would be referenced.   If the function referenced ClientID as in our example, when you call the function from form "A", frm.ClientID references Forms!frmA!ClientID but if you call it from form "B" then frm.ClientID references Forms!frmB!ClientID because the reference "Me"  that is passed in, is different depending on context.  "Me" in form "A" is Forms!frmA whereas "Me" in form "B" is Forms!frmB.

The function would reside in a standard module since otherwise you wouldn't be able to even call it unless the form that housed it were loaded and you don't want to force forms to load just to reference their code modules.  All common code goes into standard modules or class modules if you care to build them.

I have an app where I rely heavily on this technique.  The main edit form is bulky and shows a lot of stuff but it also has complex editing and calculation requirements.  The same app has a condensed list type view of this data with only some of the columns.  The client wants to be able to change some things on the list form but I didn't want to recreate all the editing and put it in both forms so this was the solution.  To be more specific, the application manages care plans created by a state agency that manages care for the elderly.  Services have start and stop dates, frequency, hours, etc.  So we might schedule a homemaker 3 times a week for 2 hours and the care plan would start 5/1/14 and go through 4/31/15.  The plan would also include nursing visits, transportation, companion, etc.  But if the client goes into the hospital, even for a short stay, we have to suspend all the services.  The data entry people don't want to have to go into each record, they would rather just use the list form and change the end dates but there is way too much work that goes on behind the scenes to just duplicate the code.  This was the solution.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

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