Get a Subform Name

[accvbalearner]

Hi all,

:banghead: Stuck again and everyone here is so helpful!

Today I am trying to retrieve a subform's name so that I can incorporate it into a piece of code, but I am not having any luck after searching through the forum.

I have established a variable like this:

Dim currentFormName As Form
Set currentFormName = Screen.ActiveForm

fmName = currentFormName.Name

to get the name of the Active Form so that I can return to it after another form closes.

Now what I would like to do is the same thing for a subform but I can't figure out how to create the reference.

The code below is on the lost focus event for a field in a form. I have seven other forms that are similar but get data to/from from different tables. What I would like to do is have one set of code that will get the name of the recordsource for the form and the name of the subform so that the code can just be copied and pasted in to each lost focus event on the seven forms, or made into a standard module that the lost focus event will refer to when running.

Private Sub ActCode_LostFocus()
'This will determine if the Field APCode will be a duplicate
'based on the Facility and System Chosen. It should prevent
'Duplicate Systems from being created for the same facility.
Dim strSys As String
Dim strFac As String
Dim recSrc As String 'Used for Recordsource to Filter
Dim subFrm As SubForm 'Used for Name of Subform
Dim sfmName As String
 
Set subFrm = Screen.ActiveForm.Form.Name
 
sfmName = subFrm
MsgBox ("Name of Subform is : " & sfmName)
 
'This will determine the type of filter to use on F1x-Subform
'If ActCode is blank, but Facility isn't then it filters F1x
'for Facility only, otherwise it will clear the filter or run
'a filter if both are not null.
 
recSrc = Me.RecordSource
 
If IsNull(Me.ActCode) And Not IsNull(Me.Facility) Then
MsgBox ("Statement will look like this to filter: [PlantCode] = '" & Me.Facility & "'")
Me.sfmName.Form.Filter = "[PlantCode] = '" & Me.Facility & "'"
Me.sfmName.Form.FilterOn = True
Else
If IsNull(Me.ActCode) And IsNull(Me.Facility) Then
Me.sfmName.Form.FilterOn = False
Else
strAC = Me.ActCode
strFac = Me.Facility
strFP = strFac & strAC
 
'MsgBox ("Combined Code is " & strFP)
 
If Not IsNull(DLookup("[APCode]", recSrce, "[APCode] = '" & strFP & "'")) Then
MsgBox "The Area Code you have entered will create a duplicate area for the facility." & vbCrLf & "Here is the similar record."
Me.sfmName.Form.Filter = "[ActCode] = '" & strAC & "' AND " & "[PlantCode] = '" & strFac & "'"
Me.sfmName.Form.FilterOn = True
End If
End If
End If
End Sub

Any help would be much appreciated!

 

[ChrisO]

If I understand correctly…

No code required behind the Form(s)

In the property sheet for the Lost Focus event of APCode:-

=Handle_LostFocus([APCode],[PlantCode])

That then directly calls the code below in a standard module:-

Public Function Handle_LostFocus(ByRef ActCode As Control, _
                                 ByRef Facility As Control)
     
    With ActCode.Parent
        If IsNull(ActCode) And Not IsNull(Facility) Then
            MsgBox "Statement will look like this to filter: [PlantCode] = '" & Facility & "'"
            
            .Filter = "[PlantCode] = '" & Facility & "'"
            .FilterOn = True
        Else
            If IsNull(ActCode) And IsNull(Facility) Then
                .FilterOn = False
            Else
                If Not IsNull(DLookup("[APCode]", .RecordSource, "[APCode] = '" & Facility & ActCode & "'")) Then
                    MsgBox "The Area Code you have entered will create a duplicate area for the facility." & vbCrLf & _
                           "Here is the similar record."
                           
                    .Filter = "[ActCode] = '" & ActCode & "' AND " & "[PlantCode] = '" & Facility & "'"
                    .FilterOn = True
                End If
            End If
        End If
    End With
    
End Function

The above code is the nearest equivalent I can figure out but is unlikely to work exactly as you require.

What is does, though, is to entirely remove the need for the Form name in the sub-Form control.

Forms in sub-Form controls are not opened but instantiated. The names of instantiated Forms can not be relied on to be unique. Since they are not unique they are almost useless.

Since a sub-Form is not opened it can not become the Screen.ActiveForm. For a sub-Form the Screen.ActiveForm is the outer most Parent Form which contains the sub-Form at any level.

However, a pointer (reference) to the sub-Form is unique. A pointer to a Control, such as a Textbox, is also unique, even if it is in an instantiated Form . A Control has a Parent property which points to the Form which contains it. The Parent property of a unique Control points to the unique Form which contains it, even if that Form is instantiated.

So…
Screen.ActiveForm points to the outer most Parent Form, not the sub-Form.
Screen.ActiveControl points to the Active Control even if that Control is on an instantiated sub-Form.
Screen.ActiveControl.Parent points to the Form on which the Control resides even if that Form is instantiated.

And…
We can rely on pointers to both Controls and Forms.
We can rely on pointers to Control Parent.
But we can not rely on Control.Name or Form.Name if the Form is instantiated, they are not unique.

Therefore, in the above code…
A pointer to each of the two Controls is passed. (ActCode and PlantCode).
The pointer to their Parent Form is derived from ActCode.Parent.
No reference to the name of the Controls or Form is done, they may not be unique.
The pointers are used because the pointers are unique.


