Check whether a form is opened as subform

[LarryE]

Lets say you have a couple thousand forms and subforms and do not want to populate this by hand. Write some simple code to update the table. This gets rid of all the problems.

That's exactly what I am doing. Populating the FormTable with form names, then the VBA routine looks at each form name in the table and adds it to the RibbonUI Forms combobox list. But I only want the forms that are NOT subforms OR are already loaded to be available to be opened with the combobox. Populating a custom RibbonUI combobox is NOT a simple matter. I also have one for Reports. The Callbacks are a nightmare.

 

[LarryE]

FYI, that still would not help. How would you read the tag of a closed form? Still the same problem
The naming convention would be better except you may need a suffix for a form opened as either main form or subform.
However if I am building a ribbon for a user I doubt they want to see my form name since it is not super intuitive.
frmUseDtls
instead of showing them something like "Form User Details (frmUseDtls)".

No, I am opening the form in Design mode, checking the Tag property to see if it is a Subform, then closing the form and if the form is NOT a subform, then add it to the FormTable. First, I check to see if the form is loaded, if it isn't then check to see if it is a subform or not, if it is, do nothing, if it is not, add it to the table.

I know, working with the RibbonUI is not easy. Just telling you what I am attempting is giving me a headache.

 

[MajP]

Sorry, I am lost. Still no idea why at runtime you need to load the table. Why can't the table not be fully populated as I suggested.
A table with all forms that can be opened? Then if you need to simply mark those in the table as being already opened or closed.

 

[Edgar_]

@LarryE
Since you have the collection of form names in CurrentProject.AllForms, you can loop over that list, use SaveAsText, find the Begin Subform line and check its SourceObject property, here's a snippet of what that command outputs, it's like a blueprint of the form.

Also, whenever you have doubts about data types, you can open the Inspect window from the VBE IDE, add an inspection scoped for all modules and procedures, name it Application and you will get an expandable item where you can find out the properties of what is loaded. The error you refer to in post #8 was thrown because you were trying to reference a Form property but the object in your Loop was an AccessObject object, not a Form object.

 

[The_Doc_Man]

In the specific case, @LarryE, you can step through and tag subforms if you wish. That is because if you are the author of the DB you would know which forms were intended for subform usage.

In the general and somewhat pedantic case, though, there is no such thing as as a subform. There is only a form that is loaded to a subform control. In the truly general case, that load action can be done dynamically at run-time, so it isn't even a static condition.

 

[LarryE]

Still no idea why at runtime you need to load the table

Because to populate the custom RibbonUI, we need to do that. The Callbacks work fine and populates the combobox. My only problem was identifying and excluding sub-forms

 

[LarryE]

In the specific case, @LarryE, you can step through and tag subforms if you wish. That is because if you are the author of the DB you would know which forms were intended for subform usage.

In the general and somewhat pedantic case, though, there is no such thing as as a subform. There is only a form that is loaded to a subform control. In the truly general case, that load action can be done dynamically at run-time, so it isn't even a static condition.

Yes. That is why I originally thought the IsSubform custom function might work but it has the same problem. Just learning new stuff at 75 years old. They say it's never too late, but maybe it is..... I'll just do it the easy way and rename the sub-forms as sub-forms and identify them that way. Opening and reading each forms Tag is a pain and it's cocktail o'clock anyway. I've been trying to learn how to populate and use a custom RibbonUI combobox for opening forms and reports.

 

[NauticalGent]

@LarryE
Since you have the collection of form names in CurrentProject.AllForms, you can loop over that list, use SaveAsText, find the Begin Subform line and check its SourceObject property, here's a snippet of what that command outputs, it's like a blueprint of the form.

View attachment 116196

Also, whenever you have doubts about data types, you can open the Inspect window from the VBE IDE, add an inspection scoped for all modules and procedures, name it Application and you will get an expandable item where you can find out the properties of what is loaded. The error you refer to in post #8 was thrown because you were trying to reference a Form property but the object in your Loop was an AccessObject object, not a Form object.
View attachment 116197

Neat little utility there!

 

[Edgar_]

Neat little utility there!

Thanks, John

