I have a form that I use both as a continuous Form and a continuous SubForm. There is a Close button on this form except that I hide this button when the form is being loaded as a Sub-Form.
The way I do this is to use the cmdClose.Visible code is put in the Load event of the form a check to see if the Parent Form is Loaded.
However this code is becoming cumbersome as the form could be loaded in 3 or 4 separate instances as a SubForm.
I was wondering whether there was a simple way to check whether a form was being loaded as a subform or not.
I have thought about using some code based around Me.Parent.Name but that gives me a 2427 error. I can work with this but again wonder if there is a more elegant way?
Private Sub Form_Load()
On Error GoTo Err_Parent
Dim str As String
str = Me.Parent.Name ' It will error at this point if No Parent exists
cmdClose.Visible = False
Exit_Parent:
Exit Sub
Err_Parent:
If Err.Number = 2427 Then
cmdClose.Visible = True
Resume Exit_Parent
End If
End Sub
Public Function IsSubForm(ByVal obj As Form, Optional Ctl As Object) As Boolean
On Error GoTo Err_Parent
Dim str As String
str = obj.Parent.name ' It will error at this point if No Parent exists
If Not Ctl Is Nothing Then _
obj(Ctl.name).Visible = False
IsSubForm = True
Exit_Parent:
Exit Function
Err_Parent:
If Not Ctl Is Nothing Then _
obj(Ctl.name).Visible = True
IsSubForm = False
Resume Exit_Parent
End Function
now on the Load Event of the form/subform (Property->Event), just put:
I wouldn’t do it in each instance of the subform, I’d do it from the Main Form….
Also, I'm uncomfortable when an Error is triggered deliberately to make something happen. However, it can't be avoided all of the time and is sometimes a necessary evil.
In this particular case I can see a way which would avoid such an abuse of MS Access!
I would look at the problem from a different perspective.
There's no such thing as a “Sub-Form”! What you have on your “Main Form” is an ordinary form housed within a subform/subreport control, which I refer to as a subform window. The term “Subform” is a useful way of explaining what you see, however the term does tend to take your eye off the ball so to speak, because the form (Subform) isn’t actually on the Main Form it actually resides within the subform/subreport control.
There is a natural tendency to think about Controls, Forms, Objects as individual entities, however most things in MS Access are members of a collection. There's no exception in this case, your command button is one of the controls in the controls collection of the form it’s on. The form itself is housed within the subform window. It’s not necessary to refer to this form by its full name, you just refer to it as an object, as the object contained within that subform window a “Form”
Once you think about writing your code to take advantage of these Collections of Controls, then there are loads of fascinating and interesting things you can do. In this case, I would approach the problem from the “Main Form” perspective, not the subform perspective.
I would write code in the main form that would traverse the Main Form’s Controls Collection when the main form loads.
in your main form's “Load Event” (the form housing all of the identical subforms), run the code below. The code will seek out all of your subforms it will look in each form and see if it has a command button named “btnToHide” and set it's visible property to false...
Code:
Private Sub Form_Load()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acSubform
Ctrl.Form.btnToHide.Visible = False
End Select
Next Ctrl
End Sub 'Form_Load
Gasman, that property goes on the form that might be stand-alone or might be a subform...
Code:
Property Get HasParent As Boolean
[COLOR="Green"]' This property defaults to false if there is an error[/COLOR]
On Error Resume Next
HasParent = Not Me.Parent Is Nothing [COLOR="green"]'this causes an error in a stand-alone form[/COLOR]
End Property
Private Sub Form_Open(Cancel As Integer)
Me.cmdClose.Visible = Not Me.HasParent [COLOR="Green"]'not visible if Me.HasParent, else visible[/COLOR]
End Sub
I agree with Uncle Gizmo that it is not good practice to wait for an error to happen as a routine part of solving a problem, but to me it is crazy that referencing the Parent property of a form should ever return an error at all. A stand-alone form's Parent property should return Nothing.
The previous code I provided is dependent on each subform containing a command button named “btnToHide” In other words if you add a new, different subform, one without this command button on it, then you will get an error.
One solution would be to add a command button named “btnToHide” to all of the subforms that you are going to use on the main form.
However it doesn't seem right that you should have to add an unnecessary control to a form to ensure that your code works correctly in one particular situation. Now think about the above statement, it is obvious that adding an extra control for this purpose goes against the grain, but look, you are quite happy adding code to the onload event of the form which is very specific to one particular situation in your database. In other words adding code seems right, but adding a control seems wrong.
The point I am making is that you should keep your forms clean! Don't have any extra controls, controls that are there to operate under certain conditions. Don't have any code which operates under certain conditions, not if it's possible to achieve the same effect by manipulating the form with VBA.
As I mentioned before about utilising an error to make your code work correctly, it should be avoided if at all possible. Again the same here, if you have the opportunity to keep your form clean, free from unnecessary code, free from unnecessary controls then you should do that.
I think what I have described is written down somewhere as a basic principal of writing code, and it's got a name, but I can't recall what it is… something like coupling and/or decoupling, although I'm sure those terms are more to do with Class Modules…
I'm not criticizing anybody for using the error event or adding code or controls to a form in this way. I do it myself! My goal is to pique your interest in a different way of looking at your coding techniques which I'm sure once your grasp will make things so much easier!
Now for the update...
The new version of the code below looks in each form (subform) interrogates the controls collection of the form looks for a command button named “btnToHide” if it finds the button it renders it invisible, if it cannot find the button it does nothing. In other words it does not fail like the first code example. You will only need this updated code if you add forms (subforms) which do not have the command button named “btnToHide”....
Code:
Private Sub Form_Load()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
Select Case Ctrl.ControlType
Case acSubform
Dim frmCtrl As Control
For Each frmCtrl In Ctrl.Form.Controls
If frmCtrl.Name = "btnToHide" Then
frmCtrl.Visible = False
End If
Next frmCtrl
End Select
Next Ctrl
'Warning!!! This code is not tested, and besides that warning, I also have some
'suspicions that what I think it's doing and what it is actually doing are two
'different things! In other words if you use it make sure you know what it's
'doing!
End Sub
I agree completely with both mark and Tony.
I don't like using code that relies on an error being triggered
If you do so, unexpected behaviour will probably occur at a later date and bite you.
There is (almost) always a better way.
For example compare the different solutions given to code which checks if a function or sub exists. See this thread https://www.access-programmers.co.uk/forums/showthread.php?t=241933
The O/P is specifically asking about a button, but what if there were a bunch of controls involved?
I've seen lots of examples here where controls are disabled/hidden if a value is in the Tag property.
Why not use the tags? then it does not matter if the control exists or not?
What I don't like about using the .Tag property of a subset of controls is that it is hidden. You have to go digging thru the controls in design view to see how the thing works. What I do if I have a subset of controls I am concerned with is I re-expose that subset as a variant array explicitly constructed in the code, like...
Code:
private m_vControls[COLOR="Green"] 'global to the form, for the lifetime of the form[/COLOR]
Public Property Get MyControlSet
[COLOR="Green"]' this property lazy-loads the array if a consumer happens to need it[/COLOR]
if isempty(m_vControls) then m_vControls = Array([COLOR="Blue"]Me.Text0, Me.Text2, Me.Text4[/COLOR])
MyControlSet = m_vControls
End Property
no hidden .Tag property values
very short list of controls to enumerate, better than For Each ctl in Me.Controls, and then check EVERYTHING for a .Tag
clear-as-day list of controls that are in the set
...and enumerating the set is as simple as...
Code:
Dim var
For Each var in Me.MyControlSet
var.ForeColor = vbRed
Next
...and MyControlSet is public, so consumers outside the form itself can access it too if they want.
Mark
What I do if I have a subset of controls I am concerned with is I re-expose that subset as a variant array explicitly constructed in the code, like...
Code:
private m_vControls[COLOR="Green"] 'global to the form, for the lifetime of the form[/COLOR]
Public Property Get MyControlSet
[COLOR="Green"]' this property lazy-loads the array if a consumer happens to need it[/COLOR]
if isempty(m_vControls) then m_vControls = Array([COLOR="Blue"]Me.Text0, Me.Text2, Me.Text4[/COLOR])
MyControlSet = m_vControls
End Property
no hidden .Tag property values
very short list of controls to enumerate, better than For Each ctl in Me.Controls, and then check EVERYTHING for a .Tag
clear-as-day list of controls that are in the set
...and enumerating the set is as simple as...
Code:
Dim var
For Each var in Me.MyControlSet
var.ForeColor = vbRed
Next
...and MyControlSet is public, so consumers outside the form itself can access it too if they want.
Mark
As someone who regularly uses the tag property to manage groups of controls, I fully accept your comments about digging around in design view to determine which controls have a particular tag.
I also think your code is elegant but I'm not convinced its as concise in practice as using tags to do the same thing.
Also do you risk getting confused by having several control sets on your form to manage several control groups?
Or have I misunderstood that bit?
So I have a challenge for you which I hope you will be happy to take on.
Some months ago I created a sample database using the tag property to change the following properties of groups of controls - visible / enabled / locked
My challenge is for you to use the same form and controls and do exactly the same as I did but using your approach.
There are 4 groups of controls on the form, currently tagged A, B, C, D
I'd be interested in seeing the code required using your approach.
it is crazy that referencing the Parent property of a form should ever return an error at all. A stand-alone form's Parent property should return Nothing.
Hi Mark..
I have suffered many hours nay, many days nay, many weeks struggling with the stupid, unnecessary issue of what happens when an object does not exist in an object variable/form variable... I've tried using "is nothing" (that's the only one I can recall at the moment) there's loads of different ones I've tried, but nothing works! So I understand your pain!!!
In view of your observation about referencing the Parent property of a form I wrote The following routine. Unfortunately will not work from the load event of the subform because at that stage the subform is not instantiated, even though you are calling the damn code from the very self same subform!
I spent a few hours on this and I am tired! So I haven't vetted it for stupid errors. I would appreciate someone else pointing anything out to me that I have missed...
First thing is it needs a better name!
It will return true if the form you pass into it is a subform, and will return false if it's a stand-alone form.
The first problem I solved was that you might have the same subform loaded more than once so you can't use the name of subform because if you find 3 how do you know which one is the right one? Then I remembered that you could get a forms unique identifier, the windows handle "Hwnd" so I use that to identify the particular form...
The only thing is, seeing as you can't use it from the form's load event, I can't see that there's really anything else you can use it for?
Code:
Function fBlnIsSubform(oForm As Form) As Boolean
'Code Found Here:-
'https://msdn.microsoft.com/en-us/vba/access-vba/articles/allforms-object-access
'This looks interesting!
'https://social.msdn.microsoft.com/Forums/office/en-US/cbe20928-a121-4709-a7c3-d8952973242f/runtime-error-2455-you-entered-an-expression-that-has-an-invalid-reference-to-the-property?forum=accessdev
'''Call Like this:-
'''Private Sub btnToHide_Click()
''' MsgBox "Am I a Subform? >>> " & fBlnIsSubform(Me)
'''End Sub
'WILLNOT work from Load event, Well, it sorta works but throws errors....
Dim blnIsA_Sub As Boolean
Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
Dim strFrmName As String
Dim frmX As Form
strFrmName = obj.Name
'Find each loaded form in turn and set them to a form variable
Set frmX = Forms(strFrmName)
Dim Ctrl As Control
'For each control in the found, "Loaded" form
For Each Ctrl In frmX.Controls
Select Case Ctrl.ControlType
Case acSubform 'check to see if it is a subform
If Ctrl.Form.Hwnd = oForm.Hwnd Then 'Test to see if the form you are in (the form you passed in)
blnIsA_Sub = True 'and the form you have found, have the same Windows handle.
End If
End Select
Next Ctrl
End If
Next obj
fBlnIsSubform = blnIsA_Sub
End Sub
