Solved

Access Public Module to get Entire Form Name string

Posted on 2014-12-23
5
247 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 (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 48

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 48

Expert Comment

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

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 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