I tested this code with a few database files and it's working as expected so far: it finds subforms in subform controls, ignores empty subform controls, is quick and it does not require having any form open, it can be in a module on its own.

Sub FindSubforms()
    Dim folderPath, flag As Boolean, line As String, f As AccessObject, output As String
    folderPath = CurrentProject.Path
    output = "Subforms found:"
    For Each f In CurrentProject.AllForms
        SaveAsText acForm, f.FullName, folderPath & "\" & f.FullName & ".txt"
        Open folderPath & "\" & f.FullName & ".txt" For Input As #1
        Do While Not EOF(1)
            Line Input #1, line
            If InStr(line, "Begin Subform") > 0 Then flag = True
            If flag And InStr(line, "End") > 0 Then flag = False
            If flag And InStr(line, "SourceObject") > 0 Then
                output = output & vbCrLf & Trim(Split(line, "=")(1))
                flag = False
            End If
        Loop
        Close #1
    Next f
    MsgBox output
End Sub

Spoiler: Click here to see the code with comments of what it's doing

Sub FindSubformsWithComments()
    ' folder path
    Dim folderPath As String
    folderPath = CurrentProject.Path
 
    ' loop vars
    Dim flag As Boolean
    Dim line As String
    Dim f As AccessObject
    Dim output As String
    output = "Subforms found:"
 
    ' check forms
    For Each f In CurrentProject.AllForms
        ' save form blueprint
        SaveAsText acForm, f.FullName, folderPath & "\" & f.FullName & ".txt"
    
        ' open blueprint file for reading
        Open folderPath & "\" & f.FullName & ".txt" For Input As #1
    
        ' read line by line
        Do While Not EOF(1)
    
            ' initialize line
            Line Input #1, line
        
            ' if Begin Subform is found, flag for subsequent lines
            If InStr(line, "Begin Subform") > 0 Then flag = True
        
            ' if flag is true and End was found, the subform control was empty
            If flag And InStr(line, "End") > 0 Then flag = False
        
            ' if flag is true and we have a sourceobject, that's our subform!
            If flag And InStr(line, "SourceObject") > 0 Then
        
                ' build the output
                output = output & vbCrLf & Trim(Split(line, "=")(1))
            
                ' restart flag
                flag = False
            End If
        Loop
    
        ' close file
        Close #1
    Next f
 
    MsgBox output
 
End Sub

The blueprint files are created in the same directory as the database file, but they are overwritten each time the process runs. To prevent this, you can add some code to delete the existing files either before running the process or after it runs to keep your directory clean. I aimed to keep the code brief, so I avoided adding things that don't really matter.

 

[LarryE]

The file solution suggested by others is what I would use. It also allows you to use VBA-friendly form names internally and user-friendly form names externally. I do a similar thing for reports. I have a different switchboard that organizes forms. In my reports table, I also have a group code so that can help the user find a particular report because the group will probably be only a few choices but the entire report list could easily be long enough to require scrolling. The reports table ends up to also be a good documentation tool. When the user clicks on a report, its description shows up in a text box below the listbox so if he doesn't know which report he needs because he's new to the company, this helps.

Using the ribbon with a list of forms is "cool". I don't do "cool". What I don't like about this method is you don't have a way to group the forms unless you use prefixes in their names, so the alternative is always an alpha sort.

