First I made the clsOpenArgs up in the form's header public so that I could see it from outside the form.
Code:
Option Compare Database
Option Explicit
Public mclsOpenArgs As clsOpenArgs
Of course the correct way to do this is to create a property in the form to expose the clsOpenArgs but this is just test code.
I had not created the method to apply the properties to the form so I did that.
Code:
Function fApplyFormProperties()
On Error GoTo fApplyFormProperties_Error
Dim lclsOpenArg As clsOpenArg
For Each lclsOpenArg In mcolOpenArg
mfrm.Properties(lclsOpenArg.pName) = lclsOpenArg.pVal
Next lclsOpenArg
Exit_fApplyFormProperties:
On Error GoTo 0
Exit Function
fApplyFormProperties_Error:
Dim strErrMsg As String
Select Case Err
Case 0 'insert Errors you wish to ignore here
Resume Next
Case Else 'All other errors will trap
strErrMsg = "Error " & Err.Number & " (" & Err.Description & ") in procedure EDPDemoDB.clsOpenArgs.fApplyFormProperties, line " & Erl & "."
Beep
#If boolELE = 1 Then
WriteErrorLog strErrMsg
#End If
assDebugPrint strErrMsg
Resume Exit_fApplyFormProperties
End Select
Resume Exit_fApplyFormProperties
Resume 0 'FOR TROUBLESHOOTING
End Function
I then created a function out in a test module to get a pointer to that specific form so that I could play around.
Code:
Function frmOpenArgsDemo() As Form_frmOpenArgsDemo
Set frmOpenArgsDemo = Forms("frmOpenArgsDemo")
End Function
I then opened the form as follows (in the debug window):
docmd.OpenForm "frmOpenArgsDemo",acNormal,,,,acWindowNormal,"Visible=false;Color=Blue"
passing in two open args, visible=false and color=blue
I then called the method in the debug window to apply the OpenArgs to the form's properties (in the debug window):
frmOpenArgsDemo.mclsOpenArgs.fApplyFormProperties
The form went invisible due to the first OpenArg. Then the second OpenArg threw an error as shown.
Error 2455 (You entered an expression that has an invalid reference to the property Color.) in procedure EDPDemoDB.clsOpenArgs.fApplyFormProperties, line 0.
So the concept works.
The code is up on GitHub in my demo database. frmOpenArgsDemo for the form, basTest for the module with the code to get a pointer to the open form. It has to be open obviously.
This has been a post to demonstrate a little more about classes
John, I do not presume to know your meaning by putting "expert" in quotations. When I use them in that manner, my intention is to question the word in quotes as questionable.
It is there that I am coming from.
The two individuals you alluded to, I can assure you are indeed experts - recognized as such by every member of this forum and elsewhere in the Access Developer community. Their contribution to this thread, whether you or anyone else agrees with it, does not in any way negate that.
If I misunderstood your intent, then please disregard and accept my apology. If I have it right, then understand I will call out you, and anyone else for that matter, each and every time I perceive a slight towards this Forum's experts, or Heavyweights as I like to call them.
My intent was to point out that "experts" were doing things not particularly expert nor helpful. I absolutely agree that both individuals are experts. But their suggested solutions were not particularly so. IMO of course.
And this points out the problem I have with folks just jumping in to a thread where I am trying to teach something. Yea, I can ignore these posts but doing so ignores the obvious (to me) problems with their posts. Suddenly I am getting solutions to something that just don't work at all (a where clause in a form doing what what I was trying to accomplish) or do work but are decidedly questionable (to me). To suggest using a dictionary when that library is being deprecated is questionable (IMO). To question grabbing a pointer to the form "to avoid a circular reference" is questionable (IMO). And even worse, as expressed by another poster, "experts" (which they are) are calling into question my solutions and my abilities as a programmer and thus as a teacher of the subject. In this case the pointer to the form easily and quickly adds an ability that I wanted to have. See my post about using OpenArgs to set form properties.
I am sending you to my blog where links to the book and the db exist. I just tested this and it is working for me. And the book is even hotter than the banging!!!
this is a link to my blog. The link there downloads the book and / or the demo database directly from GitHub into your downloads dir.
For some reason, at least on my computer, the link is randomly opening to another blog post rather than to the blog post regarding the book. No se por que. Nor how to fix it.
A function to allow testing pushing OpenArgs into form properties. Many properties are only settable in design mode.
In the immediate window perform the following:
fTestOpenArgs "Caption=Hello;"
More scary "Visible=False". Yes it hides the opened form.
Code:
Function fTestOpenArgs(strOpenArgs As String)
On Error Resume Next
DoCmd.Close acForm, "frmOpenArgsDemo"
DoCmd.OpenForm "frmOpenArgsDemo", acNormal, , , , acWindowNormal, strOpenArgs
frmOpenArgsDemo.mclsOpenArgs.fApplyFormProperties
End Function
Having little or nothing to do with OpenArgs except it was a convenient place to test this stuff:
Code:
'
'I am making the value variant simply because I have no clue
'what variable type any given property might accept.
'
Public Function fSetFormProperty(strPropertyName As String, varPropertyValue As Variant)
mfrm.Properties(strPropertyName) = varPropertyValue
End Function
Now in the debug window one at a time, and watch the form:
fTestOpenArgs "Caption=This is a test of the 1MC from deckhouse 2"
frmOpenArgsDemo.mclsOpenArgs.fSetFormProperty "Caption","This is a drill"
frmOpenArgsDemo.mclsOpenArgs.fSetFormProperty "Visible","False"
frmOpenArgsDemo.mclsOpenArgs.fSetFormProperty "Visible","True"
frmOpenArgsDemo.mclsOpenArgs.fSetFormProperty "Caption","What a mess"
frmOpenArgsDemo.mclsOpenArgs.fSetFormProperty "Caption","General Quarters general quarters, all hands man your battle stations"
John, I do not presume to know your meaning by putting "expert" in quotations. When I use them in that manner, my intention is to question the word in quotes as questionable.
It is there that I am coming from.
The two individuals you alluded to, I can assure you are indeed experts - recognized as such by every member of this forum and elsewhere in the Access Developer community. Their contribution to this thread, whether you or anyone else agrees with it, does not in any way negate that.
If I misunderstood your intent, then please disregard and accept my apology. If I have it right, then understand I will call out you, and anyone else for that matter, each and every time I perceive a slight towards this Forum's experts, or Heavyweights as I like to call them.
I understand that I have not yet achieved the Expert classification here. However wouldn't coming into "my" (I am the OP here) thread and implying (at the very least) that I was doing this wrong be a slight? I had another poster suggest that I was perhaps unqualified to teach this subject because I "failed" to teach about circular references and another expert did so. Though IMO, the other expert did not teach what a circular reference is, how it is created, when you might validly want to use one, nor how to correctly deal with it when cleaning up.
One cannot be hurt in an area one does not posses...
Kidding aside, I commend you on your tenacity and (for the most part) thick skin. A lot of others have come here, did not get the "propers" they thought they deserved and bounced.
I have mentioned you and your BLOG numerous times on this forum LONG before you became a member. And although your overall Access prowess has yet to be showcased, your expertise in Class Modules are legendary. What I have repeatedly mentioned is how you fill a void on how to build them and what's more, you break it down in easy to understand sentences.
Like GasMan, I have a hard time wrapping my head around them, mostly because 99.9999% of the time, a simple Public Sub/Function is all that is needed for what I am trying to do.
Like MarkK, I truly appreciate your efforts and enjoy what you bring to AWF - even if you tend to get butt-hurt easily!
The "Microsoft Scripting Runtime," installed on my machine at C:\Windows\System32\scrrun.dll, and provider of the insanely useful Dictionary, FileSystemObject, File, Folder, and Drive objects, is not the same library this link points to.
• Microsoft Scripting Runtime is not being deprecated.
• The Scripting.Dictionary gives you exceedingly fast access to key/value or key/object pairs you might need to manage in code. It's a list structure like a VBA.Collection. It's a little harder to enumerate its members, but then you rarely need to enumerate its members because it exposes...
Code:
Public Property Get Exists(key) As Boolean
...which a VBA.Collection painfully lacks.
• If you need an enumerable list, use a Collection.
• If you have named, keyed, or ID'ed elements, use a Dictionary.
The "Microsoft Scripting Runtime," installed on my machine at C:\Windows\System32\scrrun.dll, and provider of the insanely useful Dictionary, FileSystemObject, File, Folder, and Drive objects, is not the same library this link points to.
• Microsoft Scripting Runtime is not being deprecated.
• The Scripting.Dictionary gives you exceedingly fast access to key/value or key/object pairs you might need to manage in code. It's a list structure like a VBA.Collection. It's a little harder to enumerate its members, but then you rarely need to enumerate its members because it exposes...
Code:
Public Property Get Exists(key) As Boolean
...which a VBA.Collection painfully lacks.
• If you need an enumerable list, use a Collection.
• If you have named, keyed, or ID'ed elements, use a Dictionary.
Phase 1 (Second Half of 2024): VBScript will be available as a Features on Demand (FOD) in Windows 11, version 24H2. It will be pre-installed and enabled by default, allowing users time to transition their applications and processes away from VBScript. thehackernews.com+9anvilogic.com+9msftnewsnow.com+9
Phase 2 (Around 2027): VBScript will no longer be enabled by default in Windows. Users who still rely on VBScript will need to manually enable it as a FOD. winbuzzer.com
Phase 3 (Date TBD): VBScript will be completely removed from future versions of Windows, including the removal of all associated dynamic link libraries (.dll files). thehackernews.com+5i-programmer.info+5winbuzzer.com+5
If your applications or scripts depend on the Microsoft Scripting Runtime (e.g., the FileSystemObject or Dictionary), it's advisable to begin migrating to alternatives like PowerShell or JavaScript. This transition will help ensure continued functionality and security as VBScript support diminishes.
Language expressions, e.g., If, For, Function, etc.
Registered COM Library?
Yes
Yes
Dependent on vbscript.dll?
No
N/A (they’re siblings, not parents)
In Practice
You can use Scripting.FileSystemObject and Scripting.Dictionary in VBA without any involvement from the VBScript engine.
Even if vbscript.dll is disabled (e.g., due to security hardening on Windows), scrrun.dll continues to work just fine.
These libraries are often used together in Windows Script Host (WSH) .vbs files, which is probably where the confusion comes from — but they are separate.
Example from VBA (No VBScript Required):
Code:
Dim fso As New Scripting.FileSystemObject
Dim file As Scripting.TextStream
Set file = fso.OpenTextFile("C:\Temp\test.txt", ForReading)
Debug.Print file.ReadAll
file.Close
This works even if VBScript is disabled via group policy or registry.
Summary
No, the Microsoft Scripting Runtime does not depend on VBScript. They are separate COM components. You can safely use the Scripting Runtime in VBA and Access regardless of VBScript’s availability.
My framework enables behaviors across my entire application. A clsCtlCbo is loaded anytime that a combo is encountered in any form where clsFrm is being used. So...
clsFrm calls its control scanner function to scan for controls. The clsCtlCbo is instantiated automagically. A pointer to that combo is passed in to clsCtlCbo and stored in the header of the class "Withevents". Withevents means that clsCtlCbo can now sink the events for the combo passed in.
In the meantime clsFrm stores the new clsCtlCbo in a collection in its header. ClsFrm has a property to allow the developer to access any combo box by name:
Code:
Property Get cCtlCbo(strCboName As String) As clsCtlCbo
Set cCtlCbo = mcolClasses(strCboName)
End Property
clsCtlCbo has a bunch of variables in its header dealing with a bunch of different possible behaviors.
Code:
Option Compare Database
Option Explicit
Private WithEvents mctlCbo As ComboBox
Private Const cstrEvProc As String = "[Event Procedure]"
Private mlngBackColor As Long
Private Const clngBackColor As Long = vbCyan
Private mclsCtlLbl As clsCtlLbl
Private mstrDblClickFrmName As String
'
'Variables for handling the DblClick and NotInList events
Private mstrFrmName As String 'The form we will open if DblClick
Private mstrTblName As String 'The table that we will put data into in NotInList
Private mstrFldName As String 'The field in the table to place data in into in NotInList
Private mblnUseDblClick As Boolean 'True means use dblclick to open a form
Private mblnUseNotInList As Boolean 'True means use the NotInList processing
Private mblnUseNotInListFrm As Boolean 'True means use the form for NotInList
Private WithEvents mctlCbo As ComboBox
is a variable to store the pointer to the combo that the class is dealing with
Private mstrFrmName As String 'The form we will open if DblClick
mstrFrmName stores the name of a field to open if we want this DblClick behavior enabled.
clsCtlCbo has a method that allows the developer to pass it a form to open on dblClick.
Code:
Property Let pDblClickFrmName(lstrDblClickFrmName As String)
mstrDblClickFrmName = lstrDblClickFrmName
End Property
Back in the form itself...
We have set up the form to use fclsFrm
Code:
Public fclsFrm As clsFrm
Private Sub Form_Open(Cancel As Integer)
Set fclsFrm = New clsFrm
fclsFrm.mInit Me
'
'These two lines program fclsFrm to open a specific list form
'when the user double clicks in cboEyeColor or cboHairColor
'
fclsFrm.cCtlCbo("cboEyeColor").pDblClickFrmName = "lfrmEyeColor"
fclsFrm.cCtlCbo("cboHairColor").pDblClickFrmName = "lfrmHairColor"
'
fclsFrm is dimensioned, initialized and the cboEyeColor is passed in a form name to open, lfrmEyeColor. CboHairColor is passed in a form name to open, lfrmHair color.
clsCtlCbo, being a class, can store code and data about the environment in question. It has properties which I can call with a .Property syntax, in this case .pDblClickFrmName
The following is the dblClick event for the combo box.
Code:
Private Sub mctlCbo_DblClick(Cancel As Integer)
If Len(mstrDblClickFrmName) Then
'
'Pass in OpenParams "PKID=XXX"
'The opening form will parse them and use them and use to seek to the right record
'
DoCmd.OpenForm mstrDblClickFrmName, , , , , acDialog, "PKID=" & mCtlCbo.Value
'
'We opened the form as a dialog which means that the code stops
'executing until the form cloase.
'When execution returns here, requery the combo to include any changes
'
mCtlCbo.Requery
End If
End Sub
So we have set up all the pieces to allow a form to find and create instances of combo box clsCtlCbo. We have set up all the pieces to allow clsCtlCbo to sink the dblclick event. After that to know what form it will open in the dblClick event.
Notice that the dblClick event opens the form acDialog, so execution stops back in the dblClick event as the form loads.
The openargs passed in tells the opening form that the PKID to seek to is whatever mCtlCbo.Value
So for example, the frmEyeColor opens. It has a clsOpenArgs which pulls in the openarg, parses it and gets it ready for the form to use.
Code:
Function mInit(lfrm As Form)
Dim lngRecID As Long
Set mcolClasses = New Collection
Set mFrm = lfrm
EvSet("OnKeyDown").EvSet("BeforeUpdate").EvSet("OnClose").EvSet ("OnCurrent")
mFrm.KeyPreview = True
Set mclsOpenArgs = New clsOpenArgs
mclsOpenArgs.mInit mFrm
in clsFrm.mInit, immediately after the control scanner, we check the Openargs to see if the correct openarg has been passed in.
Code:
MCtlScanner
'
'If no Openargs then don't try to do the mFindRec
'
'
If (cOpenArgs.colOpenArgs.Count) Then
On Error Resume Next
'
'If we do have an open arg, see if one of them is a PKID
lngRecID = cOpenArgs.cOpenArgByName("PKID").pVal
If (Error = 0) Or (Error Is Nothing) Then
'
'If so then find the record
mFindRec lngRecID
End If
End If
If we do then go find the record:
Code:
'
'This function finds a record based on the record ID
'Syncs the record selector if any
Public Function mFindRec(lngRecID As Long)
On Error GoTo Err_mFindRec
Dim rst As DAO.Recordset
'Dim intCnt As Integer
If lngRecID > 0 Then
With mfrm
Set rst = .RecordsetClone
'SET ALLOWEDITS BACK TO FALSE
Dim strSQL As String
'BUILD AN SQL STATEMENT
strSQL = !txtPKID.ControlSource & " = " & lngRecID
' Find the record that matches the control.
rst.FindFirst strSQL
If Not rst.NoMatch Then
'SET THE FORMS BOOKMARK TO THE RECORDSET CLONES BOOKMARK ("FIND" THE RECORD)
.Bookmark = rst.Bookmark
On Error Resume Next
'
'Do this later when the RecSel combo is working
' mclsCtlCboRecSel.mFrmSyncRecSel
Else
'We didn't find the record so move to the new record
mfrm.DataEntry = True
End If
End With
End If
Exit_mFindRec:
On Error Resume Next
If Not (rst Is Nothing) Then rst.Close: Set rst = Nothing
Exit Function
Err_mFindRec:
MsgBox Err.Description, , "Error in Function clsFrm.mFindRec"
Resume Exit_mFindRec
Resume 0 '.FOR TROUBLESHOOTING
End Function
FindRec causes the form to find the correct record. At this point the user can edit that record, or for that matter any other record. Or no record.
Whatever the user does edit wise, as (s)he closes the form, code execution picks back up in clsCtlCbo.mctlCbo_DblClick.
Code:
'
'We opened the form as a dialog which means that the code stops
'executing until the form cloase.
'When execution returns here, requery the combo to include any changes
'
mctlCbo.Requery
And the combo requeries. Now if the user performed any edits, the edits will be seen in the combo box immediately as they come back from the form that opened.
And that ladies and gentlemen is the magic of a framework. I already had a clsFrm. I already had a clsCtlCbo. In clsCtlCbo I added code, properties, event sinks and header variables to add a dblClick behavior. From that point on I could simply "program" a given combo control using a dot property to pass in a form to open.
It is the very fact that we did this that causes the combo to to discover that it has a form it is supposed to open in the dblclick event.
Code:
Private Sub mctlCbo_DblClick(Cancel As Integer)
If Len(mstrDblClickFrmName) Then
'
'Pass in OpenParams "PKID=XXX"
'The opening form will parse them and use them and use to seek to the right record
'
DoCmd.OpenForm mstrDblClickFrmName, , , , , acDialog, "PKID=" & mctlCbo.Value
To summarize, I am teaching classes, event sinks, wrapping controls, wrapping forms, and in the end, frameworks. I wrote code in my framework to perform this behavior. It wasn't free, it wasn't easy. It wasn't "no thinking". It took years of working on client apps and figuring out things that I wanted my framework to do, slowly adding the pieces and parts to allow me to do these behaviors.
In this specific case, clsOpenArgs is at the center of this specific behavior. Which is the answer to the question of "why not a where clause". Much much more is going on here.
The point is not what my framework does, it does what I thought was useful The point is that anyone can do this. Whatever behaviors you think are useful, you can implement using classes, wrapping forms and controls, sinking events. You may decide that you could never do this kind of thing. You can. I am just a guy who learned this stuff and had some ideas.
You may decide that you don't want to do this kind of thing. That is valid.
I am not afraid of classes. I have done this for 20+ years. Yes I have to consistently clean up behind myself. So should everyone.
When I started working with classes sinking and raising events, in A97 in 1998, Access crashed with regularity. Now it does not. And I still clean up behind myself.
John, I have a question you can probably answer. Suppose I have an object, say a collection. Each item in the collection is another object (e.g. a reference to a form or a control, or another collection). In terms of "cleaning up", is it enough to set the collection to nothing, or is it better/necessary to first iterate on each item to set it to nothing - and only then, destroy the collection?
The safest is to iterate the collection and delete each item. However I believe that just destroying the pointer to the collection will cause the garbage collector to clean it all up. At one time I had a function I could call, passing in a collection, and it wold do that iterate / delete each item for me.
I thought so. But I usually iterate and destroy all the items before destroying the container object. After all, if we really trusted the garbage collector, we wouldn't even consider destroying the container object...
My meaning is still clear I suppose?
!
Here’s the distinction:
No
In Practice
Example from VBA (No VBScript Required):
