Solved

Access Public Module to get Entire Form Name string

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

770 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