Previously I always used just a standard report and form combo box using the MySysObjects table and that always worked fine, and I will continue to do that, but I was just experimenting with other methods. I don't know if it's "cool" or not, but I really don't like the way you are forced to populate the custom combobox list. You populate a table first, then each individual record needs to be added one at a time to the combobox list and then you delete the record just added to the list and start all over again with the next record. And it's a pain to keep the list current (for example, if a form or report is already open, you don't want it in the list to be available to open again). It's confusing and exasperating at times. I had fun trying though. Well, sort of. I have used a lot of custom RibbonUI buttons and standard ACCESS functions in the past, but I never tried it with opening forms and reports using a combo. I like to learn new stuff.

Actually, there is a way to "group" like items together in a Ribbon group, but the XML code in the custom Ribbon table is just a nightmare. Using the custom RibbonUI is difficult enough. As you know, I'm not a programmer by trade, so doing stuff like this is a challenge. I'm going to keep it simple from now on.

 

[jdraw]

I like Edgar's solution in #30. But thought I should give identifying subform controls and their sourceobjects a try based on Forms, controls and forms loaded into subform controls. FWIW.

' ----------------------------------------------------------------
' Procedure Name: Check4Subforms
' Purpose: Check all forms in current project and identify all subform controls and
'          the forms loaded into those subform controls.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 25-Sep-24
' ----------------------------------------------------------------
Sub Check4Subforms()
          Dim frm As Object
          Dim ctl As Control
          Dim subformControlname As String
          Dim subformSourceObject As String
          Dim iCount As Long
          ' Loop through all forms in current Project
10        For Each frm In CurrentProject.AllForms
                    
              ' Debug.Print  frm.name 'testing
20            DoCmd.OpenForm frm.name, acDesign, , , , acHidden
              ' Loop through all controls on the form
30            For Each ctl In Forms(frm.name).Controls
40                With ctl
                      ' Check if the control is a subform
50                    If .ControlType = acSubform Then
60                        iCount = iCount + 1
70                        subformControlname = .name
80                        subformSourceObject = .SourceObject
90                        Debug.Print iCount & " Form: " & frm.name & "  " & "Subform control found: " & subformControlname & vbCrLf _
                              & vbTab & "   *** Form loaded as subform: " & subformSourceObject
100                   Else
110                   End If
120               End With
130           Next ctl
140           DoCmd.Close acForm, frm.name
150       Next frm
160       MsgBox "Subform Check complete."
End Sub

Sample output:
1 Form: Customer Orders Subform control found: Customer Orders Subform1
*** Form loaded as subform: Customer Orders Subform1
2 Form: Customer Orders Subform control found: Customer Orders Subform2
*** Form loaded as subform: Customer Orders Subform2
3 Form: _QE_QueryExplorer Subform control found: CTL_Query
*** Form loaded as subform: _QE_Query
4 Form: _QE_QueryExplorer Subform control found: CTL_ComboListbox
*** Form loaded as subform: _QE_ComboListbox
5 Form: formToFilterAnimalLocations Subform control found: AnimalLocsSubform
*** Form loaded as subform: AnimalLocsSubform
6 Form: frmEmployeeTravel Subform control found: subfObject
*** Form loaded as subform: sfrmEmpTravel
7 Form: FrmShiftCompletion Subform control found: frmShiftEnd
*** Form loaded as subform: frmShiftEnd
8 Form: frmTicketStatus Subform control found: Child6
*** Form loaded as subform: Table.TicketStatusComment
9 Form: frmBookAuthorsByNameWhenMultipleIds Subform control found: AddMissingDogToTable
*** Form loaded as subform: frmBooksAndAuthors
10 Form: FormFriendlySearch Subform control found: subFormControl
*** Form loaded as subform: sfrmOrdersSearchQ
11 Form: FormFriendlySearchMain Subform control found: subformcontrol
*** Form loaded as subform: ZNewForm
12 Form: FormWithTabsEstablishment Subform control found: tbl_Spl_LCL_Std_EstAllianceEngsubform
*** Form loaded as subform: tbl_Spl_LCL_Std_EstAllianceEngsubform
13 Form: FormWithTabsEstablishment Subform control found: tbl_Spl_LCL_Std_EstProfileFre subform
*** Form loaded as subform: tbl_Spl_LCL_Std_EstProfileFresubform

 

[Gasman]

Jack, I see you leave your subform control names the same as their source objects.

 

[jdraw]

Hi Paul,
I tested with an old database and it's a bit of a nightmare re naming consistency. Or maybe just old age and little practice. Hopefully the logic may be useful to someone.

 

[isladogs]

Jack, I see you leave your subform control names the same as their source objects.

In my opinion, there are two perfectly valid approaches:
1. Always name the subform control the same as the subform object
2. Always name the subform control differently to the subform object

The problems most developers have is when they aren't consistent in their approach
FWIW, I always use method 1. It works and I have one less name to deal with in my code

 

[GPGeorge]

In my opinion, there are two perfectly valid approaches:
1. Always name the subform control the same as the subform object
2. Always name the subform control differently to the subform object

The problems most developers have is when they aren't consistent in their approach
FWIW, I always use method 1. It works and I have one less name to deal with in my code

The only time I can think of that method 1 "doesn't work" is a navigation design that loads different subforms into a subform control depending on the current function.

 

[Edgar_]

The only time I can think of that method 1 "doesn't work" is a navigation design that loads different subforms into a subform control depending on the current function.

For those setups, the SaveAsText method can also be helpful, as it exports the code from the form module. Right now, I recall Form.RecordSource, DoCmd.BrowseTo and NavigationButton.NavigationTargetName as the main ways to modify the form within a subform control. If we search for lines that contain these commands, we can extract the names of the forms designated as subforms and add them to the final list of subforms.

 

[isladogs]

The only time I can think of that method 1 "doesn't work" is a navigation design that loads different subforms into a subform control depending on the current function.

Good point
I 'roll my own' navigation style forms and for such purposes use code such as the following to identify the object and , where necessary, set the record source

Me.fsubAnalysis.SourceObject = "fsubObjectsInfo"

Me.fsubAnalysis.Form.RecordSource = "SELECT tblObjectInfo.ObjType, tblObjectInfo.ObjName, qryObjBadNames.ObjName AS BadName," & _
                      " tblReservedWords.Term AS ReservedWord, tblObjectInfo.TypeValue, tblObjectInfo.Flags," & _
                      " IIf(Nz(tblObjectInfo.SubType,'')<>'',tblObjectInfo.SubType,IIf(Nz([ModuleType],'')<>'', [ModuleType],IIf(Nz([DefaultView],'')<>'',[DefaultView],''))) AS ObjectSubType," & _
                      " tblObjectInfo.SubType, tblObjectInfo.ModuleType, tblObjectInfo.DefaultView, tblObjectInfo.ObjSize," & _
                      " tblObjectInfo.Include, tblObjectInfo.Hidden, tblObjectInfo.System" & _
                      " FROM (tblObjectInfo LEFT JOIN qryObjBadNames ON (tblObjectInfo.ObjType = qryObjBadNames.ObjType)" & _
                      " AND (tblObjectInfo.ObjName = qryObjBadNames.ObjName)) " & _
                      " LEFT JOIN tblReservedWords ON tblObjectInfo.ObjName = tblReservedWords.Term" & _
                      " WHERE " & strWHERE & _
                      " ORDER BY tblObjectInfo.ObjType, tblObjectInfo.ObjName;"

 

[Gasman]

The only time I can think of that method 1 "doesn't work" is a navigation design that loads different subforms into a subform control depending on the current function.

Like in Northwind2 George?
Had a user on another forum asking how that was done.

 

[NauticalGent]

If .ControlType = acSubform

I didn't know that was part of the ControlType enums. Learn something new every day...

 

[GPGeorge]

That prevents you from using properly named report objects.

Not sure what you're talking about but my combo's RowSources are always populated using queries and never with code.

Keeping a table as I (and others) described earlier solves the "sub" problem since you only ever want to see main forms/reports. The table also gives you the opportunity to use good object names as well as friendly user names which just using MSysObjects doesn't support. I don't ever run into the other problem because I don't allow two forms to ever be open at the same time unless one is in dialog mode so it has to be closed for control to return to the other form. If you do allow multiple active forms (sure hope your users are on the ball), then I suppose you could use a table where each form adds its name when it opens and deletes it as it closes. That lets you avoid all the confusing code and just use queries to control the menu lists. Also, in Northwind some (but not all) forms allow multiple instances of the same form to be open. That actually makes more sense than allowing multiple different forms to be open. The only use case where I could envision a user having to have multiple forms open is if one is being used for reference and that worries me that there might be a design flaw.

As Pat noted, Northwind Developers Edition supports multiple open Orders forms and multiple open Invoices forms. That allows a user to compare items in two or more different orders without having to close and reopen the form for the second (or subsequent) order. The same applies to invoices.

The code which supports multiple copies of these forms is provided for you in Northwind Developers Edition.

Featured Access templates - Microsoft Support

Analyze and store large amounts of information, and manage data more efficiently with Access database templates available for download.

support.microsoft.com