?
Solved

Access Public Module to get Entire Form Name string

Posted on 2014-12-23
5
Medium Priority
?
248 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
[X]
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
  • 3
  • 2
5 Comments
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 2000 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 48

Accepted Solution

by:
Dale Fye earned 2000 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 48

Expert Comment

by:Dale Fye
ID: 40514788
glad to help.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

800 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