Please…
Use Option Explicit in your code.
Use the Code Tags when posting.
Copy and paste code to site, don’t try to type it.
It would help if you could post a small demo in Access 2003.

Chris.

 

[accvbalearner]

ChrisO,

Thank you very much for your comments and recommendations! I really like your idea of putting it into a module rather than an event, I should be able to use it in all of the forms rather than change a reference for each one. I do have some questions however, so if you have time can you give me some more answers?

1. I added your Function to a standard module and put the reference for the Handle_LostFocus in the Property sheet On Lost Focus Event for the ActCode Combobox, whenever it runs, I get a Type Mismatch on the On Lost Focus event. It is stopping the form, so I don't have a way to trace the Type Mismatch because it doesn't start the Debug in VBA. Any ideas?
2. Option Explicit - from what I understand, when using this it requires all variables to be assigned a data type like string, variant, boolean etc. If you are already doing that (like Dim Test As String) does it make a difference? By using it does it make troubleshooting any easier?
3. Code Tags - I thought that I used them when posting the thread, if you are talking about Code Tags in Access, the only one's I know of are SmartTags and Tags in the Property Sheet.
4. Copy & Paste Code - I have been copying code straight from VBA, but the indents don't transfer across as yours seem to, not sure why?
5. Instantiated Forms - I'm relatively new to VBA, so this is a first for me. I understand your point about an instantiated form being useless if it isn't unique. Can you provide a link, book or article that discusses this further?
6. Starting code with (.) - I don't understand how that works as I am reading through your code. Does VBA just assume there is a Me or Forms or something else is there when it runs? How does it know which one (i.e. Me.[ActCode] or Form.[ActCode])? I see the Filter commands, but where does the code tell the filter to apply to the subform?

I hope I haven't bombarded you with too many questions at once. Thanks again for the taking the time to give me a hand with this issue.

 

[ChrisO]

Answers, as best I can at the moment.

1.
The error is most probably in the Lost Focus event Property…
=Handle_LostFocus([APCode],[PlantCode])
May be it should be:-
=Handle_LostFocus([cboActCode],[txtFacility])

I really don’t know because I don’t know many things. I didn’t know if APCode was a Textbox, List box, Combo box or, indeed, a long legged giraffe in pink stockings.
(There are many long legged giraffes in pink stockings in Access.)

So I made an assumption that whatever it was…it was a Control.
Now we know it is a Combo box, we don’t know if it is fed from a Table, Value List, is Bound / Unbound, what Column is Bound if it is Bound, if it is Bound does it have the same name as the field to which it is Bound, which Column is Bound if it is Bound and is it set to Limit to List. All we really know is that it’s not a long legged giraffe in pink stockings.

It’s because of that long legged giraffe in pink stockings I said:-
>>It would help if you could post a small demo in Access 2003.<<

2.Option Explicit.
>>If you are already doing that (like Dim Test As String) does it make a difference?<<
If you are already doing that then, as far as I know, it makes no difference.
Trouble is, you were not doing that:-
Dim recSrc As String
recSrc = Me.RecordSource
If Not IsNull(DLookup("[APCode]", recSrce, "[APCode] = '" & strFP & "'")) Then
So, turn it on.

3.Code Tags
>>I thought that I used them when posting the thread<<
No you didn’t. I put the code tags in for you, look at the edit at the bottom of post #1, and since it made no difference I assume you do not indent your code.

4. Copy & Paste Code.
I can not explain that since adding the code tags made no difference. See 3.

5. Instantiated Forms.
>>I understand your point about an instantiated form being useless if it isn't unique.<<
No, that was not my point. My point was that instantiated Forms are unique but their Names may not unique. Hence, the Name of an instantiated Form can not be relied on to differentiate between instances.
This is why instantiated Forms, including sub-Forms (which are instantiated Forms), do not appear in the Forms collection. The Forms collection is a collection of open Forms. One of the ways to index into that collection is by Name but the Name needs to be unique in order to be an index. So, if the Form collection defines which Forms are open then instantiated Forms are not open. Sure they are visible on the screen but they are, by definition, still not open. Instantiated Forms are copies of a Form and we could draw a loose parallel with visible Controls on a Continuous Form. There may be many Controls visible on the Form but there is only one Control by that Name. If we try to use that Control’s Name as an index we affect all instances of that Control.

So for both cases, instantiated Forms and Controls on a Continuous Form, the Name becomes useless as a reliable index.

>>Can you provide a link, book or article that discusses this further?<<
Apart from a link to this thread; no I can’t and I wouldn’t if I could. The reason is that I have no control over what others have said, though at times I do work on what others may say. Whatever you read may be correct or may just be another recycled internet long legged giraffe in pink stockings.

6. Starting code with (.)
>>Does VBA just assume there is a Me or Forms or something else is there when it runs?<<
Yes precisely, it’s the With Block in code:-

With Me
    .FilterOn = True
End With

Equates to:-
Me.FilterOn = True

With ActCode.Parent
    .FilterOn = True
End With

Equates to:-
ActCode.Parent.FilterOn = True


The With Blocks can be nested:-

With ActCode.Parent
    .FilterOn = True

    With .cboActCode
        .BackColor = vbRed
    End With
    
End With

Would equate to:-
ActCode.Parent.FilterOn = True
and
ActCode.Parent.cboActCode.BackColor = vbRed


As you can see, answering general questions can involve a lot of work.

But, if you want me to help with a specific problem then you will need to do some of the work and supply a specific demo in Access 2003.

Chris.