How do I get the name of a subform that is part of a tabbed Main form? (1 Viewer)

[Lateral]

Hi everyone,

I have looked and looked and can't find the answer to this issue so I am posting here hoping for some help.

I have developed a comprehensive Stock and Inventory system using Access 2007.

Here is the issue I have.

I have a main form called "BulkPartsMainForm"

It has a number of tabs that contain subforms.

One of the tabs is called "Alter Web Prices/SOH". The subform "loaded" into this tab is called "AlterWebSubForm".

This form enables the user to edit various bits of data for each product. It all works well. I have added some VBA code that logs any changes to a field for Audit purposes. It also logs the name of the form.

This works well when the "AlterWebSubForm" is opened by itself (as a Main Form) and not defined as a subform.

Using Screen.ActiveForm, it correctly extracts the name, "AlterWebSubForm".

When "AlterWebSubForm" is defined as a subform, the Screen.ActiveForm extracts the name of the MainForm, in this case "BulkPartsMainForm"

How can I get the name of the actual subform?

Thanks for any help you can provide.

Regards
Greg

 

[Pat Hartman]

Don't use Screen.ActiveForm

Use:
Me.Name

This will give you the Name of the form that is running the code and should give you a big clue as to why we NEVER use "Name" as a field name.

 

[June7]

Post your code. Is it located in the subform's module?

 

[Lateral]

Post your code. Is it located in the subform's module?

This is the module that I use for creating the Audit Records.....

It does not allow me to use the Me.Name instead of Screen.ActiveForm.



Function LogChanges(lngID As Long, Optional strField As String = "")



On Error GoTo Error_Problem

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim varOld As Variant
Dim varWebID As Variant
Dim varNew As Variant
Dim strFormName As String
Dim strControlName As String
Dim varWOPOID As Variant


' the following code displays the name of the active form


Dim frmCurrentForm As Form


Dim strName As String


Set frmCurrentForm = Screen.ActiveForm

'MsgBox "Current form is " & frmCurrentForm.Name



'Save the contents of the controls


'MsgBox Screen.ActiveControl.OldValue


varOld = Screen.ActiveControl.OldValue
varNew = Screen.ActiveControl.value
strFormName = Screen.ActiveForm.Name
strControlName = Screen.ActiveControl.Name

' Check to see if either a WO or PO is loaded by checking the name of the active form to see if it is Workorders

If Screen.ActiveForm.Name = "Workorders" Then

varWOPOID = Forms("Workorders").[WorkorderID]
'MsgBox "wo or po loaded"

End If

' Check to see if the active form is fPartsEdit and set the WOPOID to NULL
If Screen.ActiveForm.Name = "fPartsEdit" Then

varWOPOID = Null
'MsgBox "fpartsEdit loaded"

End If

'Dim frm As Form, ctl As Control

' Return Form object pointing to active form.
'Set frm = Screen.ActiveForm
'MsgBox frm.Name & " is the active form."
' Return Control object pointing to active control.
'Set ctl = Screen.ActiveControl
'MsgBox ctl.Name & " is the active control " _
'& "on this form."









'Dialog.Box varWIPOID


Set dbs = CurrentDb()
Set rst = dbs.TableDefs("ztblDataChanges").OpenRecordset

With rst
.AddNew
!FormName = strFormName
!ControlName = strControlName
!WOPOID = varWOPOID

If strField = "" Then
!FieldName = strControlName
Else
!FieldName = strField
End If
!RecordID = lngID
!UserName = Environ("username")
If Not IsNull(varOld) Then
!OldValue = CStr(varOld)
End If
If IsNull(varNew) Then
varNew = 0
End If

!NewValue = CStr(varNew)
.Update
End With
'clean up
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit_Nicely:

Exit Function

Error_Problem:

Dialog.Box "Click OK to continue"

Resume Exit_Nicely


End Function


I simply call it via the BeforeUpdate event attached to a field.

 

[Lateral]

Don't use Screen.ActiveForm

Use:
Me.Name

This will give you the Name of the form that is running the code and should give you a big clue as to why we NEVER use "Name" as a field name.

Hi Pat

Thanks for the reply.

This works but it doesn't work if I am calling a module.

 

[Pat Hartman]

Then get the form name and pass it to the module.....

Call yourfunction (Me.Name)

 

[Lateral]

Then get the form name and pass it to the module.....

Call yourfunction (Me.Name)

 

[Lateral]

Thanks Pat,

I'll have a play around.

Cheers
Greg

 

[The_Doc_Man]

OK, here is what you need to understand. Forms with sub-forms are not uncommon. Access manages these by having something called a sub-form control. You load a form to the sub-form control (usually but not always) in design mode. The main form and sub-form potentially have a parent/child relationship between them. Depending on something called "program locus" (the location of the code you are executing) you will use different syntax to find names.

From any form executing code that is local to that form, Me.Name will name the form that is executing the code. If done from the main form, it returns the name of the main form. If done from the sub-form, it returns the name of the sub-form. That is because Me. is a relative reference.

From a main form that has a sub-form, finding the name of the sub-form depends on knowing the name of the sub-form's containing control To get that name, you can use this rather awkward sequence: Me.sub-form-control-name.Form.Name What this actually asks is "what is the name of the form loaded to the sub-form control?" This is another relative reference.

From a sub-form that is within a main form, the name of the main form is Me.Parent.Name This is another relative reference.

The reason you didn't get anywhere using Screen.ActiveForm is because the main form is also active and the sub-form is merely a child of the active form. The main form, though it might not have focus, IS open on the screen and is of higher priority than the sub-form when searching for properties.

 

[June7]

Note for future: please post code between code tags to retain indentation and readability.

 

[Lateral]

OK, here is what you need to understand. Forms with sub-forms are not uncommon. Access manages these by having something called a sub-form control. You load a form to the sub-form control (usually but not always) in design mode. The main form and sub-form potentially have a parent/child relationship between them. Depending on something called "program locus" (the location of the code you are executing) you will use different syntax to find names.

From any form executing code that is local to that form, Me.Name will name the form that is executing the code. If done from the main form, it returns the name of the main form. If done from the sub-form, it returns the name of the sub-form. That is because Me. is a relative reference.

From a main form that has a sub-form, finding the name of the sub-form depends on knowing the name of the sub-form's containing control To get that name, you can use this rather awkward sequence: Me.sub-form-control-name.Form.Name What this actually asks is "what is the name of the form loaded to the sub-form control?" This is another relative reference.

From a sub-form that is within a main form, the name of the main form is Me.Parent.Name This is another relative reference.

The reason you didn't get anywhere using Screen.ActiveForm is because the main form is also active and the sub-form is merely a child of the active form. The main form, though it might not have focus, IS open on the screen and is of higher priority than the sub-form when searching for properties.

Thanks for the detailed explanation Doc Man1

 

[Lateral]

Note for future: please post code between code tags to retain indentation and readability.

No problem