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
Solved

Access Public Module to get Entire Form Name string

Posted on 2014-12-23
5
245 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

789 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