Solved

Access Public Module to get Entire Form Name string

Posted on 2014-12-23
5
243 Views
Last Modified: 2014-12-23
Hello Experts....

I will start by declaring that I have NOT tried to build a Public Module of much complexity before.  So it's intricacies and limitations are foreign to me, so I apologize in advance if my question seems stupid.

I am trying to build a Module that I can place a Command Button on any Form in my DB that will get that Form's entire name string (Form/Parent/Grandparent/GreatGrandParent etc...)

IF there is a function already in Access that allows that I am unaware of it.  So my feeble attempt was
Option Compare Database
Option Explicit

Public strFormCtlName As String
Public strFormName As String
Public strFormParentName As String
Public strFormGrParentName As String
Public strFormGreatGrParentName As String
Public strFormGreatX02GrParentName As String
Public strFormGreatX03GrParentName As String
Public strFormGreatX04GrParentName As String
Public strFormGreatX05GrParentName As String
Public strFormGreatX06GrParentName As String
Public strFormGreatX07GrParentName As String
Public strFormGreatX08GrParentName As String
Public strFormGreatX09GrParentName As String

Public Sub LVLFrmName()

On Error GoTo err_Handler

    strFormName = ""
    strFormParentName = ""
    strFormGrParentName = ""
    strFormGreatGrParentName = ""
    strFormGreatX02GrParentName = ""
    strFormGreatX03GrParentName = ""
    strFormGreatX04GrParentName = ""
    strFormGreatX05GrParentName = ""
    strFormGreatX06GrParentName = ""
    strFormGreatX07GrParentName = ""
    strFormGreatX08GrParentName = ""
    strFormGreatX09GrParentName = ""
    
    strFormName = Form.Name
    strFormParentName = Parent.Form.Name
    strFormGrParentName = Parent.Parent.Form.Name
    strFormGreatGrParentName = Parent.Parent.Parent.Form.Name
    strFormGreatX02GrParentName = Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX03GrParentName = Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX04GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX05GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX06GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX07GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX08GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Form.Name
    strFormGreatX09GrParentName = Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Form.Name

MsgBox "" & strFormParentName
MsgBox "" & strFormGrParentName
MsgBox "" & strFormGreatGrParentName
MsgBox "" & strFormGreatX02GrParentName
MsgBox "" & strFormGreatX03GrParentName
MsgBox "" & strFormGreatX04GrParentName

err_Handler:
    Resume Next

End Sub

Open in new window


The message boxes I used to just display if I was getting anything.....  I didn't....
I tried it with "Me." in front of all the names ie Me.Form.Name ..... However, as you Experts know that doesn't work either...

On my Form I placed a Command Button that simply Calls LVLFrmName

Oh... By the way.... No I don't have any form that I have 6 7 8 or 9 layers of subforms but I put that in just in case I got carried away :-)
0
Comment
Question by:wlwebb
  • 3
  • 2
5 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 total points
ID: 40514711
@wlwebb

The way to do this is to define your public function and declare a form in the arguments section.  Then you can create a loop.  Something like:

Private Function fnFormName(frm As Form) As String

    fnFormName = frm.Name
    
    On Error GoTo ProcError
    
    Do
       Set frm = frm.Parent
       fnFormName = frm.Name & " => " & fnFormName
    Loop

ProcError:
    If Err.Number <> 2452 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    
End Function

Open in new window

The error handler will display an error message if any error other than #2452 is generated, which is the error that occurs if you refer to a parent when you are at the highest level.

When the function reaches the highest level it will exit the function and return a string that looks like:

TopLevelName => Next Level Name => bottom level name

If you want this info displayed in some other manner, you can replace the string (" => ") with vbcrlf or some other delimiter.  I called this function in the Click event of the subform and simply displayed a message box with:

Private Sub cmd_FormHeirarchy_Click

    msgbox fnFormName(me)

End Sub

Open in new window


Or you might want to simply display this path as the Caption of the top level form.

Private Function fnFormName(frm As Form) As String

    fnFormName = frm.Name
    
    On Error GoTo ProcError
    
    Do
       Set frm = frm.Parent
       fnFormName = frm.Name & " => " & fnFormName
    Loop

ProcError:
    If Err.Number <> 2452 Then
        MsgBox Err.Number & vbCrLf & Err.Description
    End If
    frm.Caption = fnFormName

End Function

Open in new window

0
 

Author Comment

by:wlwebb
ID: 40514766
Dale,
So you can't put this in a Public Function within a Module that can be called from any form?????


.... OOPSS.... I SEE... Had it as Private in the module
Give me a second
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40514775
Oops, that should have been Public.
0
 

Author Closing Comment

by:wlwebb
ID: 40514781
Thank you....
MUCH MUCH MUCH cleaner than mine ..... and better yet.... IT WORKS and mine doesn't......... :-)))
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40514788
glad to help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

11 Experts available now in Live!

Get 1:1 Help Now