pressMac
asked on
search and destroy mission, Microsoft Forms 2.0 Object Library
i have a ms access 2013 project that is converted from earlier ms access going back to 1.1. (glutton for punishment)
Anyway, i have references for Microsoft Forms 2.0 Object Library and the dreaded Microsoft Windows Common Controls 6.0 (SP6) for two treeviews and to make matters worse OCW11 for four charts on the dashboard.
My questions is whether there is a way or some code to run to iterate through the forms to find where there are instances of any of these controls. There are over 100 forms, and i don't know what might be using the controls aside from some specific instances as noted above. I know of no instance that is using the fm20, but it still will not let me uncheck it.
So, i would like to find anything that uses the fm20 control and remove or replace it. Also a ways to avoid OCW11 without using excel. Modern treeview. Also of these controls are very hard to maintain on a variety of users machines. Win Xp - Win8.1
Many thanks..
Anyway, i have references for Microsoft Forms 2.0 Object Library and the dreaded Microsoft Windows Common Controls 6.0 (SP6) for two treeviews and to make matters worse OCW11 for four charts on the dashboard.
My questions is whether there is a way or some code to run to iterate through the forms to find where there are instances of any of these controls. There are over 100 forms, and i don't know what might be using the controls aside from some specific instances as noted above. I know of no instance that is using the fm20, but it still will not let me uncheck it.
So, i would like to find anything that uses the fm20 control and remove or replace it. Also a ways to avoid OCW11 without using excel. Modern treeview. Also of these controls are very hard to maintain on a variety of users machines. Win Xp - Win8.1
Many thanks..
here is the list of control type names and number(code above returns number)
Constant Value
acBoundObjectFrame 108
acCheckBox 106
acComboBox 111
acCommandButton 104
acCustomControl 119
acImage 103
acLabel 100
acLine 102
acListBox 110
acObjectFrame 114
acOptionButton 105
acOptionGroup 107
acPage 124
acPageBreak 118
acRectangle 101
acSubform 112
acTabCtl 123
acTextBox 109
acToggleButton 122
Constant Value
acBoundObjectFrame 108
acCheckBox 106
acComboBox 111
acCommandButton 104
acCustomControl 119
acImage 103
acLabel 100
acLine 102
acListBox 110
acObjectFrame 114
acOptionButton 105
acOptionGroup 107
acPage 124
acPageBreak 118
acRectangle 101
acSubform 112
acTabCtl 123
acTextBox 109
acToggleButton 122
ASKER
Ok, so i added to that so it would loop through all forms and all controls. For this i limited to type = 112. Pagebreak.
i found two forms with pagebreaks and deleted those objects since i did not need them. Still could not delete fm20.
Now that i have a nice was to find things, how can i determine which as associated or dependent on which references?
Public Sub FindControlsForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object, ctl As control, countFrms As Integer, countCtls As Integer
Set dbs = Application.CurrentProject
countFrms = 0
countCtls = 0
Open MyDocuments & "\FindControlsForms.txt" For Append As #1
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
Print #1, "FORM NAME " & obj.Name
For Each ctl In Forms(obj.Name).Controls
If ctl.ControlType = 118 Then
Print #1, " CONTROL NAME "; ctl.Name & " > " & ctl.ControlType
countCtls = countCtls + 1
End If
Next
DoCmd.Close acForm, obj.Name, acSaveNo
countFrms = countFrms + 1
Next obj
Close #1
Debug.Print "CountForms:"; countFrms
Debug.Print "CountControls:"; countCtls
End Sub
i found two forms with pagebreaks and deleted those objects since i did not need them. Still could not delete fm20.
Now that i have a nice was to find things, how can i determine which as associated or dependent on which references?
Public Sub FindControlsForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object, ctl As control, countFrms As Integer, countCtls As Integer
Set dbs = Application.CurrentProject
countFrms = 0
countCtls = 0
Open MyDocuments & "\FindControlsForms.txt" For Append As #1
For Each obj In dbs.AllForms
DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
Print #1, "FORM NAME " & obj.Name
For Each ctl In Forms(obj.Name).Controls
If ctl.ControlType = 118 Then
Print #1, " CONTROL NAME "; ctl.Name & " > " & ctl.ControlType
countCtls = countCtls + 1
End If
Next
DoCmd.Close acForm, obj.Name, acSaveNo
countFrms = countFrms + 1
Next obj
Close #1
Debug.Print "CountForms:"; countFrms
Debug.Print "CountControls:"; countCtls
End Sub
<Still could not delete fm20.>
what is fm20 ?
what is fm20 ?
ASKER
This is MS Forms 2.0 Object Library, FM20.DLL.
So i added Print #1, " CONTROL Class "; ctl.Class to the loop and can now find the class that items are in.
Here are the results. with my notes to the right. It is still hard to know which reference goes with what, but these i know.
I have been able to go the the forms and simple paste in the new class property string into the Class field in the properties window. I have done this will the progress bar and the tree view.
FORM NAME Inventory Assembly Tree NOT USED BUT CHANGED the control class
CONTROL NAME TreeView1 > 119
CONTROL Class COMCTL.TreeCtrl.1 Pasted this in: MSComctlLib.TreeCtrl.2
FORM NAME frmJobsPopLineAdd LIVE
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME yRestore Company Dialog This form not used, very old, will delete
CONTROL NAME Duzocx11 > 119 ZIP Control
CONTROL Class duzactxctrl.duzactxctrl.1
CONTROL NAME MinorStatus > 119
CONTROL Class COMCTL.ProgCtrl.1 pasted in MSComctlLib.ProgCtrl.2
FORM NAME Convert Zip Code File CAN PROBABLY REMOVE
CONTROL NAME pbar > 119
CONTROL Class COMCTL.ProgCtrl.1 pasted in MSComctlLib.ProgCtrl.2
FORM NAME frmScheduleJobs LIVE FORM
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl. 4
FORM NAME yBackup Company Dialog CAN DELETE THIS FROM, using sql server now. DYNA ZIP
CONTROL NAME MinorStatus > 119
CONTROL Class COMCTL.ProgCtrl.1
CONTROL NAME Dzocx11 > 119
CONTROL Class dzactxctrl.dzactxctrl.1
FORM NAME frmOpsPop LIVE FORM
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME Inventory Professional LIVE FORM
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME frmGoogleMap LIVE FORM
CONTROL NAME WebBrowser0 > 119
CONTROL Class Shell.Explorer.2
FORM NAME SchedEvents_New DELETE THIS DEAD FORM, OLD
CONTROL NAME tab > 119
CONTROL Class TACOMP.fmstabCtrl.1
CONTROL NAME ProgressMeter > 119
CONTROL Class TACOMP.fmsprogCtrl.1
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl. 4
CONTROL NAME ctTips1 > 119
CONTROL Class ctTIPS.ctTipsCtrl.1
FORM NAME frmScheduleResources LIVE
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl. 4
FORM NAME frmCRM LIVE, the dashboard
CONTROL NAME chtGross > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtClients > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtMarketing > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtPipeLine > 119
So i added Print #1, " CONTROL Class "; ctl.Class to the loop and can now find the class that items are in.
Here are the results. with my notes to the right. It is still hard to know which reference goes with what, but these i know.
I have been able to go the the forms and simple paste in the new class property string into the Class field in the properties window. I have done this will the progress bar and the tree view.
FORM NAME Inventory Assembly Tree NOT USED BUT CHANGED the control class
CONTROL NAME TreeView1 > 119
CONTROL Class COMCTL.TreeCtrl.1 Pasted this in: MSComctlLib.TreeCtrl.2
FORM NAME frmJobsPopLineAdd LIVE
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME yRestore Company Dialog This form not used, very old, will delete
CONTROL NAME Duzocx11 > 119 ZIP Control
CONTROL Class duzactxctrl.duzactxctrl.1
CONTROL NAME MinorStatus > 119
CONTROL Class COMCTL.ProgCtrl.1 pasted in MSComctlLib.ProgCtrl.2
FORM NAME Convert Zip Code File CAN PROBABLY REMOVE
CONTROL NAME pbar > 119
CONTROL Class COMCTL.ProgCtrl.1 pasted in MSComctlLib.ProgCtrl.2
FORM NAME frmScheduleJobs LIVE FORM
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl.
FORM NAME yBackup Company Dialog CAN DELETE THIS FROM, using sql server now. DYNA ZIP
CONTROL NAME MinorStatus > 119
CONTROL Class COMCTL.ProgCtrl.1
CONTROL NAME Dzocx11 > 119
CONTROL Class dzactxctrl.dzactxctrl.1
FORM NAME frmOpsPop LIVE FORM
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME Inventory Professional LIVE FORM
CONTROL NAME axTreeView > 119
CONTROL Class MSComctlLib.TreeCtrl.2
FORM NAME frmGoogleMap LIVE FORM
CONTROL NAME WebBrowser0 > 119
CONTROL Class Shell.Explorer.2
FORM NAME SchedEvents_New DELETE THIS DEAD FORM, OLD
CONTROL NAME tab > 119
CONTROL Class TACOMP.fmstabCtrl.1
CONTROL NAME ProgressMeter > 119
CONTROL Class TACOMP.fmsprogCtrl.1
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl.
CONTROL NAME ctTips1 > 119
CONTROL Class ctTIPS.ctTipsCtrl.1
FORM NAME frmScheduleResources LIVE
CONTROL NAME ctSchedule1 > 119
CONTROL Class ctSCHEDULE.ctScheduleCtrl.
FORM NAME frmCRM LIVE, the dashboard
CONTROL NAME chtGross > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtClients > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtMarketing > 119
CONTROL Class OWC11.ChartSpace.11
CONTROL NAME chtPipeLine > 119
Access has no knowledge of the contents of the various ActiveX control libraries. It simply provides a container for those controls, and lets things go from there. If you need to know which controls are contained in the various items, you'd have to find an external reference and compare your findings with those.
ASKER
So i was able to consolidate all of the actixex controls. But i still am unable to remove the Microsoft Forms 2.0 Object Library as it states that it is in use. What i need is to be able to see what is using that. It is not exposed for objects like combo boxes.
See output of file:
FORM NAME frmChangeName
CONTROL NAME Field62 > 111
CONTROL Application FORM NAME zSalesOrderLineitemsSimple
CONTROL NAME Item Number > 111
CONTROL Application FORM NAME frmClientsRepopDialog
FORM NAME yFixed Assets Disposal Dialog
CONTROL NAME Capital Account > 111
CONTROL Application CONTROL NAME Cash Account > 111
CONTROL Application CONTROL NAME Asset Account > 111
CONTROL Application FORM NAME yInventory Functions Dialog
CONTROL NAME ItemNumber > 111
CONTROL Application CONTROL NAME Category > 111
CONTROL Application CONTROL NAME ItemNumberFromList > 111
CONTROL Application FORM NAME yInvoice Functions Dialog
FORM NAME yProblem Report Dialog
CONTROL NAME Type > 111
CONTROL Application CONTROL NAME Priority > 111
CONTROL Application FORM NAME yQuotation Functions Dialog
FORM NAME ySales Order Functions Dialog
CONTROL NAME CreateBackOrder > 111
CONTROL Application FORM NAME frmCliients_subLog
CONTROL NAME ctlSubject > 111
CONTROL Application CONTROL NAME ctlTheirRep > 111
CONTROL Application CONTROL NAME ctlOurRep > 111
CONTROL Application FORM NAME frmJobs_subLog
See output of file:
FORM NAME frmChangeName
CONTROL NAME Field62 > 111
CONTROL Application FORM NAME zSalesOrderLineitemsSimple
CONTROL NAME Item Number > 111
CONTROL Application FORM NAME frmClientsRepopDialog
FORM NAME yFixed Assets Disposal Dialog
CONTROL NAME Capital Account > 111
CONTROL Application CONTROL NAME Cash Account > 111
CONTROL Application CONTROL NAME Asset Account > 111
CONTROL Application FORM NAME yInventory Functions Dialog
CONTROL NAME ItemNumber > 111
CONTROL Application CONTROL NAME Category > 111
CONTROL Application CONTROL NAME ItemNumberFromList > 111
CONTROL Application FORM NAME yInvoice Functions Dialog
FORM NAME yProblem Report Dialog
CONTROL NAME Type > 111
CONTROL Application CONTROL NAME Priority > 111
CONTROL Application FORM NAME yQuotation Functions Dialog
FORM NAME ySales Order Functions Dialog
CONTROL NAME CreateBackOrder > 111
CONTROL Application FORM NAME frmCliients_subLog
CONTROL NAME ctlSubject > 111
CONTROL Application CONTROL NAME ctlTheirRep > 111
CONTROL Application CONTROL NAME ctlOurRep > 111
CONTROL Application FORM NAME frmJobs_subLog
Is it possible the control was used on a Report?
ASKER
The question at it's root is how to tell what library a control belongs to, not just what type of control. This does not answer the question so far.
So, while trying to figure this out i noticed that my old commandbutton controls had fewer properties in the properties window than my new ones. A new is a new form with a new command button, an old one is an imported form with all its controls, from Access XP. Gridline Color in particular. This gave me the idea that if i search for commandbuttons that did not have that property, then maybe those were old controls. Also, i tried to match all of the properties of the old commandbutton to the properties of the new command button. Even will all properties matched, the buttons did not match visually. How annoying.
Well, by looping through those controls, it turns out that all the properties are there. So i wrote out the properties to a text file and compared the values. Sure enough there were more properties than listed in the property sheet. But both old and new lead me to conclude that Access 2013 converts all commandbuttons when the forms are imported to a current version.
Then i used that output to set the values on my old controls, and it reformatted them where neither the property sheet nor the format painter succeeded. Note that there was one value, .gradient that had to moved to the end or it was countermanded by another property. Anyway, here is the code to reformat your command buttons to match each other.
Still looking to search and destroy old school references. Forms 2.0.
So, while trying to figure this out i noticed that my old commandbutton controls had fewer properties in the properties window than my new ones. A new is a new form with a new command button, an old one is an imported form with all its controls, from Access XP. Gridline Color in particular. This gave me the idea that if i search for commandbuttons that did not have that property, then maybe those were old controls. Also, i tried to match all of the properties of the old commandbutton to the properties of the new command button. Even will all properties matched, the buttons did not match visually. How annoying.
Well, by looping through those controls, it turns out that all the properties are there. So i wrote out the properties to a text file and compared the values. Sure enough there were more properties than listed in the property sheet. But both old and new lead me to conclude that Access 2013 converts all commandbuttons when the forms are imported to a current version.
Then i used that output to set the values on my old controls, and it reformatted them where neither the property sheet nor the format painter succeeded. Note that there was one value, .gradient that had to moved to the end or it was countermanded by another property. Anyway, here is the code to reformat your command buttons to match each other.
Public Sub fFindControlsForms()
On Error Resume Next
Dim obj As AccessObject, dbs As Object, ctl As control, countFrms As Integer, countCtls As Integer, prp As Property
Set dbs = Application.CurrentProject
countFrms = 0
countCtls = 0
Open MyDocuments & "\FindControlsForms.txt" For Append As #1
For Each obj In dbs.AllForms
If obj.Name = "111frmTest" Or obj.Name = "frmDialog" Then
DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
'Print #1, "FORM NAME " & obj.Name
For Each ctl In Forms(obj.Name).Controls
If ctl.ControlType = 104 Then
Print #1, "FORM NAME " & obj.Name & " CONTROL NAME "; ctl.Name & " > " & ctl.ControlType & " > " & ctl.BackColor&; " > " & ctl.Shape
Debug.Print "FORM NAME " & obj.Name & " CONTROL NAME "; ctl.Name & " > " & "ctl.shape"; ctl.Shape; ""
' For Each prp In ctl.Properties
' Print #1, vbTab & "ctl." & prp.Name & " = " & prp.Value
'Next prp
ctl.BackStyle = 1
ctl.Transparent = False
ctl.UseTheme = True
ctl.Shape = 1
ctl.Bevel = 0
ctl.Glow = 0
ctl.Shadow = 0
ctl.SoftEdges = 0
ctl.Gradient = 12
ctl.QuickStyle = 0
ctl.QuickStyleMask = 0
ctl.BackColor = 14136213
ctl.BackThemeColorIndex = 4
ctl.BackTint = 60
ctl.BackShade = 100
ctl.BorderStyle = 1
ctl.OldBorderStyle = 1
ctl.BorderLineStyle = 0
ctl.BorderWidth = 0
ctl.BorderColor = 14136213
ctl.BorderThemeColorIndex = 4
ctl.BorderTint = 60
ctl.BorderShade = 100
ctl.HoverColor = 15060409
ctl.HoverThemeColorIndex = 4
ctl.HoverTint = 40
ctl.HoverShade = 100
ctl.PressedColor = 9592887
ctl.PressedThemeColorIndex = 4
ctl.PressedTint = 100
ctl.PressedShade = 75
ctl.HoverForeColor = 4210752
ctl.HoverForeThemeColorIndex = 0
ctl.HoverForeTint = 75
ctl.HoverForeShade = 100
ctl.PressedForeColor = 4210752
ctl.PressedForeThemeColorIndex = 0
ctl.PressedForeTint = 75
ctl.PressedForeShade = 100
ctl.FontName = "Calibri"
ctl.fontsize = 11
ctl.alignment = 2
ctl.FontWeight = 400
ctl.FontUnderline = False
ctl.FontItalic = False
ctl.FontBold = 0
ctl.ForeColor = 4210752
ctl.ThemeFontIndex = 1
ctl.ForeThemeColorIndex = 0
ctl.ForeTint = 75
ctl.ForeShade = 100
ctl.GridlineStyleTop = 0
ctl.GridlineStyleBottom = 0
ctl.GridlineStyleLeft = 0
ctl.GridlineStyleRight = 0
ctl.GridlineColor = 10921638
ctl.GridlineWidthTop = 1
ctl.GridlineWidthBottom = 1
ctl.GridlineWidthLeft = 1
ctl.GridlineWidthRight = 1
ctl.TopPadding = 30
ctl.BottomPadding = 30
ctl.LeftPadding = 30
ctl.RightPadding = 30
ctl.HorizontalAnchor = 0
ctl.VerticalAnchor = 0
ctl.DisplayWhen = 0
ctl.ReadingOrder = 0
ctl.TextFontCharSet = 0
ctl.GridlineThemeColorIndex = 1
ctl.GridlineTint = 100
ctl.GridlineShade = 65
ctl.Gradient = 12 'oddly enough, when this property is set in the order that the properties are read, it does not stick. Here it works.
countCtls = countCtls + 1
End If
Next
DoCmd.Close acForm, obj.Name, acSaveYes
countFrms = countFrms + 1
Else
End If
Next obj
Close #1
Debug.Print "CountForms:"; countFrms
Debug.Print "CountControls:"; countCtls
End Sub
So i have attached the code in case it helps anyone. Thanks to Rey for the baseline code i used to get started.Still looking to search and destroy old school references. Forms 2.0.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Scott,
I will enumerate the controls and see if OLE or Class or not, to identify the external controls.
In the mean time i have found that not only must the dlls or ocxs be registered, they must (in some cases only) be the exact same version to ensure that they work. Once everything thing was matching, everything is compiling on all machines.
PS, the OWC11 is used for charting. Not as pretty as some newer controls, but it does work.
PressMac
I will enumerate the controls and see if OLE or Class or not, to identify the external controls.
In the mean time i have found that not only must the dlls or ocxs be registered, they must (in some cases only) be the exact same version to ensure that they work. Once everything thing was matching, everything is compiling on all machines.
PS, the OWC11 is used for charting. Not as pretty as some newer controls, but it does work.
PressMac
In the mean time i have found that not only must the dlls or ocxs be registered, they must (in some cases only) be the exact same version to ensure that they work.The must be matched to the other components on which they depend, which is why is maddening to try and piece together a fix for things like this, and why it becomes increasingly difficult to support applications that are not upgraded in a timely manner. In particular, and app like yours, that comes from a 1.1 version, is highly susceptible to these sorts of things, since they tend to grow and develop over the years, and become dependent on technology that becomes deprecated. Sooner or later MSFT stops being concerned with those technologies (like the OCW, for example), and you're left with trying to make things work in environments where they're not intended to work. That's when "Dll Hell" comes into play, and essentially that's where you're at.
I would strongly advise you to get away from non-native controls and instead work with the native Access stuff. There are some control suites that claim to work with Access which can provide you with extended functionality, but quite honestly if you need more than Access can offer, you should strike out on the .NET path and learn one of those languages. Desktop Access seems to be falling aside in favor of Web Access, and that trend doesn't seem to show any signs it's stopping.
Finally: MSFT has warned for years that using ActiveX controls not designed for Access is a problem waiting to happen, and unfortunately many (including quite a few Experts here) don't heed those warning. Even controls put out by MSFT (like the MSFORMS stuff) is not Access compliant, so you're taking a risk when you use them. If your business (or your client's business) is dependent on that Access app, then you're obligated to at least review the best way ahead - either get rid of all those non-native controls, or make plans to migrate to a compliant platform.
Dim ctl As Control, frmX as string
frmX="NameOfForm"
DoCmd.OpenForm frmX, acDesign, , , , acHidden
For Each ctl In Forms(frmX).Controls
Debug.Print ctl.Name & " > " & ctl.ControlType
Next
DoCmd.Close acForm, frmX, acSaveNo